CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Chris Woodall
Chris Woodall

Reputation: 19

TSql xml query is concatenating values

When I query an xml column in a temp variable, the results are being concatenated if an xml element has multiple values. Below is an excerpt from the xml column-

 <metadata>
   <dataType>Date</dataType>
    <tags>
      <tag>SPA</tag>
      <tag>Timed Release</tag>
    </tags>
 </metadata>

Notice the two tag elements. I need one row for each tag element. Here is my query-

 SELECT id,
    dataType = [Type].value('(/metadata/tags)[1]', 'varchar(max)')
 FROM
@tmpProperty

that produces the below

 id   | dataType
 -----+-------------------
 6357 | SPATimed Release

if I change the query to

 SELECT id,
dataType = [Type].value('(/metadata/tags/tag)[1]', 'varchar(max)')
FROM
@tmpProperty

I get

 id   | dataType
 -----+-----------
 6357 | SPA

Both results are wrong. One concatenates into a single row instead of 2 rows, and the other just returns one value. I am using sql server 2012. How can I fix this?

Upvotes: 0

Views: 42

Answers (1)

GarethD
GarethD

Reputation: 69749

You need to use nodes() first to get multiple rows, then you can use value() to extract the data:

DECLARE @tmpProperty TABLE (ID INT IDENTITY, [Type] XML);
INSERT @tmpProperty([Type])
VALUES ('<metadata>
   <dataType>Date</dataType>
    <tags>
      <tag>SPA</tag>
      <tag>Timed Release</tag>
    </tags>
 </metadata>');

 SELECT id,
        dataType = Tags.value('(text())[1]', 'varchar(max)')
 FROM   @tmpProperty AS t
        CROSS APPLY t.[Type].nodes('/metadata/tags/tag') AS n (Tags);

Upvotes: 1

Related Questions