Open In App

SQL Server | Convert tables in T-SQL into XML

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In this, we will focus on how tables will be converted in T-SQL into XML in SQL server. And you will be able to understand how you can convert it with the help of command. Let’s discuss it one by one.

Overview :
XML (Extensible Markup Language) is a markup language similar to HTML which was designed to share information between different platforms. And here, you will understand how you can Convert tables in T-SQL into XML.

Example –
Here is a below sample XML Document as follows.

<email>
 <to>Manager</to>
 <from>Sruti</from>
 <heading>Work Status</heading>
 <body>Work Completed</body>
</email>

Converting tables in T-SQL into XML :
To Convert tables in T-SQL into XML by using the following steps as follows. Let’s first generate an Employee_Table to store data of few Employees in order to create XML documents. 

Creating table – Employee_Table 

CREATE TABLE Employee_Table  
(  
EmpId int identity(1,1) primary key,  
Name varchar(100),  
Salary int ,  
City varchar(20)  
)  

Inserting data into Employee_Table –

insert into Employee_Table ( Name,City,Salary)
VALUES
('Sruti','Dhanbad',20000),
('Raj','Kerala',25000),
('Rajsekar','Jaipur',50000),
('Prafull','Kochi',250000),
('Tripti','Kolkata',10000),
('Aditya','Mumbai',5000),
('Kiran','Indore',21000)

Reading data to verify –

SELECT * FROM Employee_Table

Output :

Methods of converting tables in T-SQL into XML :
There are two common ways to convert data from SQL tables into XML format as follows.

With FOR XML AUTO –
The FOR XML AUTO class creates an XML document where each column is an attribute.

SELECT * FROM Employee_Table
FOR XML AUTO

Output :

This query will create a hyperlink as an output. On clicking the link, we will see the following document in a new query window of SSMS as follows.

With FOR XML PATH clauses –
FOR XML PATH will create an XML document where each row is embedded into <row> and </row> clause. In each row, each column value is embedded into <ColumnName> and </ColumnName> clauses.

SELECT * FROM Car
FOR XML PATH

Output :


Last Updated : 09 Mar, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads