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?

wBob · Accepted Answer · 2016-08-06 18:11:39Z

3

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

answered Aug 6, 2016 at 18:11

wBob

10.4k2 gold badges26 silver badges44 bronze badges

Add a comment |

Stack Exchange Network

SQL and XML insert new Node

1 Answer 1

Your Answer

Hot Network Questions

SQL and XML insert new Node

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions