CODEWITHSUNDEEP
sqlsql-serverxml
Conrad Jagger
Conrad Jagger

Reputation: 153

SQL Server: Nesting Elements with FOR XML PATH

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

INPUT:

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

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

<Employee>
<Name>Conrad</Name>
<Birthdate>14-oct-76</BirthDate>
<WageAmount>10000</WageAmount>
<AdditionalInfo>
    <Address1>Washington DC</Address1>
    <Address2>DC</Address2>
    <Mobile>989898989</Mobile>
</AdditionalInfo>
</Employee>

Upvotes: 2

Views: 1985

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9480

For this task simple path mode is not enough and we need more elaborate explicit mode. It is rather tricky but gives you maximum flexibility in xml formatting. Detail description is available on MSDN site. Here is a query for current question.

declare @emp table(--test table
        EmpId int not null identity(1,1),
        EmpName varchar(50),
        EmpDOB datetime,
        EmpSalary money,
        Add1 varchar(50),
        Add2 varchar(50),
        Mobile varchar(20)
        )
--Add some data
insert @emp values
('Conrad','1976-10-14',10000,'Washington DC','DC','989898989'),
('Alex','1966-10-14',15000,'New York','NY','989898988')
-- prepare query 
SELECT 1    Tag, --mandatory field and level 
       0 Parent, --2nd field must be Parent 
       null [Employees!1!EmpID],  --get from 1st level
       NULL [Employee!2!Name!element],  --from 2nd level
       NULL [Employee!2!Birthdate!element],
       null [Employee!2!WageAmount!element],   
       null [AdditionalInfo!3!Address1!element],--from 3rd level
       null [AdditionalInfo!3!Address2!element],
       null [AdditionalInfo!3!Mobile!element]
FROM   @emp
union
SELECT 2    Tag,  --2nd level
       1    Parent, --refer to 1st level 
       EmpId [Employees!1!EmpID],  --all we need on this level
       EmpName [Employee!2!Name!element],  
       EmpDOB [Employee!2!Birthdate!element],
       EmpSalary [Employee!2!WageAmount!element],   
       null [AdditionalInfo!3!Address1!element]  ,
       null [AdditionalInfo!3!Address2!element]  ,
       null [AdditionalInfo!3!MObile!element]
FROM   @emp
union
SELECT 3    Tag,  --3rd level
       2 Parent,  --include into 2nd level
       EmpId [Employees!1],  
       NULL [Employee!2!Name!element],  
       NULL [Employee!2!Birthdate!element],
       null [Employee!2!WageAmount!element],   
       Add1 [AdditionalInfo!3!Address1!element]  ,
       Add2 [AdditionalInfo!3!Address2!element]  ,
       Mobile [AdditionalInfo!3!MObile!element]
FROM   @emp
order by [Employees!1!EmpID]
for xml explicit --This is explicit mode

And test result:

<Employees>
  <Employee>
    <Name>Conrad</Name>
    <Birthdate>1976-10-14T00:00:00</Birthdate>
    <WageAmount>10000.0000</WageAmount>
    <AdditionalInfo>
      <Address1>Washington DC</Address1>
      <Address2>DC</Address2>
      <Mobile>989898989</Mobile>
    </AdditionalInfo>
  </Employee>
  <Employee>
    <Name>Alex</Name>
    <Birthdate>1966-10-14T00:00:00</Birthdate>
    <WageAmount>15000.0000</WageAmount>
    <AdditionalInfo>
      <Address1>New York</Address1>
      <Address2>NY</Address2>
      <Mobile>989898988</Mobile>
    </AdditionalInfo>
  </Employee>
</Employees>

This is what we need.

Upvotes: 1

Brian Pressler
Brian Pressler

Reputation: 6713

The easy way in this case is to just specify the path in your column alias with a forward slash delimiter:

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

You can also do it with sub-queries that return XML in the select columns. Something like this:

SELECT EmpName AS [Name],
       EmpDOB AS [Birthdate],
       EmpSalary AS [WageAmount],
       (select
           Add1 AS [AdditionalInfo/Address1],
           Add2 AS [AdditionalInfo/Address2],
           Mobile AS [AdditionalInfo/Mobile]
        from Employee EmpAddInfo
        where EmpAddInfo.EmployeeID=Employee.EmployeeID
        FOR XML PATH('AdditionalInfo'), TYPE 
        )
FROM Employee
FOR XML PATH ('Employee')

It's silly to do it this way in your case because the fields are all in the same table, but you would do something like this way if you had a detail table to join to.

Upvotes: 5

Related Questions