Export Table Data in to XML in SQL

Introduction

Sometimes it is required fetch table data into file or in application for transferring data over network or for some application.This blog describes and demonstrates,how to export SQL table data into XML form using SQL queries.

Getting Started

SQL Server provides some T-SQL command which supports to export data in XML format. It provides four most used methods(AUTO, RAW, PATH & EXPLICIT) to export table content into XML format. All the method are used with FOR XML clause as postfix.

Syntax:

 SELECT 'Columns' FROM 'Table_Name' FORM XML 'Method_Name'  

Before going to discuss the export methods of SQL,will introduce a table which i have already created for taking as example while discussing about methods.

I have created a table having name'StudentDetails' and add some columns using below queries and also have inserted some data into the table to took as example.

Codes:

 CREATE TABLE StudentDetails  
 (  
   StudentID INT,  
   StudentName NVARCHAR(100),  
   StudentClass NVARCHAR(10)  
 )  
 INSERT INTO StudentDetails VALUES(1,'Kailash1','5TH')  
 INSERT INTO StudentDetails VALUES(2,'Kailash2','5TH')  
 INSERT INTO StudentDetails VALUES(3,'Kailash3','5TH')  
 INSERT INTO StudentDetails VALUES(4,'Kailash3','5TH')  
 INSERT INTO StudentDetails VALUES(5,'Kailash5','5TH')  

Export Methods:

1. RAW
   The RAW method exports each rows of table as a XML element and each column of that rows as attributes of element and the element name by default is <row>.
   Syntax:
   

    SELECT 'Columns' FROM 'Table_Name' FORM XML RAW  
   

   Example:
   

    SELECT * FROM StudentDetails FOR XML RAW  
   

   Result:
   

    <row StudentID="1" StudentName="Kailash1" StudentClass="5TH"/>  
    <row StudentID="2" StudentName="Kailash2" StudentClass="5TH"/>  
    <row StudentID="3" StudentName="Kailash3" StudentClass="5TH"/>  
    <row StudentID="4" StudentName="Kailash3" StudentClass="5TH"/>  
    <row StudentID="5" StudentName="Kailash5" StudentClass="5TH"/>  
   

2. AUTO
   The AUTO mode method work same as RAW method, but it names the element name same as the table name. for example here we have created a table named 'StudentDetails', hence the element name will be <StudentDetails>.
   Syntax:
   

    SELECT 'Columns' FROM 'Table_Name' FORM XML AUTO  
   

   Example:
   

    SELECT * FROM StudentDetails  FOR XML AUTO  
   

   Result:
   

    <StudentDetails StudentID="1" StudentName="Kailash1" StudentClass="5TH"/>  
    <StudentDetails StudentID="2" StudentName="Kailash2" StudentClass="5TH"/>  
    <StudentDetails StudentID="3" StudentName="Kailash3" StudentClass="5TH"/>  
    <StudentDetails StudentID="4" StudentName="Kailash3" StudentClass="5TH"/>  
    <StudentDetails StudentID="5" StudentName="Kailash5" StudentClass="5TH"/>  
   

3. PATH
   The path mode is little difference from AUTO & RAW method, it generates nested elements each columns of row even you can add root element for a all row. Means It generates an element for a row and child element for row columns.
   Syntax:
   

    SELECT 'Columns' FROM 'Table_Name' FORM XML PATH  
   

   Example:-1
   Using PATH method without any parameter
   

    SELECT * FROM StudentDetails  FOR XML PATH  
   

   Result:
   

    <row><StudentID>1</StudentID><StudentName>Kailash1</StudentName><StudentClass>5TH</StudentClass></row>  
    <row><StudentID>2</StudentID><StudentName>Kailash2</StudentName><StudentClass>5TH</StudentClass></row>  
    <row><StudentID>3</StudentID><StudentName>Kailash3</StudentName><StudentClass>5TH</StudentClass></row>  
    <row><StudentID>4</StudentID><StudentName>Kailash3</StudentName><StudentClass>5TH</StudentClass></row>  
    <row><StudentID>5</StudentID><StudentName>Kailash5</StudentName><StudentClass>5TH</StudentClass></row>  
   

   Example:-2
   By default the path method gives element name generated for a row as <row>, but can be changed by providing name as parameter.
   

    SELECT * FROM StudentDetails  FOR XML PATH('StudentDetails')  
   

   Result:
   

    <StudentDetails><StudentID>1</StudentID><StudentName>Kailash1</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>2</StudentID><StudentName>Kailash2</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>3</StudentID><StudentName>Kailash3</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>4</StudentID><StudentName>Kailash3</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>5</StudentID><StudentName>Kailash5</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
   

   Example:-3
   Adding Top-level element
   

    SELECT * FROM StudentDetails  FOR XML PATH('StudentDetails'), root ('Root')  
   

   Result:
   

    <StudentDetails><StudentID>1</StudentID><StudentName>Kailash1</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>2</StudentID><StudentName>Kailash2</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>3</StudentID><StudentName>Kailash3</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>4</StudentID><StudentName>Kailash3</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
    <StudentDetails><StudentID>5</StudentID><StudentName>Kailash5</StudentName><StudentClass>5TH</StudentClass></StudentDetails>  
   

4. EXPLICIT
   The EXPLICIT method provides more control to export table data into XML format. The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.
   
   This is very vast concept you can refer this link for the same.
   
   

Thanks
Kailash Chandra Behera