Creating XML with SQL Server
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
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')