开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