Skip to content

MS SQL

Parsing XML Data in MS SQL Server

In the first part of this article titled XML Data Processing in MS SQL Server we saw how we can represent relation data in SQL Server in XML format by using FOR XML clause in one of the following four ways:

  1. AUTO
  2. RAW
  3. EXPLICIT
  4. PATH

In this article, we will continue with Parsing XML Data in MS SQL Sever.

Parsing of single column multi-valued data from XML

Example of single column multivalued data in XML is shows below.

Example of single column multivalued data with varying number of elements

Example of single column multivalued data with varying number of elements

We can use two XML functions called nodes and value effectively to parse the XML data from this table as shown below.

values for ‘c1’ and ‘c2’ has been extracted using nodes and value functions in XML

Values for ‘c1’ and ‘c2’ has been extracted using nodes and value functions in XML

In our example data as we do not know how many multi-values can be present in one row (c1, c2…); so we will need to use loop and increment the iterator and check whether for the next iteration value is present or not. At some point we will not get any row returned (as shown below when we are using c7 which is not present in the multi-value column) and that’s when need to stop processing.

pic10

Value function returning no row when non-existing element is supplied in xpath

Parsing from multiple columns of data

An example of multi-column multi-valued data is displayed below.

Example of multiple column multi valued data in XML

Example of multiple column multi valued data in XML

Unlike the previous single column example; this pattern is slightly different because here one single column position (denoted by ‘c1’, ‘c2’ etc) can have multiple values that are identified by an attribute ‘m’. For example, ‘c5’ is having 4 different values here as the maximum value of ‘m’ is 4. In the below example, how to parse all the contents of a specific column position can be retrieved in different rows has been shown.

Parsing of all the contents or a specific content from column position ‘c7’

Parsing of all the contents or a specific content from column position ‘c7’

As shown in the example below; we can get the total number of value sets present for a specific column position and it is also possible to apply aggregate function like MAX or SUM on the values present in a specific column position.

Getting total number of value sets present in a column position and applying aggregate function

Getting total number of value sets present in a column position and applying aggregate function

Representation of XML data

As with normal relational data, we can create views from XML fields as shown below. But it should be noted that for those columns that can have variable number of value sets in a row can not be included in view definition.

Creation of view for picking up data from specific column positions in XML column

Creation of view for picking up data from specific column positions in XML column

We can also create computed columns on tables containing XML data to segregate frequently accessed information into a separate column. But we can not create a computed column directly from the xml column as shown in below screen shot.

Directly assigning expression in computed column from xml column is not allowed

Directly assigning expression in computed column from xml column is not allowed

To achieve this functionality we will need to write a wrapper function that will return the result from the xml column and we can use that function as the computed column expression.

Source Code Download

You can download the required source code for both the articles from this Dropbox URL.

Conclusion

Although XML data type plays an important role for some specific requirements, it has some limitations too.

  1. This column can store maximum length up to 2 GB,
  2. Data in an xml column can not converted to text or ntext data types, we can only convert to nvarchar,
  3. Does not support sorting or comparing and that is why we can not perform GROUP BY with these columns.

Hope, reading this series of two articles made you confident about XML Data Processing.

 

 

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)