SQL and XML insert new Node

Question

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?

Answer 1

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