Generate XML - Column Names with their Values as text() enclosed within their Column Name Tag

  • Article

The most commonly used XML format is the following: (column names with their values as text() enclosed within their column name tag).

Let's find out how to generate the following XML for table provided below:

<Employees> 
  <field Name="ID">1</field> 
  <field Name="Name">Sathya</field> 
  <field Name="Age">25</field> 
  <field Name="Sex">Male</field> 
  <field Name="ID">2</field> 
  <field Name="Name">Madhu K Nair</field> 
  <field Name="Age">30</field> 
  <field Name="Sex">Male</field> 
  <field Name="ID">3</field> 
  <field Name="Name">Vidhyasagar</field> 
  <field Name="Age">28</field> 
  <field Name="Sex">Male</field> 
</Employees>

Here is an example :

DECLARE @Employee TABLE
(ID INT, 
Name VARCHAR(100), 
Age INT, 
Sex VARCHAR(50)) 
  
INSERT @Employee SELECT 1,'Sathya',25,'Male'
INSERT @Employee SELECT 2,'Madhu K Nair',30,'Male'
INSERT @Employee SELECT 3,'Vidhyasagar',28,'Male'
  
SELECT * FROM @Employee 
  
DECLARE @xmldata XML 
  
SET @xmldata = (SELECT ID,Name,Age,Sex FROM  @Employee FOR  XML PATH ('')) 
  
SET @xmldata = ( 
SELECT ColumnName AS "@Name", 
       ColumnValue AS  "text()"
FROM( 
SELECT i.value('local-name(.)','varchar(100)') ColumnName, 
       i.value('.','varchar(100)') ColumnValue 
FROM @xmldata.nodes('//*[text()]') x(i)) tmp 
FOR XML PATH ('field'),root('Employees')) 
  
SELECT @xmldata

See Also