如何查询 SQL Server XML 列并返回特定节点的所有值? [英] How can I query a SQL Server XML column and return all values for a specific node?

问题描述

我在 SQL Server 的 XML 列中有以下 XML.

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>

如何编写 TSQL 以逗号分隔的字符串返回限定符/限定符/消息中的所有值?我的目标是让查询在每一行的单个列中返回 XML 中的值.

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.

结果应该是这样的:

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

推荐答案

SQLFiddle for相同:根据@xQbert 建议的解决方案

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

这篇关于如何查询 SQL Server XML 列并返回特定节点的所有值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！