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

Views: 8.312
Reading Time: 16 minutes

Hello people,
Good night!

In this post I will demonstrate to you how to import, read, handle and export data between a SQL Server table and an XML file.

This integration between database and XML files is a great feature and a big differentiator for developers using SQL Server who can easily read and generate files in this format natively through the database.

Knowing the Sample 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

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 the attributes of an XML in SQL Server

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:

SQL Server - Read XML Attributes

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:

SQL Server - Read XML Attributes with Nodes

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

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:

SQL Server - Verify Check if XML Attribute exists

Using exist along with the value:

SQL Server - Verify Check if XML Attribute exists function with 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

SQL Server - Read Parse XML data from 2 file

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

SQL Server - Read Parse XML data from 3 file

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

SQL Server - Read Parse XML data from 4 file

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.

SQL Server - Read Parse XML data from 5 file

How to handle and read XML data in SQL Server

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

SQL Server - Read XML Data

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:

SQL Server - Read XML Data with Nodes function

Most complete example, mixing data and attributes:

SQL Server - Read XML Data with Nodes function 2

Using XQuery (XML.query Function)

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:

SQL Server - XML ​​XQuery query function

Using XQuery to filter results:

SQL Server - XML ​​XQuery query function filter data

Returning Student Information Peter:

SQL Server - XML ​​XQuery query function filter data 2

Working with values:

SQL Server - XML ​​XQuery query function filter data 3

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

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:

SQL Server - XML ​​XQuery query function filter data 4

String Functions:

or we can use contains:

and even simple Transact-SQL operations:

SQL Server - XML ​​XQuery query function filter data string

Manipulating XML with the modify function

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:

SQL Server - XML ​​XQuery replace value of modify function

Remove an element from XML using the delete function:

SQL Server - XML ​​XQuery delete modify function

Insert nodes into XML using the insert function:

SQL Server - XML ​​XQuery insert modify function

Insert multiple nodes into XML from one SQL variable:

SQL Server - XML ​​XQuery insert sql variable modify function

Using XQuery FLOWR Expressions in XML

Using XQuery FLOWR Expressions in XML

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

Using the FOR iteration:

SQL Server - XML ​​XQuery FLOWR expression query function for return

Using the FOR, WHERE, and ORDER BY iteration:

SQL Server - XML ​​XQuery FLOWR expression query function for return where order by

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.

SQL Server - XML ​​XQuery FLOWR expression query function for return where order by let

Exporting database data to XML - FOR XML

Exporting database 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 do not use it, the only difference is that the <root> 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 not generated, as is the case with 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:

SQL Server - FOR XML AUTO XMLSCHEMA

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:

SQL Server - FOR XML PATH 1

Just 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 too (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:
SQL Server - FOR XML PATH 2

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:

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:
SQL Server - FOR XML EXPLICIT

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

Regenerating XML:

Our result will be this:
SQL Server - FOR XML EXPLICIT 2

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:
SQL Server - FOR XML EXPLICIT 3

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

SQL Server - FOR XML EXPLICIT 5

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:
SQL Server - FOR XML EXPLICIT 4

If you have any questions or suggestions, leave it here in the comments.
Thanks for stopping by and see you next time!

sql server how to learn work use read import handle xml string file learning

sql server how to learn work use read import handle xml string file learning