How can I query a SQL Server XML column and return all values for a specific node?

Question

I have the following XML in a XML column in SQL Server.

  <qualifiers>
    <qualifier>
      <key>111</key>
      <message>a match was not found</message>
    </qualifier>
    <qualifier>
      <key>222</key>
      <message>a match was found</message>
    </qualifier>
    <qualifier>
      <key>333</key>
      <message>error</message>
    </qualifier>
  </qualifiers>

How can I write TSQL to return all the values in qualifiers/qualifier/message in a comma delimited string? My goal is to have the query return the values from the XML in a single column for each row.

The results should look like this:

"a match was not found, a match was found, error"

Example: stackoverflow.com/questions/4379039/…

xQbert
– xQbert

2014-12-23 19:11:37 +00:00
Commented Dec 23, 2014 at 19:11 — xQbert
– xQbert, Commented Dec 23, 2014 at 19:11

knkarthick24 · Accepted Answer · 2014-12-23 19:32:11Z

4

SQLFiddle for the same: Solution as per @xQbert suggested

create table Temp (col1 xml)
go

insert into Temp (col1)
values('<qualifiers>
    <qualifier>
      <key>111</key>
      <message>a match was not found</message>
    </qualifier>
    <qualifier>
      <key>222</key>
      <message>a match was found</message>
    </qualifier>
    <qualifier>
      <key>333</key>
      <message>error</message>
    </qualifier>
  </qualifiers>')
go

SELECT
    STUFF((SELECT 
              ',' + fd.v.value('(.)[1]', 'varchar(50)')
           FROM 
              Temp
           CROSS APPLY
              col1.nodes('/qualifiers/qualifier/message') AS fd(v)
           FOR XML PATH('')
          ), 1, 1, '')

answered Dec 23, 2014 at 19:32

knkarthick24

3,24619 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

itzik Paz · Accepted Answer · 2014-12-23 19:23:18Z

I believe that is what you are looking for : -- it is from the LINK : -- http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
a.b.value('Colors[1]/Color1[1]','varchar(10)') AS Color1,
a.b.value('Colors[1]/Color2[1]','varchar(10)') AS Color2,
a.b.value('Colors[1]/Color3[1]','varchar(10)') AS Color3,
a.b.value('Colors[1]/Color4[1]/@Special','varchar(10)')+' '+
+a.b.value('Colors[1]/Color4[1]','varchar(10)') AS Color4,
a.b.value('Colors[1]/Color5[1]','varchar(10)') AS Color5,
a.b.value('Fruits[1]/Fruits1[1]','varchar(10)') AS Fruits1,
a.b.value('Fruits[1]/Fruits2[1]','varchar(10)') AS Fruits2,
a.b.value('Fruits[1]/Fruits3[1]','varchar(10)') AS Fruits3,
a.b.value('Fruits[1]/Fruits4[1]','varchar(10)') AS Fruits4
FROM @MyXML.nodes('SampleXML') a(b)

Collectives™ on Stack Overflow

How can I query a SQL Server XML column and return all values for a specific node?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related