I have an XML value like this:
<R> <I>A</I> <I>B</I> <I>C</I> ... </R>
I want to concatenate all
I values and return them as a single string:
Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and apply
.values('text()', ...) to the result:
SELECT ( SELECT n.n.value('text()', 'varchar(50)') AS [text()] FROM @MyXml.nodes('/R/I') AS n (n) FOR XML PATH (''), TYPE ).value('text()', 'varchar(50)') ;
However, I would like to do all that using XPath/XQuery methods only, something like this:
SELECT @MyXml. ? ( ? );
Is there such a way?
The reason I am looking for a solution in this direction is because my actual XML contains other elements too, for instance:
<R> <I>A</I> <I>B</I> <I>C</I> ... <J>X</J> <J>Y</J> <J>Z</J> ... </R>
And I would like to be able to extract both the
I values as a single string and the
J values as a single string without having to use an unwieldy script for each.