SQL SERVER – Validate an XML document in TSQL using XSD by Jacob Sebastian

Following article is sent by SQL Server MVP Jacob Sebastian on request to provide solution for validating XML document.

XML Schema Collections

Because of the ‘eXtensible’ nature of XML (eXtensible Markup Language), often there is a requirement to restrict and validate the content of an XML document to a pre-defined structure and values. XSD (XML Schema Definition Language) is the W3C recommended language for describing and validating XML documents. SQL Server implements XSD as XML Schema Collections.

An XML Schema Collection can be used to validate an XML document stored in an XML column or variable.  To understand this better, let us see an example.

XML Document

For the purpose of this example, let us create an XML Schema Collection to validate the following XML Document.

<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>

Validation Rules

Here are the validation rules we are trying to implement.

  1. The root element should be named ‘Employee’
  2. The root element should have three child elements, named ‘FirstName’, ‘MiddleName’ and ‘LastName’.
  3. Child elements should appear exactly in the order given above.

Creating the XML Schema Collection

Here is the XML Schema collection that performs the above validations.

CREATE XML SCHEMA COLLECTION EmployeeSchema
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element />
                <xsd:element />
                <xsd:element />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

Validating an XML variable

The following code validates an XML document against the rules defined in the XML Schema Collection.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>'

Note that the operation will succeed only if all the validation rules defined in the XML schema collection are met. For example, the following will fail because the XML document is missing the ‘MiddleName’ element.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <LastName>Sebastian</LastName>
</Employee>'

Please read further details on the same subject over SyntaxHelp XML.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

11 thoughts on “SQL SERVER – Validate an XML document in TSQL using XSD by Jacob Sebastian

  1. Hi Pinal,
    When i ran the CREATE XML SCHEMA script, i got this error:
    Msg 2299, Level 16, State 1, Line 1
    Required attribute “name” of XSD element “element” is missing.

    Any thoughts on this.

    Thank you

    Like

  2. Hi Pinal,
    When i modified the CREATE XML SCHEMA COLLECTION to include the element name it worked:
    CREATE XML SCHEMA COLLECTION EmployeeSchema

        
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

        <xsd:element name = "Employee" >

            <xsd:complexType>

                <xsd:sequence>

                    <xsd:element name = "FirstName" />

                    <xsd:element name = "MiddleName"/>

                    <xsd:element name = "LastName" />

                </xsd:sequence>

            </xsd:complexType>

        </xsd:element>

    </xsd:schema>'

    Like

  3. I’ve tried everything I can to create an XML SCHEMA in SQL Server 2008.

    Maybe someone can help. Here’s the XML that I need a schema for:

    e4933d5d-7452-4fad-b557-125d700da000

    Here is my original XSD SCHEMA that successfully ‘compiled’ into a XML SCHEMA:

    CREATE XML SCHEMA COLLECTION [dbo].[GUIDSchema]
    AS
    N’


    GO

    Here is the function that makes use of the schema:
    ALTER FUNCTION [dbo].[fnGUID_from_XML] ( @inputXML XML )
    RETURNS uniqueidentifier
    AS
    BEGIN
    DECLARE @ValidateXML as XML(GUIDSchema)
    SET @ValidateXML = @inputXML

    DECLARE @GUID as uniqueidentifier
    DECLARE @PL_FILE TABLE (TempGUID uniqueidentifier )

    INSERT INTO @PL_FILE (TempGUID)
    SELECT ParamValues.Items.value(‘GUID[1]’,’uniqueidentifier’)
    FROM @ValidateXML.nodes(‘/NODE’) as ParamValues(Items)

    select @GUID = TempGUID from @PL_FILE
    RETURN @GUID
    END

    However, when I try to compile the above function, I get this error:
    Msg 9314, Level 16, State 1, Procedure fnGUID_from_XML, Line 18
    XQuery [value()]: Cannot implicitly atomize or apply ‘fn:data()’ to complex content elements, found type ‘xs:anyType’ within inferred type ‘element(GUID,xs:anyType) ?’.

    I have tried removing the various tags from the schema, but can never compile the schema again. The only code that creates a successful schema compilation is what I’ve provided here.

    Thanks in advance,
    Jerry

    Like

  4. Hi,
    Im poorna. Can any one please let me know how to get alla error messages at once while validating xml with xsd in sql2008?
    thank a lot in advance.

    Like

  5. Pingback: SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31 « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s