how to generate nested xml from database/table using sql queries

[I’ve been focusing on Frontend development for some time and didn’t had an need to explore FOR XML command on SQL server. But I when I needed it today, felt its an awesome feature. Adding this post here as the search engines pointed me to how to write complex stored procedures for this instead of redirecting to nested For XML queries.]

 

I was working on migrating some of the features to Azure and there was data XML with complex structure, I wanted to move to relational data base and build WebAPI’s to expose the functionality to be used by many frontend clients running outside Azure. So I created all the relational tables in SQL Azure and wrote the stored procedures etc. A blocker question came in as a requirement on this feature should work if Azure goes down (yup, rare but I work on critical app which shouldn’t have any downtime) and we don’t the old app to work without much changes. After thinking through caching & performance, decided to generate the XML output from SQL itself and pass it on for current applications that are not in Azure (and save cost by get bulk and cache it and reducing chattiness).

 

Binged on creating XML in stored procedure and got many forums explaining how to create XML nodes, optimize it with Cursors, etc. wrote the full sp and accidently noticed the For XML clause. Explored further and found it was a simple and powerful with nested For XML queries and I got the whole legacy XML with <10 line query and still keep the well designed DB tables and names.

 

The MSDN has detailed info with many examples to refer, so not adding much here.. http://msdn.microsoft.com/en-us/library/ms188276(v=sql.110).aspx

 

An example from MSDN for quick reference…

SELECT ProductCategoryID, Name as CategoryName,

       (SELECT ProductSubCategoryID, Name SubCategoryName,

               (SELECT ProductModel.ProductModelID,

                       ProductModel.Name as ModelName,

                       (SELECT ProductID, Name as ProductName, Color

                        FROM   Production.Product

                        WHERE  Product.ProductModelID =

                               ProductModel.ProductModelID

                        FOR XML AUTO, TYPE)

                FROM   (SELECT distinct ProductModel.ProductModelID,

                               ProductModel.Name

                        FROM   Production.ProductModel,

                               Production.Product

                        WHERE  ProductModel.ProductModelID =

                               Product.ProductModelID

                        AND    Product.ProductSubCategoryID =

                               ProductSubCategory.ProductSubCategoryID)

                                  ProductModel

                FOR XML AUTO, type

               )

        FROM Production.ProductSubCategory

        WHERE ProductSubCategory.ProductCategoryID =

              ProductCategory.ProductCategoryID

        FOR XML AUTO, TYPE, ELEMENTS

       )

FROM Production.ProductCategory

ORDER BY ProductCategoryID

FOR XML AUTO, TYPE

 

 

~ Tony Julien