Parse XML data in TSQL

This articles narrates on how to parse XML data in TSQL using NODES(). In this tutorial we are going to validate input XML data against its corresponding schema. Upon successful validation data has been extracted from the same using nodes() method. Nodes() method can be used in TSQL for extracting relational information from a XML data type.

Following code evaluates a student xml data. In the first query we directly extract particular student information along with attributes credited as columns. In the second query, we extract subtree data of a student i.e., each and every semester information of a student along with student full name and enrollment id.

Code is self explanatory and comments are provided at necessary places for ease in understanding.

/*********************************** Create TestDB Database **************************************/
IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO

USE [TestDB]
GO


/**************************** Drop existing Student Schema (if any) ******************************/
IF EXISTS(SELECT * FROM sys.xml_schema_collections WHERE name = 'StudentSchema') 
BEGIN
       DROP XML SCHEMA COLLECTION StudentSchema
END
GO


/********************************** Create Student Schema ****************************************
For more information on Schema Validation of XML data che4ck out following article - 

http://www.intstrings.com/ramivemula/articles/xml-validation-using-schema-in-tsql/

*************************************************************************************************/
CREATE XML SCHEMA COLLECTION StudentSchema AS 
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema 
              targetNamespace="http://www.intstrings.com/ramivemula/studentschema" 
              xmlns:stu="http://www.intstrings.com/ramivemula/studentschema"  
              elementFormDefault="qualified" 
              attributeFormDefault="qualified" 
              xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:complexType name="SemesterType">
    <xsd:sequence>
        <xsd:element name="Semester" maxOccurs="unbounded">
            <xsd:complexType>
                <xsd:attribute name="SemesterNumber" type="xsd:integer" use="required" />
                <xsd:attribute name="SemesterPercentage" type="xsd:integer" use="required" />
            </xsd:complexType>
        </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="Students">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Student" minOccurs="1" maxOccurs="60">
            <xsd:complexType>
                <xsd:sequence>
                    <xsd:element name="FullName" type="xsd:string" />
                    <xsd:element name="BirthDate" type ="xsd:date" />
                    <xsd:element name="OverallPercentage" type="xsd:integer" />
                    <xsd:element name="IsQualifiedForNextSemester" type="xsd:boolean" fixed="1"/>
                    <xsd:element name="SemesterStartDate" type="xsd:date" default="2013-01-14"/>
                    <xsd:element name="Address" minOccurs="1" maxOccurs="3">
                        <xsd:complexType>
                            <xsd:sequence>
                                    <xsd:element name="City" type="xsd:string" />
                                    <xsd:element name="State" type="xsd:string" />
                                    <xsd:element name="Country" type="xsd:string" />
                                    <xsd:element name="Phone" type="xsd:string" />
                            </xsd:sequence>
                            <xsd:attribute name="AddressType" type="xsd:string" use="required" />
                        </xsd:complexType>
                    </xsd:element>
                    <xsd:element name="PreviousSemesters"  type="stu:SemesterType" maxOccurs="1">
                    </xsd:element>
                </xsd:sequence>
                <xsd:attribute name="EnrollmentNumber" type="xsd:string" use="required" />
                <xsd:attribute name="Stream" type="xsd:string" fixed="Engineering" use="required" />
                <xsd:attribute name="EnrolledSemester" type="xsd:integer" default="1" />
            </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
'
GO


/***************************** Test XML against Student Schema ***********************************
*************************************************************************************************/
DECLARE @student AS XML(StudentSchema)
SET @student = '
<stu:Students xmlns:stu="http://www.intstrings.com/ramivemula/studentschema">
<stu:Student stu:EnrollmentNumber="1001" stu:Stream="Engineering" stu:EnrolledSemester="3">
       <stu:FullName>Ramilu</stu:FullName>
       <stu:BirthDate>1984-02-14</stu:BirthDate>
       <stu:OverallPercentage>93</stu:OverallPercentage>
       <stu:IsQualifiedForNextSemester>1</stu:IsQualifiedForNextSemester>
       <stu:SemesterStartDate/>
       <stu:Address stu:AddressType="Home">
              <stu:City>Seattle</stu:City>
              <stu:State>Washington</stu:State>
              <stu:Country>USA</stu:Country>
              <stu:Phone>123-456-7890</stu:Phone>
       </stu:Address>
          <stu:Address stu:AddressType="Dorm">
              <stu:City>Long Beach</stu:City>
              <stu:State>California</stu:State>
              <stu:Country>USA</stu:Country>
              <stu:Phone>123-456-7890</stu:Phone>
       </stu:Address>
       <stu:PreviousSemesters>
              <stu:Semester stu:SemesterNumber="1" stu:SemesterPercentage="89" />
              <stu:Semester stu:SemesterNumber="2" stu:SemesterPercentage="94" />
              <stu:Semester stu:SemesterNumber="3" stu:SemesterPercentage="92" />
       </stu:PreviousSemesters>
