SQL Server - For XML Path (nested elements, multiple records)

Question

Struggling to write query which can generate nested elements in XML. Can anyone help

INPUT:

SELECT EmpName AS [Name],
       EmpSalary        AS [WageAmount],
    Add1    AS [Address1],
    Add2    AS [Address2],
    Mobile  AS [Mobile]
FROM   Employee
FOR XML PATH

TABLE DATA (2 records exist for emoployee with different addresses)

Conrad Jagger   12345    London   UK    9191919191
Conrad Jagger   12345    Coventry UK    9191919191

Expected OUTPUT: (this has additional info as a nested element)

    <Employee>
    <Name>Conrad</Name>
    <WageAmount>10000</WageAmount>
    <AdditionalInfo>
         <PersonAddress>
                  <Address1>London</Address1>
                  <Address2>UK</Address2>
                 <Mobile>919191919191</Mobile>
        </PersonAddress>
        <PersonAddress>
                  <Address1>Coventry</Address1>  --->Second address
                  <Address2>UK</Address2>
                <Mobile>919191919191</Mobile>
       </PersonAddress>
    </AdditionalInfo>
    </Employee>

This query works if you have single record coming, i don't know how to make this work for multiple records which im getting

SELECT EmpName AS [Name],
       EmpSalary AS [WageAmount],
       Add1 AS [AdditionalInfo/Address1],
       Add2 AS [AdditionalInfo/Address2],
       Mobile AS [AdditionalInfo/Mobile]
FROM   Employee
FOR XML PATH ('Employee')

Sateesh Pagolu · Accepted Answer · 2016-06-10 06:03:56Z

1

I hope there is an uniqe id column in the table. As you haven't provided that column, i am joining on EmpName column for instance.

Try this

SELECT EmpName AS [Name],
       EmpSalary AS [WageAmount],
       (SELECT 
       Add1 AS [PersonAddress/Address1],
       Add2 AS [PersonAddress/Address2],
       Mobile AS [PersonAddress/Mobile] FROM Employee B WHERE EmpName = A.EmpName
       FOR XML PATH('AdditionalInfo'),type)
FROM   Employee A
GROUP BY EmpName,EmpSalary
FOR XML PATH ('Employee')

edited Jun 10, 2016 at 6:03

answered Jun 10, 2016 at 5:57

Sateesh Pagolu

9,6563 gold badges32 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

SQL Server - For XML Path (nested elements, multiple records)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related