0

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.