Select multiple values in XML file and concatenation them with xQuery

Question

I have the following XML:

<items>
 <item value="1"/>
 <item value="2"/>
 <item value="4"/>
</items>

and I would like to select all item value and concatenate them like this - see below - with XQuery :

1.2.4

Any ideas? Thanks in advance.

Answer 1

There are two parts to your question:

1. getting values out of xml
2. concatenating multiple values together

The second part of your question has been covered off very nicely in the answers to this question.

In terms of getting the values out of your xml, try the below:

-- Your xml variable

DECLARE @xml AS XML = '<items>
                         <item value="1"/>
                         <item value="2"/>
                         <item value="4"/>
                       </items>'


-- Example of selecting the values into rows

SELECT 
  item.value('.', 'int') 
FROM 
   @xml.nodes('/items/item/@value') as T1(item) 

-- Use your favourite/'best for your circumstance' method of 
-- concatenating the rows into one string 
-- see https://stackoverflow.com/q/194852/1208914 for other ways

SELECT 
  item.value('.', 'varchar(50)') + ' ' as 'data()' 
FROM 
  @xml.nodes('/items/item/@value') as T1(item) 
for xml path('')

I've put the code above into a sql fiddle that you can access and play with here: http://sqlfiddle.com/#!6/d41d8/18144/0

Answer 2

Using XQuery you can simply select the values using XPath and join them together using a dot.

string-join(/items/item/@value, '.')