CODEWITHSUNDEEP
sql-serverxquery-sql
Cherry
Cherry

Reputation: 23

xml.value()in Xquery only returns 1 record

I have multiple "abc" nodes in xml file but it is returning the value for the first row as we have specified [1] , if I write [2] , it returns the second row.

I want to return all the values of "Element" in all "abc" loops.

Query Used:

Insert Into @temp(aa,bb)
SELECT 
y.b.value('(abc/Segment/Element)[1]', 'NVARCHAR(50)') ,
y.b.value('(abc/Segment/Element)[1]', 'NVARCHAR(50)') 
FROM @x.nodes('(a1/b1/c1)') AS y(b)

Below Query is giving the desired result but I can't use the query as I need to access other fields from location a1/b1/c1 in same query 

Insert Into @temp(aa,bb)
SELECT 
y.b.value('(Segment/Element)[1]', 'NVARCHAR(50)') ,
y.b.value('(Segment/Element)[1]', 'NVARCHAR(50)') 
FROM @x.nodes('(a1/b1/c1/abc)') AS y(b)

Upvotes: 0

Views: 701

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Use a cross apply on y.b to get the rows for abc.

select z.b.value('(Segment/Element)[1]', 'nvarchar(50)') ,
       z.b.value('(Segment/Element)[1]', 'nvarchar(50)') 
from @x.nodes('(a1/b1/c1)') as y(b)
  cross apply y.b.nodes('abc') as z(b);

Upvotes: 1

Related Questions