sql server - Get Sibling Node using SQL -

sql server - Get Sibling Node using SQL -

i have xml string below

<message> <result>a</result> <note>aa</note> <note>aaa</note> <note>aaaa</note> <result>b</result> <note>bb</note> <note>bbb</note> <note>bbb</note> </message>

i want select immediate 3 note nodes of each result node using sql , append them each other. how that?

for result -> aa+aaa+aaaa result b -> bb+bbb+bbbb

lets have xml looks this:

class="lang-xml prettyprint-override"><item result="a"> <note>aa</note> <note>aaa</note> <note>aaaa</note> </item> <item result="b"> <note>bb</note> <note>bbb</note> <note>bbb</note> </item>

then easy generate result want shredding on item , using for xml path('') concatenate note nodes.

class="lang-sql prettyprint-override">select t.x.value('@result', 'nvarchar(100)') result, ( select t2.x.value('text()[1]', 'nvarchar(100)')+' ' t.x.nodes('note') t2(x) xml path(''), type ).value('text()[1]', 'nvarchar(100)') note @xml.nodes('/item') t(x);

you can rebuild xml desired format using query.

class="lang-sql prettyprint-override">set @xml = @xml.query('for $n in /message/result homecoming element item { attribute result{$n/text()}, /message/note[. >> $n][position() < 4] }');

sql fiddle

another alternative shred on message/* , enumerate rows using row_number() in cte. utilize cte in main query , in sub-query uses for xml path('') concatenate strings bring together uses generated numbers.

class="lang-sql prettyprint-override">with c ( select t.x.value('text()[1]', 'nvarchar(100)') value, t.x.value('local-name(.)', 'nvarchar(100)') nodename, row_number() over(order t.x)as rn @xml.nodes('/message/*') t(x) ) select c1.value result, ( select c2.value+' ' c c2 c2.rn > c1.rn , c2.rn < c1.rn + 4 xml path(''), type ).value('text()[1]', 'nvarchar(100)') note c c1 c1.nodename = 'result'

sql fiddle

note: technique using row_number() enumerate nodes in xml document relies on knowledge of internal implementation of how sql server parses xml. behaviour not documented microsoft. adam machanic has blogged here: uniquely identifying xml nodes dense_rank

sql sql-server sql-server-2008