Need to generate XML from SQL in this following format

Question

I have a table in SQL server database and I am generating an XML from the table using this query.

SELECT OrderNumber, CustomerID, ITEM
FROM OrderHeader
FOR XML RAW('Order'), ELEMENTS, ROOT('Orders')

I get an XML like this:

<Orders>
<Order>
    <OrderNumber>SO101</OrderNumber>
    <CustomerID>1</CustomerID>
            <Item>item 1</Item>
</Order>
<Order>
    <OrderNumber>SO102</OrderNumber>
    <CustomerID>1</CustomerID>
            <Item>item 2</Item>
</Order>
</Orders>

But I need to generate the XML in the below format.

<Orders>
<Order OrderNumber= 'SO101'>        
    <CustomerID>1</CustomerID>
            <Item>item 1</Item>
</Order>
  <Order OrderNumber= 'SO102'>      
    <CustomerID>1</CustomerID>
            <Item>item 2</Item>
</Order>
</Orders>

i.e. the Order number column needs to be displayed as an attribute of the Order Node.

How can I achieve this?

Answer 1

I think you'll find that XML RAW will limit your options.

Try using XML PATH, something like this:

SELECT OrderNumber AS @Id, CustomerID, ITEM
FROM OrderHeader
FOR XML PATH('Order'), ELEMENTS, ROOT('Orders')

I'd like to see if that does the trick, trying to better my XML at the moment too.

Answer 2

Try this,

SELECT 
    OrderNumber AS "Order/@OrderNumber", 
    CustomerID, 
    ITEM
FROM 
    OrderHeader
FOR XML PATH, ELEMENTS, ROOT('Orders')

For more details