Mar 16

Written by: Steve Gray
3/16/2011 2:30 PM 

In this example I need to return the contents of a table in one XML field.

We’re looking at the item table(s) and we need the guid, quantity, and cost. Every item needs to be returned. The result desired will look like this:

<root>
  <item guid="6A639774-5EA9-440C-978B-FDC7DBF71949">
    <quantityOnHand>0.00000</quantityOnHand>
    <cost>0.00000</cost>
  </item>
  <item guid="D8FD406F-8CF2-47E6-B09D-E44106153C11">
    <quantityOnHand>0.00000</quantityOnHand>
    <cost>0.00000</cost>
  </item>
</root>

This piece of code will do the trick

The query is wrapped in an <item> tag by the ‘for xml path(‘item’)’ clause, adding ROOT(‘ROOT’) will wrap the whole thing in a <root> tag.

The query returns the one column with an odd system generated name. Assigning the whole thing to the @items variable allows me to return a one row, one column dataset to the user with a predictable column name.