問題描述

我在 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 列并返回特定節點的所有值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！