sql server - for xml path - how to add xmlns:xsd to the root element?



I am using below query:



select SalesOrderID,OrderDate,DueDate,rowguid from Sales.SalesOrderHeader
where SalesOrderID between 43661 and 43662
for xml path('Order'), Root('AllOrders')


The output of this is:



<AllOrders>
<Order>
<SalesOrderID>43661</SalesOrderID>
<OrderDate>2005-07-01T00:00:00</OrderDate>
<DueDate>2005-07-13T00:00:00</DueDate>
<rowguid>D91B9131-18A4-4A11-BC3A-90B6F53E9D74</rowguid>
</Order>
<Order>
<SalesOrderID>43662</SalesOrderID>
<OrderDate>2005-07-01T00:00:00</OrderDate>
<DueDate>2005-07-13T00:00:00</DueDate>
<rowguid>4A1ECFC0-CC3A-4740-B028-1C50BB48711C</rowguid>
</Order>
</AllOrders>


I want this as expected output (xmlns:xsd added):



<AllOrders xmlns:xsd="some schema URL">
<Order>
<SalesOrderID>43661</SalesOrderID>
<OrderDate>2005-07-01T00:00:00</OrderDate>
<DueDate>2005-07-13T00:00:00</DueDate>
<rowguid>D91B9131-18A4-4A11-BC3A-90B6F53E9D74</rowguid>
</Order>
<Order>
<SalesOrderID>43662</SalesOrderID>
<OrderDate>2005-07-01T00:00:00</OrderDate>
<DueDate>2005-07-13T00:00:00</DueDate>
<rowguid>4A1ECFC0-CC3A-4740-B028-1C50BB48711C</rowguid>
</Order>
</AllOrders>


What modification do I make to query to achieve the above output?


No comments:

Post a Comment