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


3 comments:

  1. You've provided quite good information here. This is fantastic since it expands our knowledge and is also beneficial to us. Thank you for sharing this piece of writing. shipment data

    ReplyDelete
  2. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you Best craigslist outboard motors for sale by owner service provider.

    ReplyDelete
  3. I truly need to thank the creator for a particularly decent blog that assisted me with understanding why it is significant. Russia Export Data

    ReplyDelete