Concatenate all values of the same XML element using XPath/XQuery I have an XML value like this:

&lt;R&gt;
  &lt;I&gt;A&lt;/I&gt;
  &lt;I&gt;B&lt;/I&gt;
  &lt;I&gt;C&lt;/I&gt;
  ...
&lt;/R&gt;


I want to concatenate all I values and return them as a single string: ABC....

Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and apply .values('text()[1]', ...) to the result:

SELECT
  (
    SELECT
      n.n.value('text()[1]', 'varchar(50)') AS [text()]
    FROM
      @MyXml.nodes('/R/I') AS n (n)
    FOR XML
      PATH (''),
      TYPE
  ).value('text()[1]', '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:

&lt;R&gt;
  &lt;I&gt;A&lt;/I&gt;
  &lt;I&gt;B&lt;/I&gt;
  &lt;I&gt;C&lt;/I&gt;
  ...
  &lt;J&gt;X&lt;/J&gt;
  &lt;J&gt;Y&lt;/J&gt;
  &lt;J&gt;Z&lt;/J&gt;
  ...
&lt;/R&gt;


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.
This might work for you:

select @MyXml.value('/R[1]', 'varchar(50)')


It picks up all text() elements from the first R and below.

If you just want all text() you can do 

select @MyXml.value('.', 'varchar(50)')


If you want the values for I and J separate do this instead.

select @MyXml.query('/R/I/text()').value('.', 'varchar(50)'),
       @MyXml.query('/R/J/text()').value('.', 'varchar(50)')