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

SQL
9 Comments

Let us learn about XML Document in TSQL using XSD by Jacob Sebastian.

XML Schema Collections

SQL SERVER - Validate an XML Document in TSQL using XSD by Jacob Sebastian xmldocument 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>'

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – A Daily Doze of Technology – Alvin Ashcraft’s Morning Dew
Next Post
SQLAuthority News – Airline Review – Paramount, Kingfisher, Go Air, Indigo, Jet Airways, Indian Airlines, Spicejet

Related Posts

9 Comments. Leave new

  • Simple and perfect example, for any newbie to understand. Great thanks to sebastian and pinal for this lucid explanation.

    Reply
  • How does the schema shown above enforce having elements named FirstName, MiddleName, LastName?

    Reply
  • 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

    Reply
  • 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>'

    Reply
  • Jerry Scannell
    April 2, 2010 11:15 pm

    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

    Reply
  • 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.

    Reply
  • Very concise and useful. Thanks Pinal and Jacob.

    Reply
  • Do you have a way to generate SQL tables from XML Schema Collections?

    Reply
  • Anirudh Sriram
    June 21, 2012 9:56 am

    Brilliant! Thanks a tonne!

    Reply

Leave a Reply

Menu