SQL Server 2012创建具有可变数量属性的XML

Question

我有一个像这样的表

;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安全字符串（无空间等）

Answer 2

其他具有已知属性集的选项将是PIVOT的组合和事实，即XML将省略任何NULL值。

;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 l1.ID 
     ,(
        SELECT
           MAX(CASE WHEN PROPERTY_NAME = 'ABC' THEN PROPERTY_VALUE END) AS [@ABC]
          ,MAX(CASE WHEN PROPERTY_NAME = 'AD' THEN PROPERTY_VALUE END) AS [@AD]
          ,MAX(CASE WHEN PROPERTY_NAME = 'AQ' THEN PROPERTY_VALUE END) AS [@AQ]
          ,MAX(CASE WHEN PROPERTY_NAME = 'AES' THEN PROPERTY_VALUE END) AS [@AES]
          ,MAX(CASE WHEN PROPERTY_NAME = 'FS' THEN PROPERTY_VALUE END) AS [@FS]
         FOR XML PATH('code'),TYPE
      ) AS YourCodeXML
FROM cte_list AS l1
GROUP BY l1.ID

结果

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

优势：您不需要在字符串级别上工作
退回：如果有一个新属性，则必须将其添加到列表中。

另一个机会是动态创建的SQL ...