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