multiple nodes with same name as rows-CodePudding

This may look like a duplicate, but what I can find are getting multiple rows from nodes with elements inside, like

<products>
  <product>
    <image>url1</image>
  </product>
  <product>
    <image>url1</image>
  </product>
</products>

What I have is an XML-field in a table (with PLU as an integer)

<product>
  <images>
    <image>url1</image>
    <image>url2</image>
    <image>url3</image>
  </images>
</product>

I want

image
-----
url1
url2
url3

I tried

select a.image.value('image','nvarchar(max)') as image 
from products r
cross apply r.xml.nodes('/product/images') a(image) where PLU='8019'

but that gives

XQuery [products.xml.value()]: 'value()' requires a singleton (or empty sequence), 
found operand of type 'xdt:untypedAtomic *'

As I want the value of each node, not of subnodes, I tried

select a.image.value('.','nvarchar(max)') ...

but that gave me only one row with url1url2url3 all urls concatenated.

select a.image.value('image[1]','nvarchar(max)')

gives only url1

CodePudding user response：

PLease try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT @tbl (xmldata) VALUES
(N'<product>
  <images>
    <image>url1</image>
    <image>url2</image>
    <image>url3</image>
  </images>
</product>');
-- DDL and sample data population, end

SELECT ID
    , c.value('text()[1]','nvarchar(max)') AS image_url
FROM @tbl
CROSS APPLY xmldata.nodes('/product/images/image') AS t(c);

Output

 ---- ----------- 
| ID | image_url |
 ---- ----------- 
|  1 | url1      |
|  1 | url2      |
|  1 | url3      |
 ---- -----------

CodePudding user response：

A shorter solution than Yitzhak Khabhinsky is this by Martin Boje Carpentier elsewhere, but I'll award the points to Yitzhak

select a.image.value('.','nvarchar(max)') as image 
from products r
cross apply r.xml.nodes('/product/images/*') a(image) where PLU='8019'