SQL Server | Convert Tables in T-SQL into XML

Last Updated : 30 Sep, 2024

XML (Extensible Markup Language) is a widely-used markup language designed to store and transfer structured data between different systems and platforms. While HTML focuses on the visual representation of data

Overview

XML is similar to HTML which is designed to structure and store data for sharing across different systems and platforms.

Unlike HTML, XML focuses on the content of the data rather than its display.
In SQL Server, we can easily convert tables into XML format to facilitate data transfer and integration between applications.

Let's go through an example of how to achieve this using SQL Server.

Example of XML Document

Here is a simple XML document structure:

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

Converting Tables in T-SQL to XML

To convert tables from T-SQL to XML in SQL Server, follow these steps. We will first create a table named Employee_Table to store employee data, and then use SQL queries to generate XML documents from it.

Step 1: Creating the `Employee_Table`

To create a table to store employee details:

CREATE TABLE Employee_Table  
(  
    EmpId INT IDENTITY(1,1) PRIMARY KEY,  
    Name VARCHAR(100),  
    Salary INT,  
    City VARCHAR(20)  
);

Step 2: Inserting Data into `Employee_Table`

Once the table is created, insert employee data into it:

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);

Step 3: Verifying the Data

Use the following query to verify the data entered in the table:

SELECT * FROM Employee_Table;

Output:

Methods of Converting Tables in T-SQL to XML

There are two common ways to convert SQL Server tables to XML format:

1. Using `FOR XML AUTO`

The FOR XML AUTO clause generates an XML document where each column from the SQL table is represented as an attribute within an element.

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.

2. Using `FOR XML PATH`

The FOR XML PATH clause creates an XML document where each row is enclosed in <row> tags, and each column is embedded in its own XML tag.

SELECT * FROM Employee_Table
FOR XML PATH;

Output:

Conclusion

Converting T-SQL tables into XML format in SQL Server is a valuable technique for sharing structured data across different systems. By using SQL Server’s FOR XML AUTO and FOR XML PATH clauses, you can easily transform your table data into a versatile XML document.

SQL Server TRY CONVERT() Function

Sruti Rai

Improve

Article Tags :

SQL Server | Convert Tables in T-SQL into XML

Overview

Example of XML Document

Converting Tables in T-SQL to XML

Step 1: Creating the Employee_Table

Step 2: Inserting Data into Employee_Table

Step 3: Verifying the Data

Methods of Converting Tables in T-SQL to XML

1. Using FOR XML AUTO

2. Using FOR XML PATH

Conclusion

Similar Reads

Thank You!

What kind of Experience do you want to share?

Step 1: Creating the `Employee_Table`

Step 2: Inserting Data into `Employee_Table`

1. Using `FOR XML AUTO`

2. Using `FOR XML PATH`