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.
Add a comment
|
2 Answers
There are two parts to your question:
- getting values out of xml
- 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
Comments
Using XQuery you can simply select the values using XPath and join them together using a dot.
string-join(/items/item/@value, '.')
