T-SQL query FOR XML output, how to correctly format XML query to specification

Question

Hi and thank you for your time in advance. I am fairly new to formating XML output from a SQL query, so here it goes :) I need to format my FOR XML SQL query (in SSMS 2012) to match this format:

<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>

So in words, a ClientInfo header with info specific to the company, and then an EmployeeHRData section that repeats for each employee, all wrapped in a HRData tag.

So far, I have the following output(Client info repeats, instead of appearing only once, and have a FinalOutputTable tag that doesn't belong):

<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>

Which is generated from:

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

Aladin Hdabe · Accepted Answer · 2015-11-29 04:49:30Z

1

To have a nested ClientId element inside the ClientInfo element you use

Select 11 as "ClientInfo/ClientId"

So you can use the forward slash to define the location for the value in the xml result.

EDIT: you can use the code below if you want to return a document for one client.

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

edited Nov 29, 2015 at 4:49

answered Nov 28, 2015 at 21:43

Aladin Hdabe

8756 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff.Clark Over a year ago

I am confused. The ClientID is currently nested inside the Client Info element. Currently the ClientInfo tag is repeated for each employee, and I need the ClientInfo element to only appear once at the very top.

Aladin Hdabe Over a year ago

I got what your are asking for now. I have updated the answer.

Collectives™ on Stack Overflow

T-SQL query FOR XML output, how to correctly format XML query to specification

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related