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:
- AUTO
- RAW
- EXPLICIT
- 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
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
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.
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
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’
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
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
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
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.
- 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.
Basant Singh
Latest posts by Basant Singh (see all)
- Go Maps Introduction for Beginners - May 20, 2015
- Go Programming – Arrays and Slices - May 11, 2015
- Golang Switch Case with Sample Code - May 1, 2015
