Represent related tables in Azure SQL in XML format(defined in XSD schema)

Question

Lets take Nortwind DB .

We'll have a XSD schema of the DB with all the relationships.
The design of the table looks like:

using below script,

========================

SELECT OrderID, CustomerID, EmployeeID,
(select OrderDetails.OrderID, ProductID, UnitPrice, Quantity
from OrderDetails
WHERE OrderDetails.OrderID = Orders.OrderID FOR XML AUTO, ELEMENTS)
from Orders FOR XML AUTO, ELEMENTS;

========================

I got attached output234545-result.xml

Through scripts we can create nested xmls but that is tough to maintain. Is there any other easy way(without 3rd party software help) to get such nested xmls?

Early help is highly appreciated. We can use sql scripts or Azure Data Factory transformation or any azure services

Answer 1

Hi @Partha Das ,

I would suggest the following for improvements:

1. Use FOR XML PATH(...) ... option, It is most powerful and flexible clause to create an XML.
2. It is possible to use XQuery's FLWOR expression to fine tune the final XML.