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.
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.
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
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
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