XML (eXtensible Markup Language) is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has become the de-facto standard for data sharing. In addition, XML is easily extendable.

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

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

In the script above, we created a Showroom database with one table Car. The Car table has five attributes: CarId, Name, Make, Model, Price, and Type. Next, we added 12 dummy records in the Car table.

Converting into XML from SQL tables

The simplest way to convert data from SQL tables 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:

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 record an element Car has been created in the XML document, and for each column, an attribute 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 creates an XML document where each column is an attribute. On the other hand, the FOR XML PATH will create an XML document where each record is an element and each column is a nested element for a particular record. Let’s see this in action:

A snapshot of the output is as follows:

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

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

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

Now suppose you want that CarId should be the attribute of the Car element rather than an element. You can do so with the following script:

The output looks like this:

You can see now that CarId has become an attribute of the Car element.

We can add further nesting levels to an XML document. For instance, if we want Name, Make and Model elements to be nested inside another element CarInfo we can do so with the following script:

In the output, you will see a new element CarInfo that encloses the Name, Make and Model elements as shown below:

Finally, if you want to convert the elements Name and Make into an attribute of element CarInfo, you can do so with the following script:

The output looks like this:

Save the above XML document with the name Cars.xml. In the next section, we will load this XML script into the SQL Server and will see how to create a table from the XML Document.

For those interested in further articles on FOR XML PATH see FOR XML PATH clause in SQL Serverarticle.

Creating a SQL table from an XML document

In the previous section, we saw how to create an XML document from the SQL table. In this section, we will see how to do the reverse i.e. we will create a table in SQL using XML documents.

The document we will use is the document that we created in the last section. One node of the document looks like this:

Creating SQL table using XML attributes

Let’s first see how we can create an SQL table using attributes. Suppose we want to create a table with two columns that contain the values from the Name and Make attributes from the CarInfo element. We can do so using the following script:

In the script above we declare an XML type variable @cars. The variable stores the result returned by the OPENROWSET function which retrieves XML data in binary format. Next using the SELECT @Cars statement we print the contents of the XML file. At this point in time, the XML document is loaded into the memory.

Next, we create a handle for the XML document. To read the attributes and elements of the XML document, we need to attach the handle with the XML document. The sp_xml_preparedocument performs this task. It takes the handle and the document variable as parameters and creates an association between them.

Next, we use the OPENXML function to read the contents of the XML document. The OPENXML function takes three parameters: the handle to the XML document, the path of the node for which we want to retrieve the attributes or elements and the mode. The mode value of 1 returns the attributes only. Next, inside the WITH clause, we need to define the name and type of the attributes that you want returned. In our case the CarInfo element has two attributes Name, and Make, therefore we retrieve both.

As a final step, we execute the sp_xml_removedocument stored procedure to remove the XML document from the memory. In the output you will see values from the Name and Make attributes of the CarInfo element as shown below:

Creating a SQL table using XML elements

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

Suppose we want to retrieve the values for the nested CarInfo, Price and Type elements of the parent Car element, we can use the following script:

Output of the script above looks like this:

Conclusion

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

• Author
• Recent Posts

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson

Latest posts by Ben Richardson (see all)

• Working with the SQL MIN function in SQL Server - May 12, 2022
• SQL percentage calculation examples in SQL Server - January 19, 2022
• Working with Power BI report themes - February 25, 2021