Converting multiple rows to single XML row. - SQL Server

Question

I have one table in following format.

enter image description here

I want data in XML format per Employee using SQL query as below

<Employee>
  <EmployeeID>1</EmployeeID>
  <FirstName>Jon</FirstName>
  <LastName>Snow</LastName>
  <City>North of the wall</City>
  <Age>28</Age>
  <Gender>Male</Male>
</Employee>

<Employee>
  <EmployeeID>2</EmployeeID>
  <FirstName>Arya</FirstName>
  <LastName>Stark</LastName>
  <City>Winterfell</City>
  <Age>14</Age>
  <Gender>Female</Male>
</Employee>

I have tried using query,

SELECT  DISTINCT EmployeeID ,(  
    SELECT   
    EmployeeID,  
    (SELECT    PDFKey ,   
        Replace(Value,char(CAST(0x001A as int)),'') as Value  FROM TempData EE WHERE  E.EmployeeID =EE.EmployeeID FOR XML PATH(''),   
    TYPE, ELEMENTS)   
    AS Employee  
    FROM   
    TempData E WHERE o.EmployeeID =E.EmployeeID  
    GROUP BY EmployeeID  
    FOR XML AUTO, ELEMENTS  
    ) AS XMLtag FROM TempData O

But it gives data in format like,

 <E>
 <EmployeeID>1</EmployeeID>
 <Employee>
    <PDFKey>FirstName</PDFKey>
    <Value>Jon</Value>
    <PDFKey>LastName</PDFKey>
    <Value>Snow</Value>
    <PDFKey>City</PDFKey>
    <Value>North of the wall</Value>
    <PDFKey>Age</PDFKey>
    <Value>28</Value>
    <PDFKey>Gender</PDFKey>
    <Value>Male</Value>
  </Employee>
  </E>

And I want one row per employee which will have 2 columns, EmployeeID and XMLdata.

Please refer image below for expected output, enter image description here

Be more specific with your question. What have you tried so far? — Jerry
– Jerry, Commented Oct 20, 2016 at 11:32

gofr1 · Accepted Answer · 2016-10-20 12:34:57Z

3

You can use PIVOT + FOR XML PATH:

SELECT  *
FROM Employee
PIVOT (
    MAX([Value]) FOR PDFKey IN ([FirstName],[LastName],[City],[Age],[Gender])
) as u
FOR XML PATH('Employee'), TYPE

Output:

<Employee>
  <EmployeeID>1</EmployeeID>
  <FirstName>Jon</FirstName>
  <LastName>Snow</LastName>
  <City>North of the wall</City>
  <Age>28</Age>
  <Gender>Male</Gender>
</Employee>
<Employee>
  <EmployeeID>2</EmployeeID>
  <FirstName>Arya</FirstName>
  <LastName>Stark</LastName>
  <City>Winterfell</City>
  <Age>14</Age>
  <Gender>Female</Gender>
</Employee>

After pivoting you will get table like this:

EmployeeID  FirstName   LastName    City                Age Gender
1           Jon         Snow        North of the wall   28  Male
2           Arya        Stark       Winterfell          14  Female

After that you add FOR XML with PATH mode

In PATH mode, column names or column aliases are treated as XPath expressions. These expressions indicate how the values are being mapped to XML. Each XPath expression is a relative XPath that provides the item type., such as the attribute, element, and scalar value, and the name and hierarchy of the node that will be generated relative to the row element.

Also I have added TYPE

SQL Server support for the xml (Transact-SQL) enables you to optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive.

EDIT

;WITH cte AS (
SELECT  *
FROM Employee
PIVOT (
    MAX([Value]) FOR PDFKey IN ([FirstName],[LastName],[City],[Age],[Gender])
) as u
)

SELECT  e.EmployeeID,
        (SELECT *
        FROM cte
        WHERE EmployeeID = e.EmployeeID
        FOR XML PATH('Employee'), TYPE) as XMLData
FROM cte e

Output:

EmployeeID  XMLData
1           <Employee><EmployeeID>1</EmployeeID><FirstName>Jon</FirstName><LastName>Snow</LastName><City>North of the wall</City><Age>28</Age><Gender>Male</Gender></Employee>
2           <Employee><EmployeeID>2</EmployeeID><FirstName>Arya</FirstName><LastName>Stark</LastName><City>Winterfell</City><Age>14</Age><Gender>Female</Gender></Employee>

edited Oct 20, 2016 at 12:34

answered Oct 20, 2016 at 11:38

gofr1

16k11 gold badges47 silver badges56 bronze badges

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

7 Comments

Abhijeet Khandagale Over a year ago

This query works perfectly, but I need one row per employee which will have 2 columns, EmployeeID and XMLdata.

Gottfried Lesigang Over a year ago

@gofr1, Great answer, +1 from my side... My magic glass bulb tells me, that OP actually wants the output as EmployeeID as INT column and the rest (possibly containing the ID) as XML within a second column. Easily achieved with a sub-select...

Abhijeet Khandagale Over a year ago

Please refer "i.sstatic.net/Z8syi.jpg", I want output in this format.

Abhijeet Khandagale Over a year ago

@Shnugo Thanks, I was unable to explain my actual query.

gofr1 Over a year ago

@AbhijeetKhandagale I changed answer, hope, this will help you.

|

Collectives™ on Stack Overflow

Converting multiple rows to single XML row. - SQL Server

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related