SQL and XML insert new Node

xml

I have an SQL Query to make an XML Document. Everything works except one thing:

A short insert of my SQL Query at this time is:

select OITM.ItemCode, OITM.ItemName, OITM.Price

Output in XML:

<DocumentElement>
  <article>
      <ItemCode>12345</ItemCode>
      <ItemName>test</ItemName>
      <Price>20.00</Price>
  </article>
</DocumentElement>

I want this Structure:

<DocumentElement>
      <article>
          <ItemCode>12345</ItemCode>
          <ItemName>test</ItemName>
          <Prices>
               <Price>20.00</Price>
          </Prices>
      </article>
    </DocumentElement>

How can i insert the <Prices> tag?

Best Answer

If you are using FOR XML PATH you can specify nested elements using forward slash (/), like this:

DECLARE @yourTable TABLE (
    ItemCode    INT, 
    ItemName    VARCHAR(10), 
    Price       MONEY
)

INSERT INTO @yourTable VALUES
    ( 12345, 'test', 20 )

SELECT 
    ItemCode, 
    ItemName, 
    Price AS "Prices/Price"
FROM @yourTable
FOR XML PATH('article'), ROOT('DocumentElement'), TYPE
Related Question