14

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.

11

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)')
  • The last one was suggested to me in chat but I find the first one extremely helpful too. I might be able to generate the XML data differently so that I can apply the first method to it. – Andriy M May 6 at 12:10
7

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

  • 1
    This is really good. I can easily adapt it to include delimiters whenever I need to. And it's not too verbose to be used as it is in case I want to extract strings both with and without delimiters in a uniform way. – Andriy M May 6 at 12:34
0

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.

  • The elements can be short, but the values in general are not, and I can't be sure they won't contain same characters as the element names do. Appreciate the outside the box approach, though. – Andriy M May 7 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.