Thursday, September 5, 2013

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..
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 =
                        FOR XML AUTO, TYPE)
                FROM   (SELECT distinct ProductModel.ProductModelID,
                        FROM   Production.ProductModel,
                        WHERE  ProductModel.ProductModelID =
                        AND    Product.ProductSubCategoryID =
                FOR XML AUTO, type
        FROM Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID =
FROM Production.ProductCategory
ORDER BY ProductCategoryID
~ Tony Julien

No comments: