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

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    

No comments:

Post a Comment