SQL Server: Nesting Elements with FOR XML PATH

Question

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>

See new answer to your question. – Alex Kudryashev Jun 10 '16 at 02:09 — Alex Kudryashev, Jun 10 '16 at 02:09

Brian Pressler · Accepted Answer · 2016-06-09T19:50:49.687

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.

You can add the root node by including it's name in the FOR XML PATH statement. I added it to the answer. — Brian Pressler, Jun 09 '16 at 19:45
String concatenation. See: http://stackoverflow.com/questions/9002403/how-to-add-xml-encoding-xml-version-1-0-encoding-utf-8-to-xml-output-in — Brian Pressler, Jun 09 '16 at 20:29

score 1 · Answer 2 · answered Jun 10 '16 at 02:06

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.

insert @emp values ('Conrad','1976-10-14',10000,'Washington DC','DC','989898989'), ('Alex','1966-10-14',15000,'New York','NY','989898988') — Conrad Jagger, Jun 10 '16 at 05:59
if i can get 2 records for CONRAD ... in second he changed his address.... now under additionalinfo... i want to display both the address how to make this possible. And thanks for excellent suggestions... on how to have more control over XML — Conrad Jagger, Jun 10 '16 at 06:01

SQL Server: Nesting Elements with FOR XML PATH

2 Answers2