CODEWITHSUNDEEP
sql-serverxml
Abhijeet Khandagale
Abhijeet Khandagale

Reputation: 89

Converting multiple rows to single XML row. - SQL Server

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

Upvotes: 0

Views: 2433

Answers (1)

gofr1
gofr1

Reputation: 15977

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>

Upvotes: 3

Related Questions