在SQL Server中将行转换为XML格式 [英] Converting rows to XML format in SQL Server

问题描述

我有如下要求。

上图的DDL和DML脚本为

CREATE TABLE #example
    ([CCP_DETAILS_SID] int, [ACCOUNT_GROWTH] int, [PRODUCT_GROWTH] int, [PROJECTION_SALES] numeric(22,6), [PROJECTION_UNITS] numeric(22,6), [PERIOD_SID] int)
;

INSERT INTO #example
    ([CCP_DETAILS_SID], [ACCOUNT_GROWTH], [PRODUCT_GROWTH], [PROJECTION_SALES], [PROJECTION_UNITS], [PERIOD_SID])
VALUES
    (30001, 0, 0, 1505384.695, 18487.25251, 1801),
    (30001, 0, 0, 1552809.983, 18695.75536, 1802),
    (30001, 0, 0, 1595642.121, 18834.75725, 1803),
    (30002, 0, 0, 10000.32, 18834.75725, 1801),
    (30002, 0, 0, 1659124.98, 18834.75725, 1802),
    (30002, 0, 0, 465859546.6, 18834.75725, 1803)
;

我必须将上面的结果转换为如下所示的XML格式(输出)。

ccp_details_sid           xml_format_string

30001                      <period>  
                           <period_sid period_sid=1801>
                           <PROJECTION_SALES>1505384.695</PROJECTION_SALES>
                           <PROJECTION_UNITS>18487.25251<PROJECTION_UNITS>
                           <ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
                           <PRODUCT_GROWTH>0</PRODUCT_GROWTH>
                           </period_sid>
                            <period_sid period_sid=1802>
                           <PROJECTION_SALES>1552809.983</PROJECTION_SALES>
                           <PROJECTION_UNITS>18695.75536<PROJECTION_UNITS>
                           <ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
                           <PRODUCT_GROWTH>0</PRODUCT_GROWTH>
                           </period_sid>
                           <period_sid period_sid=1802>
                           <PROJECTION_SALES>1595642.121</PROJECTION_SALES>
                           <PROJECTION_UNITS>18834.75725<PROJECTION_UNITS>
                           <ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
                           <PRODUCT_GROWTH>0</PRODUCT_GROWTH>
                           </period_sid>
                           </period>

30002                      Same like above      

我是XML的新手，因此无法快速完成。我已将Marc_s解决方案与cross apply一起使用，但无法实现。

注意：我的主要目标是，在上图中，如果我们看到单个ccp_Detailssid有三条记录，那么我想使用XML(上面提到的)将其转换为一行。

推荐答案

以下内容适用于您：

SELECT  t.CCP_DETAILS_SID,
        (   SELECT  PERIOD_SID AS [@period_sid],
                    x.PROJECTION_SALES,
                    x.PROJECTION_UNITS,
                    x.ACCOUNT_GROWTH,
                    x.PRODUCT_GROWTH
            FROM    #Example AS x
            WHERE   x.CCP_DETAILS_SID = t.CCP_DETAILS_SID
            FOR XML PATH('period_sid'), TYPE, ROOT('period')
        ) AS xml_format_string
FROM    #Example AS t
GROUP BY t.CCP_DETAILS_SID;

它实质上使用以下命令获取CCP_DETAILS_SID的所有唯一值：

SELECT  t.CCP_DETAILS_SID
FROM    #Example AS t
GROUP BY t.CCP_DETAILS_SID;

然后，对于这些值中的每个值，使用相关的子查询来形成XML。要点是：

• 在别名前使用@创建属性，例如AS [@period_sid]
• 使用PATH('period_sid')为每行的容器命名
• 使用ROOT('period')命名外部节点。

Example on DBFiddle

这篇关于在SQL Server中将行转换为XML格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！