SQL Server - How to read, import, and export data from XML files

These will be the XML files that I will import into the database and make them into tables for easy manipulation of the information:

Base 1 (Information as Attributes)

Base 2 (Information as Content)

Let's check now, easily and practically, how to do this.

How to import XML files into SQL Server

The first step in validating and working with the data contained in XML files is to import this data into our SQL Server database. If you already have XML data in a table, you can skip this step.

For importing XML from a physical file on disk or in a shared directory of your network, I will use the function fncFile_Ler_Returns_String, from CLR (C #), as it is the easiest and most practical to use.

As I had already shown in the post SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT), including the source code of the function fncFile_Ler_Returns_String, you can also use other ways to import text files at your own discretion or restriction on the instance.

To perform the file import, we can do the following:

How to handle and read attribute data from an XML in SQL Server

Once the XML is already in a table, let's start handling and reading the XML information. In this case, we will need to store the content in an XML type variable.

For this you can do this:

Now that we can load our XML into a variable, let's start handling the data.

Simple reading:

As we saw in the example above, we need to manually define the line we want to return information from our XML. But what if the file has 100, 1.000 or more lines? Will we have to use the WHILE statement to traverse all lines of XML? No. To do so, we can use the NODES function, which applies our SELECT filters to all nodes of our selector and returns them in the form of table rows:

Using the NODES function to return all nodes:

When we use the function nodes, we are informing that for each node in the XML tree that is in the “/ Root / Client” structure, a record from our table is returned. This record is identified using the nomenclature Customers (parent element, which would be like the table) and row (each record of the parent element).

After breaking the nodes into records, we need to retrieve the information for each attribute. For this, we use the function value, followed by @ AttributeName (must be the same as the XML attribute name) and its return type.

Using the EXIST Function

In certain situations, we need to know if a particular node exists in our XML. For this, SQL Server provides us with the exist function, which allows us to perform this type of verification:

Using exist along with the value:

Recovering the child nodes

The first part has been completed: We have already been able to perform a simple import into our XML. But we still have a long way to go: Our XML has N child nodes, with more 2 levels of hierarchy to work with (Address and Phone).

Importing the 1th Hierarchy Level: Address

Note that to get the sublevels of the hierarchy I use a CROSS APPLY of the parent element (Clients) and for each record (row) I make a crossover of this data with child nodes (of type Address).

Importing the 2th Hierarchy Level: Phone

And then we were finally able to import the data. But there is an error there ..

If we take a closer look at our XML, we will see that one of the addresses was not imported, because it had no phone, and since we made a CROSS APPLY, this record was ignored. We will need to address this:

To address this situation, I replaced the last CROSS APPLY with an OUTER APPLY, which behaves similar to a LEFT JOIN in this case, where records that do not have a child node continue to appear in our table, and columns intended for child nodes will be empty Whenever there is a situation where you are not sure whether the element will have children or not, use OUTER APPLY.

How to handle and read XML data in SQL Server

Unlike the 1 base, where the information was all stored as attributes, we will now use the 2 base XML, which is predominantly data.

Simple data reading

As I did with the attributes, I wanted to demonstrate how to individually return each file node by specifying the index of its position relative to the root. Now I will demonstrate again how to use the NODES function to return all nodes as records in a table:

Most complete example, mixing data and attributes:

Using XQuery (XML.query Function)

Often when handling XML files, we feel the need to perform more advanced queries or filters to return the information we want rather than the entire file. For this need, we have the query function:

Using XQuery to filter results:

Returning Student Information Peter:

Working with values:

One thing to note about the query function is that it does not allow you to select attribute data that is at the same level as the current node. For example, if you are using a student selector, you cannot return an attribute from that Student node that you are selecting. If you try to do so, you will encounter one of these error messages:

XQuery [Sqm.data.query ()]: Attribute may not appear outside of an element

XQuery [value ()]: Top-level attribute nodes are not supported

To work around this, use the function value and apply your filters in another way.

Using functions in XML

Another very nice feature of XML is the ability to use functions to filter or get more information from our XML data. I will now demonstrate how to apply this to our sample XML files.

Using numeric functions:

String Functions:

or we can use contains:

and even simple Transact-SQL operations:

Manipulating XML with the modify function

When we are using the modify function, we have further proof of how advanced XML handling is with SQL Server. This function allows us to modify the data stored in the XML variable at run time, and it allows us to insert data, replace data of replace, and delete data.

To use the modify () function, you must perform UPDATE, DELETE or SET @Variable. Use of this function during a SELECT is not allowed.

How to replace values ​​using the replace value of function:

Remove an element from XML using the delete function:

Insert nodes into XML using the insert function:

Insert multiple nodes into XML from one SQL variable:

