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:

  <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>

Here is an example :

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()"
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