sql-server - SQL Server 2012 创建具有可变数量属性的 xml

Question

我有一张这样的 table

;with cte_list(ID, PROPERTY_NAME, PROPERTY_VALUE) as 
(
    select '100', 'ABC', 12 union all
    select '100', 'AD',  32 union all
    select '100', 'AQ',   9 union all
    select '200', 'AES',  1 union all
    select '200', 'FS',   5
)

我想要这样的结果

ID      XML
---     -----------------------------------
100,    <codes ABC="12" AD="32" AQ="9"/>
200,    <codes AES="1" FS="5" />

这可能吗？

提前致谢

Answer 1

一种方法

;with
cte_list(ID, PROPERTY_NAME, PROPERTY_VALUE)
as (
select '100', 'ABC', 12 union all
select '100', 'AD',  32 union all
select '100', 'AQ',   9 union all
select '200', 'AES',  1 union all
select '200', 'FS',   5
)
Select A.ID
      ,XML = cast('<codes '+Stuff((Select concat(' ',Property_Name,'="',Property_Value,'"') From cte_list Where ID=A.ID For XML Path ('')),1,1,'') + ' />' as xml)
 From (Select Distinct ID from cte_list) A

返回

ID  XML
100 <codes ABC="12" AD="32" AQ="9" />
200 <codes AES="1" FS="5" />

请注意:property_name 应该是 XML 安全字符串(没有空格等)