Using XQuery FLOWR Expressions in XML

The FLOWR feature (pronounced “flower”) is a powerful tool and extension of the QUERY function, which allows iterating over XML files and performing a series of operations. The commands are FOR, LET, ORDER BY, WHERE and RETURN. Let's see below how to use them.

Using the FOR iteration:

Using the FOR, WHERE, and ORDER BY iteration:

Using the LET command to change the value of an information and increase 20% for two employees:
In this specific example, I create XML in real time, because my example 2 XML had no values ​​in data, only in attributes, which makes it impossible to use together with the query function.

Exporting bank data to XML - FOR XML RAW, AUTO, EXPLICIT, PATH

After much talk about importing and handling XML files, it's finally time to go the other way. How to transform data from a SQL Server table to an XML string.

To make this task easier, SQL Server provides 4 ways to do this with FOR XML: RAW, AUTO, EXPLICI, and PATH. Let's find out now what it is for and how to use it.

Test table creation:

FOR XML RAW

The FORW RAW method generates an XML from our table where each column becomes an attribute of the generated XML and each row will represent a node (element).

Generated XML:

In the example below, I will add the ROOT option after RAW (), to add a root element that will be the parent node of the created nodes. This is optional, if you don't use it, the only difference is that the element will not be created.

Generated XML:

Even in XML RAW we can have columns returned as XML elements. To do this, simply include the ELEMENTS option:

Generated XML (just a snippet not to get too big):

Another interesting option of FOR XML is when dealing with empty data (NULL). When we do not perform any treatment, they are simply ignored and are not generated, as is the case for the product category “TV”. To address this, we can use the XSINIL option after the ELEMENTS option, which will add the empty element and create an attribute stating this (xsi: nil = ”true”):

And then the XML with the product is generated like this:

Additionally, we can use the XMLSCHEMA option to transform our XML into a full XSD:

FOR XML AUTO

FOR XML's AUTO mode is very similar to RAW, but with the difference that in its default usage, the table name is the default name of each element.

Generated XML:

FOR XML PATH

XML PATH is a little different from the other two examples, as column names and aliases are treated as XPATH elements. When you generate ordinary XML without customizing it, it includes a row element, where each row is a child element, one hierarchy level below, and each column is also an XML element, plus another level below:

Like XML AUTO and XML RAW, we can use ROOT ('RootName') to create the root element, we can also use the ELEMENTS option with XSINIL as well (to return even null elements).

Note: Because XML PATH always returns columns as elements, using only the ELEMENTS option will have no effect, only if used in conjunction with XSINIL.

Creating hierarchies with XML PATH:

Generated XML:

FOR XML EXPLICIT

FOR XML's EXPLICIT mode already tends to be quite different from other modes. This is because it requires a header in a specific format, defining hierarchy and structures. This header must have joined the data using UNION ALL.

The SELECT of the header must have the following structure:

• First column: It is a number that defines the level of the hierarchy. The column name must be Tag.
• Second column: Is a number that defines the hierarchy level of the parent element (or NULL if it is not and is the root). The column name must be Parent.
• Third column onwards: These are the data that will be part of the XML and will be returned by your XML.

Note that from the beginning we need to define all columns that will be part of the XML already in the header.

The default format for field definition is defined as follows:

XHTML

<NomeDoElemento>!<NúmeroDaTag>!<NomeDoAtributo>[!<InformacoesAdicionais>]

1	<NomeDoElemento>!<NúmeroDaTag>!<NomeDoAtributo>[!<InformacoesAdicionais>]

Where:

• ElementName: Is the name of the parent element we are generating (In the case of the example, Products)
• TagNumber: Is the hierarchy level number of the child elements.
• AttributeName: Is the name of each attribute / column of the data we are exporting to XML (in the case of the example, Category and Description)
• Additional Information: Additional Data That Can Be Used in Building XML

Let's see now how this works in practice:

XML excerpt returned:

For example it looks cooler, I'll add a new column to our table:

Regenerating XML:

Our result will be this:

It is noteworthy that this ORDER BY used is necessary for the results to be displayed in the correct form. Otherwise, the elements will be generated in the wrong order and the XML will not have the same result.

As we can see, the new field added (ID) is an XML attribute. If you want to make it an attribute, just add! ELEMENT to your header:

This way:

In the two examples above, I added an ISNULL () clause to the category, since the product “TV” has no defined category. When this occurs in EXPLICIT mode, and the category is used to order the results, the elements end up being lost and the uncategorized are together with elements from other categories.

Another way to solve this problem, besides adding ISNULL (), is by defining the ELEMENTXSINIL attribute type in the structure header of our SELECT and not using this column that has possible NULL values ​​in ORDER BY:

Generated XML: