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