CODEWITHSUNDEEP
sqlsql-serverxmlxsd
CleanBold
CleanBold

Reputation: 1592

SQL Server - FOR XML PATH : how to add xmlns:xsd to the root element?

I am using this 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?

Upvotes: 0

Views: 2020

Answers (2)

Svein Fidjest&#248;l
Svein Fidjest&#248;l

Reputation: 3206

In general you can always convert to nvarchar(max) and do text replacements.

This means you can do it like this

select replace(cast((
    select SalesOrderID,OrderDate,DueDate,rowguid from Sales.SalesOrderHeader
    where SalesOrderID between 43661 and 43662
    for xml path('Order'), Root('AllOrders')
) as nvarchar(max)), N'<AllOrders>', N'<AllOrders xmlns:xsd="http://foo.com/bar">')

Although I would much prefer you do it as per the suggestion from @AlexK

Upvotes: 3

Alex K.
Alex K.

Reputation: 175846

You can use XMLNAMESPACES;

WITH XMLNAMESPACES ('http://foo.com/bar' AS xsd)
select SalesOrderID,OrderDate,DueDate...

Upvotes: 3

Related Questions