Creating SQL Table Using XML Attributes

Hello Friends, In this article, we will see how we can work with XML using SQL Server. We will see how to convert tables in SQL into XML, how to load XML documents using SQL Server and how to create SQL tables from XML documents.

XML Stand for (Extensible Markup Language). It is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has come thede-facto standard for data sharing. In addition, XML is fluently extendable.

Let’s first generate some dummy data. We will use this data to create XML documents. Execute the following script:

CREATE DATABASE Employee
 
USE Employee
CREATE TABLE EmployeeDetails  
(  
  EmployeeId INT IDENTITY(1,1) PRIMARY KEY,  
  EmployeeName VARCHAR(100),  
  EmployeeDesignation VARCHAR(100),  
  EmployeeSalary DECIMAL(18,2),
  EmployeeDepartment VARCHAR(500)  
)  

INSERT INTO EmployeeDetails( EmployeeName, EmployeeDesignation,  EmployeeSalary ,EmployeeDepartment)
VALUES ('Vishal','Sr.Net Developer',40000, 'IT'),
('Rinal','HR',18000, 'IT'),
('Pankaj','Manager',60000, 'IT'),
('Jinal','CA',30000, 'IT'),
('Rakesh','Designer',25000, 'IT'),
('Ankush','QA',25000, 'IT')

In the script above, we created a Employee database with one table EmployeeDetails . The EmployeeDetails table has five attributes: EmployeeId, EmployeeName, EmployeeDesignation, EmployeeSalary, EmployeeDepartment. Next, we added 6 dummy records in the EmployeeDetails table.

Converting into XML from SQL tables

The simplest way to convert data from SQL table into XML format is to use the FOR XML AUTO and FOR XML PATH clauses.

FOR XML AUTO in SQL SERVER

The FOR XML AUTO clause converts each column in the SQL table into an attribute in the corresponding XML document.

Execute the following script:

SELECT * FROM EmployeeDetails
FOR XML AUTO

In the console output you will see the following:

Click on the link and you will see the following document in a new query window of SQL Server management studio:

You can see that for each records an element EmployeeDetails has been created in the XML document, and for each column, an attributes with the same name has been added to each element in the XML document.

FOR XML PATH in SQL SERVER

The FOR XML AUTO class create an XML document where each column is an attributes. On the other hand, the FOR XML PATH will create an XML document where each records is an element and each column is a nested element for a particular record. Let’s see this in action:

SELECT * FROM EmployeeDetails
FOR XML Path

A snapshot of the output is as follows:

In the output, you will see a total of 6 elements . You can see that each column name has been converted to an elements. However, there is one problem; by default, the parent elements name is “row”. We can change that using the following query:

SELECT * FROM EmployeeDetails
FOR XML PATH ('Employees')

In the output, you can see Employees as the parent element for each sub-element. However, the document is not well-formed as there is no root elements in the document. To add a root elements, we need to execute the following scripts:

SELECT * FROM EmployeeDetails
FOR XML PATH ('Employees'), ROOT('EmployeeDetails')

In the output, you should see “EmployeeDetails” as the root element as shown below:

Creating a SQL table using XML elements

To create a SQL table using XML element, all you have to do is to change the mode value of the OPENXML function to 2 and change the name of the attribute to the name of the elements you want to retrieve.

we can use the following script:

DECLARE @xmlData XML;  
SET @xmlData = '<root>
  <EmployeeDetails>
  <EmployeeId>1</EmployeeId>
  <EmployeeName>Vishal</EmployeeName>
  <EmployeeDesignation>Sr.Net Developer</EmployeeDesignation>
  <EmployeeSalary>40000.00</EmployeeSalary>
  <EmployeeDepartment>IT</EmployeeDepartment>
</EmployeeDetails>
<EmployeeDetails>
  <EmployeeId>2</EmployeeId>
  <EmployeeName>Rinal</EmployeeName>
  <EmployeeDesignation>HR</EmployeeDesignation>
  <EmployeeSalary>18000.00</EmployeeSalary>
  <EmployeeDepartment>IT</EmployeeDepartment>
</EmployeeDetails>
<EmployeeDetails>
  <EmployeeId>3</EmployeeId>
  <EmployeeName>Pankaj</EmployeeName>
  <EmployeeDesignation>Manager</EmployeeDesignation>
  <EmployeeSalary>60000.00</EmployeeSalary>
  <EmployeeDepartment>IT</EmployeeDepartment>
</EmployeeDetails>
<EmployeeDetails>
  <EmployeeId>4</EmployeeId>
  <EmployeeName>Jinal</EmployeeName>
  <EmployeeDesignation>CA</EmployeeDesignation>
  <EmployeeSalary>30000.00</EmployeeSalary>
  <EmployeeDepartment>IT</EmployeeDepartment>
</EmployeeDetails>
<EmployeeDetails>
  <EmployeeId>5</EmployeeId>
  <EmployeeName>Rakesh</EmployeeName>
  <EmployeeDesignation>Designer</EmployeeDesignation>
  <EmployeeSalary>25000.00</EmployeeSalary>
  <EmployeeDepartment>IT</EmployeeDepartment>
</EmployeeDetails>
<EmployeeDetails>
  <EmployeeId>6</EmployeeId>
  <EmployeeName>Ankush</EmployeeName>
  <EmployeeDesignation>QA</EmployeeDesignation>
  <EmployeeSalary>25000.00</EmployeeSalary>
  <EmployeeDepartment>IT</EmployeeDepartment>
</EmployeeDetails>
</root>'

 DECLARE @idoc INT;
   EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

   select @xmlData

   SELECT * 
   FROM  OPENXML (@idoc, '/root/EmployeeDetails', 2)
   WITH (    EmployeeId INT,
    EmployeeName VARCHAR(100),
  EmployeeDesignation VARCHAR(100),
  EmployeeSalary decimal(18,2),
  EmployeeDepartment VARCHAR(100))

Output of the script above looks like this:

XML is one of the most popular data format for information exchange. In this article, we saw how we can create a documents using XML from a SQL tables. We also saw how to import into a table in SQL from an XML documents.

Please give your valuable feedback and if you have any questions or issues about this article, please let me know.

Also Check What Is Temporary Tables in SQL