sql - 关于从 SQL 语句/过程生成的多层 XML 的问题

Question

我正在修改如何获取 SQL 语句（以及从存储过程中）并从查询结果生成 XML 输出。我在 XML 中的输出如下所示：

<Application>
    <Device AppEnabled="true" AsOf="06/03/2019 16:01:58 UTC" Name="MachineName">
        <Groups>
            <Group Name="Administrators" ReportChanges="true">
                <Members>
                    <Member Name="AdminUser1" />
                    <Member Name="AdminUser2" />
                    <Member Name="AdminUser3" />
                    <Member Name="AdminUser4" />
                </Members>
            </Group>
            <Group Name="Normal Users" ReportChanges="true">
                <Members>
                    <Member Name="NormalUser1" />
                    <Member Name="NormalUser2" />
                </Members>
            </Group>
        </Groups>
    </Device>
</Application>

“AdminUser1”和“NormalUser1”都来自两个单独的查询。为了简单起见，我们会说查询如下所示：

SELECT UserName 
FROM AdminUsers 
WHERE Computer = 'ComputerName'

SELECT UserName 
FROM NormalUsers 
WHERE Computer = 'ComputerName'

理想情况下，我们会将“ComputerName”作为参数。“AsOf”将是当前时间戳。“AppEnabled”将来自另一个查询，该查询检查该计算机是否设置为或多或少地使用该应用程序。我在想我可以查找它并将值存储在一个变量中。

另一个项目是“reportchanges=true”实际上来自另一个查询，该查询查找要添加到“组名=xxx”项目中的任何项目。所以有一个表包含我将包含在该标题中的设置和值。目前只有一个附加项目（即 ReportChanges=true），但可能有多个项目。该查询看起来像这样：

SELECT XMLSetting, XMLValue 
FROM XMLItems;

我一直在修改查询中的“FOR XML”，它可以正确格式化成员/成员组，但是我将如何添加所有其他层？

标签： sqlsql-serverxml

Answer 1

您的格式将需要相当多的嵌套级别。像这样尝试（下次请提供可消耗格式的示例数据，就像我在这里使用 DDL 和 INSERT 所做的那样）：

DECLARE @AdminUsers TABLE(UserName VARCHAR(100),Computer VARCHAR(100));
DECLARE @NormalUsers TABLE(UserName VARCHAR(100),Computer VARCHAR(100));

INSERT INTO @AdminUsers VALUES('Admin1','blah')
                             ,('Admin2','blah')
                             ,('Admin3','Other');
INSERT INTO @NormalUsers VALUES('user1','blah')
                              ,('user2','blah')
                              ,('user3','Other');

DECLARE @ComputerName VARCHAR(100)='blah';

SELECT 'true' AS [Device/@AppEnabled]
      ,GETDATE() AS [Device/@AsOf]
      ,@ComputerName AS [Device/@MachineName]
      ,(
        SELECT
           (
                SELECT 'Administrators' AS [@Name]
                      ,'true' AS [@ReportChanges]
                      ,(
                        SELECT UserName AS [Member/@name]
                        FROM @AdminUsers 
                        WHERE Computer=@ComputerName 
                        FOR XML PATH(''),ROOT('Members'),TYPE
                       ) AS [*]
                FOR XML PATH('Group'),TYPE
            ) AS [*]
          ,(
                SELECT 'Normal Users' AS [@Name]
                      ,'true' AS [@ReportChanges]
                      ,(
                        SELECT UserName AS [Member/@name]
                        FROM @NormalUsers 
                        WHERE Computer=@ComputerName 
                        FOR XML PATH(''),ROOT('Members'),TYPE
                       ) AS [*]
                FOR XML PATH('Group'),TYPE
            ) AS [*]
        FOR XML PATH(''),TYPE
       ) AS [Device/Groups]
FOR XML PATH('Application'),TYPE;

结果

<Application>
  <Device AppEnabled="true" AsOf="2019-06-04T10:10:50.160" MachineName="blah">
    <Groups>
      <Group Name="Administrators" ReportChanges="true">
        <Members>
          <Member name="Admin1" />
          <Member name="Admin2" />
        </Members>
      </Group>
      <Group Name="Normal Users" ReportChanges="true">
        <Members>
          <Member name="user1" />
          <Member name="user2" />
        </Members>
      </Group>
    </Groups>
  </Device>
</Application>

更新

具有相同结果的另一种方法

WITH Groups AS
(
    SELECT 'Administrators' AS GroupName
    UNION ALL
    SELECT 'Normal Users'
)
SELECT 'true' AS [@AppEnabled]
      ,GETDATE() AS [@AsOf]
      ,@ComputerName AS [@MachineName]
      ,(
        SELECT GroupName AS [@name]
              ,'true' AS [@ReportChanges]
              ,(
                SELECT names.*
                FROM
                (
                    SELECT UserName AS [@Name] FROM @AdminUsers WHERE GroupName='Administrators' AND Computer=@ComputerName 
                    UNION ALL
                    SELECT UserName AS [@Name] FROM @NormalUsers WHERE GroupName='Normal Users' AND Computer=@ComputerName
                ) names
                FOR XML PATH('Member'),TYPE
               ) AS Members
        FROM Groups
        FOR XML PATH('Group'),TYPE
       ) AS Groups
FOR XML PATH('Device'),ROOT('Application');