CODEWITHSUNDEEP
sql-serverxmlt-sql
Val K
Val K

Reputation: 331

XML order using FOR XML PATH

I wrote an XML query that creates an order in the sligthly different format that I would like it to:

select
    'sample' "@ponumber",
    'xxxxxx' "@cust",
    'yyyyyy' "@shipto",
    '999999'  "line/material",
    '20'      "line/qty",
    '777777'  "line/material",
    '20'      "line/qty"
for 
    xml path('root')

I get the following output:

<root ponumber="sample" cust="xxxxxx" shipto="yyyyyy">
  <line>
    <material>999999</material>
    <qty>20</qty>
     <material>777777</material>
    <qty>20</qty>
  </line>
</root>

The desired output is this:

<root ponumber="sample" cust="xxxxxx" shipto="yyyyyy">
  <line>
    <material>999999</material>
    <qty>20</qty>
  </line>
  <line>
    <material>777777</material>
    <qty>20</qty>
  </line>
</root>

What do I need to modify in my query?

Thank you!

Upvotes: 1

Views: 202

Answers (1)

TT.
TT.

Reputation: 16143

This would give you the desired output. The way it works is explained here on Stack Overflow.

select
    'sample' "@ponumber",
    'xxxxxx' "@cust",
    'yyyyyy' "@shipto",
    '999999'  "line/material",
    '20'      "line/qty",
    '',
    '777777'  "line/material",
    '20'      "line/qty"
for 
    xml path('root')

Produces

<root ponumber="sample" cust="xxxxxx" shipto="yyyyyy">
  <line>
    <material>999999</material>
    <qty>20</qty>
  </line>
  <line>
    <material>777777</material>
    <qty>20</qty>
  </line>
</root>

Upvotes: 5

Related Questions