</stu:Student>
<stu:Student stu:EnrollmentNumber="1001" stu:Stream="Engineering" stu:EnrolledSemester="3">
       <stu:FullName>Rami</stu:FullName>
       <stu:BirthDate>1984-02-14</stu:BirthDate>
       <stu:OverallPercentage>19</stu:OverallPercentage>
       <stu:IsQualifiedForNextSemester>1</stu:IsQualifiedForNextSemester>
       <stu:SemesterStartDate/>
       <stu:Address stu:AddressType="Home">
              <stu:City>Seattle</stu:City>
              <stu:State>Washington</stu:State>
              <stu:Country>USA</stu:Country>
              <stu:Phone>123-456-7890</stu:Phone>
       </stu:Address>
          <stu:Address stu:AddressType="Dorm">
              <stu:City>Long Beach</stu:City>
              <stu:State>California</stu:State>
              <stu:Country>USA</stu:Country>
              <stu:Phone>123-456-7890</stu:Phone>
       </stu:Address>
       <stu:PreviousSemesters>
              <stu:Semester stu:SemesterNumber="4" stu:SemesterPercentage="78" />
              <stu:Semester stu:SemesterNumber="5" stu:SemesterPercentage="59" />
              <stu:Semester stu:SemesterNumber="6" stu:SemesterPercentage="69" />
       </stu:PreviousSemesters>
</stu:Student>
</stu:Students>
';


/****************************** Extract information from XML ***********************************
Extract student personal information from XML data using nodes.
************************************************************************************************/

WITH XMLNAMESPACES ('http://www.intstrings.com/ramivemula/studentschema' AS st)
SELECT
       [EnrollmentId] = 
                           [Student].value('@st:EnrollmentNumber', 'int'),
       [Stream] = 
                           [Student].value('@st:Stream', 'varchar(255)'),
       [FullName] = 
                           [Student].value('st:FullName', 'varchar(255)'),
       [BirthDate] = 
                           [Student].value('st:BirthDate', 'date'),
       [Percentage] = 
                           [Student].value('st:OverallPercentage', 'int'),
       [IsQualified] = 
                           [Student].value('st:IsQualifiedForNextSemester', 'bit'),
       [HomeCity] = 
                           [Student].value('st:Address[@st:AddressType="Home"][1]/st:City', 'varchar(255)'),
       [HomeState] = 
                           [Student].value('st:Address[@st:AddressType="Home"][1]/st:State', 'varchar(255)'),
       [HomeCountry] = 
                           [Student].value('st:Address[@st:AddressType="Home"][1]/st:Country', 'varchar(255)'),
       [HomePhone] = 
                           [Student].value('st:Address[@st:AddressType="Home"][1]/st:Phone', 'varchar(255)'),
       [City] = 
                           [Student].value('st:Address[@st:AddressType="Dorm"][1]/st:City', 'varchar(255)'),
       [State] = 
                           [Student].value('st:Address[@st:AddressType="Dorm"][1]/st:State', 'varchar(255)'),
       [Country] = 
                           [Student].value('st:Address[@st:AddressType="Dorm"][1]/st:Country', 'varchar(255)'),
       [Phone] = 
                           [Student].value('st:Address[@st:AddressType="Dorm"][1]/st:Phone', 'varchar(255)')
FROM
    @student.nodes('/st:Students/st:Student') as Students([Student]);


/****************************** Extract information from XML ***********************************
USe CrossApply to get related infromation of Student with his corresponding semesters records.
************************************************************************************************/

WITH XMLNAMESPACES ('http://www.intstrings.com/ramivemula/studentschema' AS st)
SELECT 
       [EnrollmensId] = 
                           Students.[Student].value('@st:EnrollmentNumber', 'int'),
       [FullName] = 
                           Students.[Student].value('st:FullName', 'varchar(255)'),
       [SemesterNumber] = 
                           StudentSemesters.[Semester].value('@st:SemesterNumber', 'int'),
       [SemesterPercentage] = 
                           StudentSemesters.[Semester].value('@st:SemesterPercentage', 'int')
FROM
       @student.nodes('/st:Students/st:Student') as Students([Student])
       CROSS APPLY 
       Students.[Student].nodes('st:PreviousSemesters/st:Semester') StudentSemesters([Semester]);

GO


/********************************** Drop TestDB Database ***************************************/
USE master
GO

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

Output –

XmlParse

You may also like...