問題描述
如何使用 T-SQL 查詢 XML 數據中的多個節點并將結果輸出為單個逗號分隔的字符串?
How can I query multiple nodes in XML data with T-SQL and have the result output to a single comma separated string?
例如，我想獲取以下 XML 中所有目的地名稱的列表，使其看起來像德國、法國、英國、意大利、西班牙、葡萄牙"
For example, I'd like to get a list of all the destination names in the following XML to look like "Germany, France, UK, Italy, Spain, Portugal"
<Holidays>
<Summer>
<Regions>
<Destinations>
<Destination Name="Germany" />
<Destination Name="France" />
<Destination Name="UK" />
<Destination Name="Italy" />
<Destination Name="Spain" />
<Destination Name="Portugal" />
</Destinations>
<Regions>
</Summer>
</Holidays>
我正在嘗試類似的東西:
I was trying something like:
Countries = [xmlstring].value('/Holidays/Summer/Regions/Destinations/@Name', 'varchar')
推薦答案
首先，要從源 XML 表中獲取記錄列表，需要使用 .nodes 函數(演示):
First, to get a list of records from a source XML table, you need to use the .nodes function (DEMO):
select Destination.value('data(@Name)', 'varchar(50)') as name
from [xmlstring].nodes('/Holidays/Summer/Regions/Destinations/Destination')
D(Destination)
示例輸出:
| NAME |
-------------
| Germany |
| France |
| UK |
| Italy |
| Spain |
| Portugal |
從這里開始，您希望將目標值連接到一個逗號分隔的列表中.不幸的是，這不是 T-SQL 直接支持的，因此您必須使用某種解決方法.如果您正在使用多行的源表，最簡單的方法是 FOR XML PATH('') 技巧.在這個查詢中，我使用一個名為 Data 的源表，并將 XML 拆分為單獨的記錄，然后我使用 FOR XML PATH('') CROSS APPLY 生成逗號分隔的行.最后，從結果中去除最后的 , 以創建列表 (演示):
From here, you want to concatenate the destination values into a comma-separated list. Unfortunately, this is not directly supported by T-SQL, so you'll have to use some sort of workaround. If you're working with a source table using multiple rows, the simplest method is the FOR XML PATH('') trick. In this query I use a source table called Data, and split out the XML into separate records, which I then CROSS APPLY with FOR XML PATH('') to generate comma-separated rows. Finally, the final , is stripped from the result to create the list (DEMO):
;with Destinations as (
select id, name
from Data
cross apply (
select Destination.value('data(@Name)', 'varchar(50)') as name
from [xmlstring].nodes('/Holidays/Summer/Regions/Destinations/Destination') D(Destination)
) Destinations(Name)
)
select id, substring(NameList, 1, len(namelist) - 1)
from Destinations as parent
cross apply (
select name + ','
from Destinations as child
where parent.id = child.id
for xml path ('')
) DestList(NameList)
group by id, NameList
示例輸出(請注意，我在測試數據中添加了另一個 XML 片段以制作更復雜的示例):
Sample Output (Note that I've added another XML fragment to the test data to make a more complex example):
| ID | COLUMN_1 |
-----------------------------------------------
| 1 | Germany,France,UK,Italy,Spain,Portugal |
| 2 | USA,Australia,Brazil |
這篇關于使用 T-SQL 查詢 XML 字段的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！