Seems pretty straight forward to me OR Am I missing something?
DECLARE @xmltable TABLE
(
ID INT IDENTITY,
XMLCOL NVARCHAR(MAX)
)
INSERT @xmltable
(
[XMLCOL]
)
SELECT '<Items>
<Item Name="Fred">
<ItemReference>123</ItemReference>
<ItemType>5</ItemType>
<ItemID>fcd13330-4983-4402-b27c-ab01e1a2438e</ItemID>
</Item>
<Item Name="Boo">
<ItemReference>234</ItemReference>
<ItemType>5</ItemType>
<ItemID>90873135-15a7-40e9-bf0d-7152300b66e5</ItemID>
</Item>
</Items>'
SELECT Item_name
, Item_type
FROM ( SELECT CAST(XMLCOL AS XML) XMLCOL
FROM @xmltable
) xmltable
CROSS APPLY XMLCOL.nodes('./Items/Item') x ( col )
CROSS APPLY ( SELECT x.col.value('@Name', 'varchar(50)')
, x.col.value('(./ItemType)[1]', 'varchar(50)')
) ColumnAlias ( Item_name, Item_type )
3 People are following this question.