开发者

Xquery in SQL: Selecting a subset

开发者 https://www.devze.com 2023-01-26 07:12 出处:网络
Given the following xml variable: declare @x xml select @x = \'<Details> <Description> <Attributes>

Given the following xml variable:

    declare @x xml
    select @x = '<Details>
<Description>
<Attributes>
   <Name>A</Name>
   <Values><RecordId>1</RecordId><RecordId>2</RecordId></Values>
</Attributes>
<Attributes>
    <Na开发者_JS百科me>B</Name>
    <Values><RecordId>3</RecordId><RecordId>4</RecordId></Values>
</Attributes>
</Description>
</Details>'

I am trying to get all Name values with all their RecordIds. I would like to do it in one statement. I have the following now.

create table #xml (element varchar(60))

insert into #xml
select RoleDetails.item.value('(Name)[1]', 'varchar(60)')
from 
  @x.nodes('/Details/Description/Attributes') AS RoleDetails(item)

The format I'm looking for would be:

A 1
  2

B 3
  4


It became easier to save the subset in the XML format and allow for code to do XML manipulation.

0

精彩评论

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