共用方式為

Complementing XSD with CHECK constraints

  • 發行項

I was recently asked to look at a customer’s question. This person wanted to write a schema that would validate instances like the following one.

<TopElement>

  <a>

    <element1 att1="data1" att2="data2"/>

    <an_element att1="data 1" att2="data 2"/>

    <something att1="abc" att2="def"/>

  </a>

</TopElement>

The customer had two requirements for the children of element ‘a’.

1. They should be allowed to have any valid unqualified name.

2. They are required to contain both attributes att1 and att2.

The first requirement can easily be taken care of using element wildcards but I couldn’t come up with a way to define the second constraint in an XML schema.

There are constraints like this one that are not expressible (or not easily expressible) using XSD but that are easy to check using a simple XQuery. The usual workaround is to use a T-SQL CHECK constraint.

First, let’s create a schema collection

CREATE XML SCHEMA COLLECTION SC AS '

<schema xmlns="https://www.w3.org/2001/XMLSchema">

      <element name="TopElement">

            <complexType>

                  <sequence>

                        <element name="a">

            <complexType>

                                    <sequence>

                                          <any processContents="skip" namespace="##local" minOccurs="0" maxOccurs="unbounded"/>

                                    </sequence>

                              </complexType>

                        </element>

                  </sequence>

            </complexType>

      </element>

</schema>

'

go

If we type an XML column with this collection, we will accept all unqualified elements as children of ‘a’, whether they contain the required attributes or not. In order to implement this second requirement, we are going to use a CHECK constraint.

Since CHECK constraints don’t allow calls to XML data type methods, we need to create a user-defined function.

CREATE FUNCTION dbo.checkAttrs(@x XML(SC))

RETURNS bit

AS

BEGIN

      RETURN ~(@x.exist('/TopElement/a/*[not( ./@att1 and ./@att2 )]'))

END

go

The function defined above checks for the presence of a child element of ‘a’ that doesn’t contain both attributes att1 and att2. If such an element exists, the function returns 0. If none is found, the function returns 1.

With this function, we can now create a table with an XML column that behaves according to the user’s specifications.

CREATE TABLE T(xmlCol XML(SC)

CHECK (1 = dbo.checkAttrs(xmlCol)))

go

When an XML instance is inserted into or modified inside table T it is first validated against the schemas present in the ‘SC’ schema collection. Then the server verifies that CHECK constraint holds true. If the instance doesn’t validate against the schema, or if it violates the check constraint, it is rejected.

In conclusion this short example shows how you can harness the power of XQuery through the T-SQL CHECK syntax in order to implement constraints that cannot be defined with XSD alone.

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments

  • Anonymous
    August 22, 2006
    The comment has been removed
  • Anonymous
    August 22, 2006
    Kris,

       The XML implementation in the SQL engine is completely separate from the CLR. There are two different code bases, and two different development teams.

    As to whether or not it makes sense to do this at the database level, I have to admit that this is not an easy question to answer.

    The main advantage IMHO is that it is easy to implement and the constraints will be enforced no matter which client application inserts/modifies data.

    On the other hand the server is going to incur a cost for validating and typing the XML instances as well as for enforcing the constraints.

  • Anonymous
    December 03, 2008
    PingBack from http://kintespace.com/rasxlog/?p=1265