Hcfyk

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: 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:

<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.

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)')

Depending on your actual XML structure you could consider using a loop like this:

DECLARE @xml XML



SELECT @xml = '<R>

  <I>A</I>

  <I>B</I>

  <I>C</I>

  <J>X</J>

  <J>Y</J>

  <J>Z</J>

</R>'



SELECT 

    Tbl.Col.query('for $i in I return $i').value('.', 'nvarchar(max)'),

    Tbl.Col.query('for $i in J return $i').value('.', 'nvarchar(max)')

FROM @xml.nodes('R') Tbl(Col);

which outputs this:

(No column name) | (No column name) 

---------------  | --------------- 

ABC              | XYZ 

See this fiddle

If your elements and values really are short and distinct this works:

declare @s varchar(99) = '<R><I>A</I><I>B</I><I>C</I></R>';



select

    @s,

    REPLACE(TRANSLATE ( @s, '<>I/R', '     '), ' ', '');

For non-trivial XML it may struggle, though.