Creating XML with SQL Server

Question

I have a table with the data:

itemID          itemLocation    quantity
-------------------------------------------------------
B008KZK44E  COMMITED    1
B008KZK44E  PRIME       1
B008KZK2LE  COMMITED    1

I need to generate an xml with this node structure:

<inventoryItemData>
  <itemID type="FAMILY">B008KZK2LE</itemID>
  <availabilityDetail>
    <itemQuantity>
      <quantity unitOfMeasure="EA">1</quantity>
      <itemLocation>COMMITED</itemLocation>
    </itemQuantity>
  </availabilityDetail>
</inventoryItemData>
<inventoryItemData>
  <itemID type="FAMILY">B008KZK44E</itemID>
  <availabilityDetail>
    <itemQuantity>
      <quantity unitOfMeasure="EA">1</quantity>
      <itemLocation>COMMITED</itemLocation>
    </itemQuantity>
  </availabilityDetail>
  <availabilityDetail>
    <itemQuantity>
      <quantity unitOfMeasure="EA">1</quantity>
      <itemLocation>PRIME</itemLocation>
    </itemQuantity>
  </availabilityDetail>
</inventoryItemData>

The closer I get is this:

SELECT 
   'itemID' AS 'itemID/@type',
   itemID AS 'itemID',
   '' AS 'availabilityDetail',
   '' AS 'availabilityDetail/itemQuantity',
   'EA' AS 'availabilityDetail/itemQuantity/quantity/@unitOfMeasure',
   quantity AS 'availabilityDetail/itemQuantity/quantity',
   itemLocation AS 'availabilityDetail/itemQuantity/itemLocation'
FROM TABLE
FOR XML PATH ('inventoryItemData')

I'd appreciate any solution.

Thanks.

Solution 1

select
    'FAMILY' AS 'itemID/@type',
    t1.itemID AS 'itemID',
    (
        select
        'EA' AS 'itemQuantity/quantity/@unitOfMeasure',
        t2.quantity AS 'itemQuantity/quantity',
        t2.itemLocation AS 'itemQuantity/itemLocation'
        from Table1 as t2
        where t2.itemID = t1.itemID
        for xml path('availabilityDetail'), type
    )
from Table1 as t1
group by t1.itemID
for xml path ('inventoryItemData')

sql fiddle demo

Solution 2

Is this what you want?

SELECT 
   'FAMILY' AS 'itemID/@type',
   itemID AS 'itemID',
   '' AS 'availabilityDetail',
   '' AS 'availabilityDetail/itemQuantity',
   'EA' AS 'availabilityDetail/itemQuantity/quantity/@unitOfMeasure',
   quantity AS 'availabilityDetail/itemQuantity/quantity',
   itemLocation AS 'availabilityDetail/itemQuantity/itemLocation'
FROM @t
FOR XML PATH ('inventoryItemData')

Comments

[Yogesh Patel]

If I want to include NULL values as well in XML. Then what changes I have to do?

[Yogesh Patel]

If I want to include NULL values as well in XML. Then what changes I have to do?

[Roman Pekar]

What kind of output are you looking for, how exactly do you want to 'include NULL values'?