Query @xml variable to get a rowset
I have an xml data that looks like below.
DECLARE @XmlContent XML
SET @XmlContent =
'<Entities>
<Entity type = "5">
<item id ="1"/>
<item id ="2"/>
<item id ="2"/>
</Entity>
<Entity type = "6">
<item id ="3"开发者_StackOverflow中文版/>
<item id ="4"/>
<item id ="5"/>
</Entity>
</Entities>'
I want to select data from this and insert into a table in the following format -
------------ Type Id ------------ 5 1 5 2 5 2 6 3 6 4 6 5
Can some one help me to write query for this in sql server?
select
ent.value('@type', 'int') as Type,
row.value('@id', 'int') as ID
from
@XmlContent.nodes('/Entities/Entity') foo(ent)
cross apply ent.nodes('item') bar(row)
精彩评论