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:
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.
We can use two XML functions called nodes and value effectively to parse the XML data from this table as shown below.
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.
Parsing from multiple columns of data
An example of multi-column multi-valued data is displayed below.
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.
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.
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.
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.
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.
Although XML data type plays an important role for some specific requirements, it has some limitations too.
- This column can store maximum length up to 2 GB,
- Data in an xml column can not converted to text or ntext data types, we can only convert to nvarchar,
- 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.