Reputation: 331
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
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