XML order using FOR XML PATH

Question

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!

Community · Accepted Answer · 2017-05-23 10:33:01Z

5

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>

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Nov 22, 2016 at 16:42

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pரதீப் Over a year ago

That's weird.. at least to me.. +1

John Cappelletti Over a year ago

Plus1 - shockingly simple! I've twisted myself into knots to create the proper nesting.

TT. Over a year ago

Yeah it's crazy how that works. I used to do it with subqueries until I saw that trick recently. Shockingly simple indeed, but also a bit counter intuitive IMO.

Gottfried Lesigang Over a year ago

Great transfer! :-)

Collectives™ on Stack Overflow

XML order using FOR XML PATH

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related