问题描述
如何使用 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 |