CODEWITHSUNDEEP
sqlsql-serverxml
Conrad Jagger
Conrad Jagger

Reputation: 153

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

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

Upvotes: 0

Views: 2120

Answers (1)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

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

Upvotes: 1

Related Questions