SQL Server | Convert tables in T-SQL into XML
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 :
Please Login to comment...