SQL Server Typed XML using XSD


SQL Server actually supports two kinds of XML objects: typed and untyped. What distinguishes the two is whether the XML column, variable, or parameter is associated with a specific schema collection, a database entity (like a table or stored procedure) that specifies the structure and data types that an XML document must adhere to. If a database object is associated with a collection, it is considered typed, otherwise it is untyped.

In the previous post in this series we saw an example of an untyped XML objects because no schema collections are associated with them.

An XML schema collection is made up of one or more XML Schema Definition (XSD) schemas that are used to validate XML data stored in a typed XML object. The XSD schemas contain the actual formatting information that defines the structure and data types an XML instance must use when saved to a typed XML object. As Jacob Sabastian in his book The Art of XML says: “When information is exchanged in XML format, there needs to be an agreement between the sender and receiver about the structure and content of the XML document. An XSD (XML Schema Definition Language) Schema can be used to enforce this contract and validate the XML data being exchanged.” That’s right, an entire book was written on XSD.

Before you can associate an XML schema collection with an XML object, the collection must exist as an entity within the database. In other words, you must specifically create the collection before you can reference it.

USE myDatabase
GO

CREATE XML SCHEMA COLLECTION ClientInfoCollection AS 
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns="urn:ClientInfoNamespace" 
targetNamespace="urn:ClientInfoNamespace" 
elementFormDefault="qualified">
  <xsd:element name="People">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Person" minOccurs="1" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="FirstName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="LastName" type="xsd:string" minOccurs="1" maxOccurs="1" />
              <xsd:element name="FavoriteBook" type="xsd:string" minOccurs="0" maxOccurs="5" />
            </xsd:sequence>
            <xsd:attribute name="id" type="xsd:integer" use="required"/>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>'
GO

Once you’ve added your schema collection to the database, you can create typed XML columns, variables, and parameters.

Leave a comment

Your email address will not be published. Required fields are marked *