sql-server - 在 SQL Server 中将行转换为 XML 格式

Question

我有如下要求。

上图的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_details_sid 有三个记录，那么我想使用 XML(如上所述)将其转换为一行。

Answer 1

以下内容适合您:

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