Export Table Data to XML in SQL

Export to XML in SQL Server is a common task for data integration, backup, and sharing between systems. XML (eXtensible Markup Language) provides a structured, platform-independent way to represent data. SQL Server and some other RDBMS platforms offer native support to export data as XML.

In this post, we’ll walk through how to export table data to XML using SQL Server's built-in functionalities. We’ll also touch briefly on approaches in MySQL and Oracle.

Export Table Data to XML in SQL

Getting Started

SQL Server provides several T-SQL commands that support exporting data in XML format. It offers four commonly 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, I will introduce a table that I have already created for taking as an example while discussing methods.

I have created a table having name'StudentDetails' and add some columns using the below queries and also have inserted some data into the table to took as an 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:

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  
 StudentID,  
 StudentName,  
 StudentClass  
 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"/>  

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  
 StudentID,  
 StudentName,  
 StudentClass  
 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"/>  

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  
 StudentID,  
 StudentName,  
 StudentClass  
 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  
 StudentID,  
 StudentName,  
 StudentClass  
 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  
 StudentID,  
 StudentName,  
 StudentClass  
 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>  

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.

Export to XML in MySQL

MySQL does not have native FOR XML support. You must manually construct XML using CONCAT():

 SELECT CONCAT(  
   '<StudentDetails >',  
   '<ID>', StudentID, '</ID>',  
   '<StudentName>', StudentName, '</StudentName>',  
   '<Class>', StudentClass, '</Class>',  
   '</StudentDetails >'  
 ) AS XMLData  
 FROM StudentDetails;  

Export to XML in Oracle

Oracle provides DBMS_XMLGEN and XMLTYPE to export XML.

 XMLTYPE(  
           CURSOR(  
             SELECT   
             StudentID,   
             StudentName,   
             StudentClass   
          )  
        ).getClobVal() AS xml_output FROM StudentDetails;  

Thanks