T-SQL查询FOR XML输出，如何根据规范正确格式化XML查询

Question

嗨，感谢您提前抽出时间。我对格式化SQL查询的XML输出相当陌生，所以它是这样的：）我需要格式化我的FOR XML SQL查询（在SSMS 2012中）以匹配以下格式：

<HR_Data>
    <ClientInfo>
      <OrganizationOID>XXXX</OrganizationOID>
      <ClientId>XXXX</ClientId>
    </ClientInfo>
    <EmployeeHRData>
      <Employee_ID>XXXX</Employee_ID>
      <SocialSecurityNumber>XXXX</SocialSecurityNumber>
      ....
    </EmployeeHRData>
    <EmployeeHRData>
      <Employee_ID>XXXX</Employee_ID>
      <SocialSecurityNumber>XXXX</SocialSecurityNumber>
      ....
    </EmployeeHRData>
</HRData>

因此，换句话说，一个包含公司特定信息的ClientInfo标头，然后是一个为每个员工重复的EmployeeHRData部分，所有这些都封装在一个HRData标记中。

到目前为止，我有以下输出（客户端信息重复，而不是只出现一次，并且有一个不属于的FinalOutputTable标签）：

<HR_Data>
  <FinalOutputTable>
    <ClientInfo>
      <OrganizationOID>XXXX</OrganizationOID>
      <ClientId>XXXX</ClientId>
    </ClientInfo>
    <EmployeeHRData>
      <Employee_ID>XXXX</Employee_ID>
      <SocialSecurityNumber>XXXX</SocialSecurityNumber>
      ....
    </EmployeeHRData>
  </FinalOutputTable>
  <FinalOutputTable>
    <ClientInfo>
      <OrganizationOID>XXXX</OrganizationOID>
      <ClientId>XXXX</ClientId>
    </ClientInfo>
    <EmployeeHRData>
      <OrganizationOID>XXXX</OrganizationOID>
      <ClientId>XXXX</ClientId>
      ....
    </EmployeeHRData>
  </FinalOutputTable>
</HRData>

生成自：

SELECT (SELECT 'XXXX' AS 'OrganizationOID', 'XXXX' AS 'ClientId'
        FOR XML PATH (''), TYPE) ClientInfo
        ,(SELECT [AOID] AS AssociateOID
                ,ISNULL([Employee Identifier], '') AS Employee_ID
                ,ISNULL(SSN, '') AS SocialSecurityNumber
                ,.........
        FOR XML PATH (''), TYPE) EmployeeHRData            

INTO #FinalOutputTable
FROM XXXX
WHERE XXXX
ORDER BY XXXX
SELECT * FROM #FinalOutputTable AS FinalOutputTable
FOR XML AUTO, ROOT('HR_Data'), ELEMENTS

Answer 1

要在ClientInfo元素中嵌套ClientId元素，请使用

Select 11 as "ClientInfo/ClientId"

因此，您可以使用正斜杠来定义xml结果中值的位置。

编辑：如果您想为一个客户端返回文档，可以使用下面的代码。

Declare @HR_Data xml
Set @HR_Data=
(Select HR_Data.ClientInfo
 from
(
Select  (
     SELECT 'XXXX' AS 'OrganizationOID', 'XXXX' AS 'ClientId'
     FOR XML PATH (''), TYPE
    ) ClientInfo
) HR_Data 
FOR XML PATH(''), ROOT('HR_Data'))

Declare @EmployeeHRData xml
Set @EmployeeHRData=
(SELECT   
      [AOID] AS AssociateOID
      ,ISNULL([Employee Identifier], '') AS Employee_ID
      ,ISNULL(SSN, '') AS SocialSecurityNumber
       ,.........
       from table_name
   FOR XML PATH('EmployeeHRData'), TYPE
   )

SET @HR_Data.modify(
    'insert sql:variable("@EmployeeHRData")
    as last into (/HR_Data[1])')
Select @HR_Data