XML validation using Schema in TSQL

At times, we have to validate the XML input passed from different sources. In SQL Server, we can use XML Schema Collection to have a schema defined and then use the same for validating corresponding XML inputs which are received. XML Schema declaration and consumption is a vast topic with lot of available tags and simultaneously with lot of restrictions and limitations. In future articles I am going to narrate about different key concepts and tips one should remember in handling XML in SQL Server.

In this tutorial, we are going to create a simple Student XML Schema with specific declarations on his basic data, enrolled information, semester details and his previous semesters’ results. Our schema will compress of some basic validations like Data types validations, required elements etc.

Check out following code for sample implementation. Comments are provided at specific points for more details explanation.

/*********************************** 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 ****************************************
Following Validations are enforced - 

** Student element can occur in range of 1 to 60.
** All elements should be in sequence specified.
** All elements and attributes data must match respective datatypes.
** All attributes are required except EnrolledSemester attribute.
** Address element can occur more than once.
** PreviousSemester element can occur only once, but its internal Semester element can occur many
   times.
** IsQualifiedForNextSemester element is fixed to accept only 1 as value.
** SemesterStartDate element will allow any valid date, if not supplied it will default to 2013-01
   -14.
** Stream attribute is fixed to accept only "Engineering" value.

*************************************************************************************************/
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="SemestedPercentage" 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">
                        <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: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>Rami</stu:FullName>
       <stu:BirthDate>1984-02-14</stu:BirthDate>
       <stu:OverallPercentage>93</stu:OverallPercentage>
       <stu:IsQualifiedForNextSemester>1</stu:IsQualifiedForNextSemester>
       <stu:SemesterStartDate/>
       <stu:Address>
              <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:SemestedPercentage="89" />
              <stu:Semester stu:SemesterNumber="2" stu:SemestedPercentage="94" />
              <stu:Semester stu:SemesterNumber="3" stu:SemestedPercentage="92" />
       </stu:PreviousSemesters>
</stu:Student>
</stu:Students>
'
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 –

XmlSchemaValidation

You may also like...

  • Ashwin

    Nice and understandable blog,

    I want to ask , can we do validation without using XML Schema collection.

    Thanks in Advance.

  • RamiVemula

    @Ashwin – you might need to do manual checks using xml parsing. It is not worth it.

  • Srikanth

    Hi Rami, nice write up…wanted to check if the order of attributes defined in the XML Schema has to be same as that of xml…thank you