Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop VI - Typed XML and SCHEMA Collection

By Jacob Sebastian,

Introduction

SQL Server 2005 supports two flavors of XML, namely TYPED and UNTYPED. A TYPED XML column or variable is bound to an XML schema which defines the structure of the XML that the variable or column can store. An UNTYPED XML variable or column can store any XML value. However, a TYPED XML variable or column can store only XML values with the specific structure defined by the SCHEMA.

Just like a TABLE has a schema which defines the columns, their data type, precision etc, the XML SCHEMA which is bound to a TYPED XML variable/column specifies the structure of the XML that it can store. Using TYPED XML will make your code more efficient as SQL Server has detailed knowledge about the structure of your XML column/variable.

In this session of the XML Workshop, I am trying to present a few examples, which would explain how to create and XML schema for a required XML structure.

The Problem

For the purpose of this example, let us assume that we need to define an XML structure which contains Customer Information for an order processing application. The application receives customer information from other applications within the enterprise. Our application expects the customer data to arrive in a specific XML format. We want SQL Server to perform the validation while inserting the XML data to the table. To facilitate this, we need to create an XML schema which specifies the required structure of the XML and bind it to the column in the table. When a column or variable is bound to an XML schema, SQL Server will perform validations while inserting or updating data, to make sure that the value matches with the given XML schema.

Here is the XML structure that our application requires.

    1 <Customers>

    2   <Customer CustomerNumber="A001">

    3     <Name>

    4       <FirstName>Jacob</FirstName>

    5       <MiddleName>V</MiddleName>

    6       <LastName>Sebastian</LastName>

    7     </Name>

    8     <Address>

    9       <Street>401, Time Square</Street>

   10       <City>Ahmedabad</City>

   11       <State>Gujarat</State>

   12       <Zip>380006</Zip>

   13     </Address>

   14     <Contact>

   15       <Phone>999 999 9999</Phone>

   16       <Fax>888 888 8888</Fax>

   17       <Email>jacob@dotnetquest.com</Email>

   18     </Contact>

   19   </Customer>

   20 </Customers>

Our task is to create an XML schema for the above XML structure. We will accept the value only if it is as per the above structure. So let us start defining the XSD Schema required to validate the above XML structure.

Creating the XML Schema

At first glance, an XSD Schema might look very confusing. In this session, I am trying to present an approach which starts with a basic schema and enhances it to meet our requirements. So, let us first create a basic schema with minimum code.

    1 /*

    2     Let us first make the basic XML schema for our customer XML structure.

    3     In the declaration below, we are creating an XML Schema Collection

    4     with the name "CustomerSchema". The schema defines an element

    5     named "Customers"

    6 */

    7 

    8 CREATE XML SCHEMA COLLECTION CustomerSchema AS '

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

   10   <element name="Customers">

   11   </element>

   12 </schema>

   13 '

   14 GO

   15 /*

   16 The "schema" element defines the schema. This is the root element of our schema.

   17 "<element name="customers">" defines the root node of our XML structure.

   18 

   19 Now let us create an XML variable which is bound to the "CustomerSchema"

   20 */

   21 

   22 DECLARE @cust AS XML(CustomerSchema)

   23 SET @cust = '

   24 <Customers>

   25 </Customers>

   26 '

   27 

   28 /*

   29 When you assign a value to a variable, which is bound to a schema, SQL Server

   30 validates the value being assigned. For example, the following code will generate

   31 an error, because the element "Customer" is not defined in the schema.

   32 */

   33 

   34 DECLARE @wrong AS XML(CustomerSchema)

   35 SET @wrong = '<Customer></Customer>'

   36 

   37 /*

   38 OUTPUT:

   39 

   40 Msg 6913, Level 16, State 1, Line 2

   41 XML Validation: Declaration not found for element 'Customer'. Location: /*:Customer[1]

   42 */

Now we have a minimal schema. Let us start enhancing it. Below the Customers element (root) we need 0 or more child elements named Customer. Let us write the SCHEMA for it. [Code]

    1 /*

    2 Let us drop the previous SCHEMA and create the new version.

    3 */

    4 

    5 DROP XML SCHEMA COLLECTION CustomerSchema

    6 GO

    7 

    8 /*

    9     Let us enhance the schema so that the "Customers" element can contain

   10     0 or more "Customer" elements.

   11 */

   12 CREATE XML SCHEMA COLLECTION CustomerSchema AS '

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

   14   <element name="Customers">

   15     <complexType>

   16       <sequence>

   17         <element name="Customer" minOccurs="0">

   18         </element>

   19       </sequence>

   20     </complexType>

   21   </element>

   22 </schema>

   23 '

   24 GO

   25 

   26 /*

   27 "Customer" element contains other elements and attributes. Hence we marked it as

   28 a "complexType".

   29 

   30 The "sequence" indicator specifies the order in which the child elements should

   31 occur inside the parent element. Ignore "sequence" for the time being. I will explain

   32 "sequence" in the next example.

   33 

   34 "minOccurs=0" specifies that the element "Customer" is optional.

   35 

   36 Let us test the schema.

   37 */

   38 

   39 DECLARE @cust AS XML(CustomerSchema)

   40 SET @cust = '

   41 <Customers>

   42     <Customer />

   43 </Customers>

   44 '

Let us move ahead. Each Customer element should contain a mandatory attribute named CustomerNumber. Let us enhance the SCHEMA to support this. [Code]

    1 

    2 DROP XML SCHEMA COLLECTION CustomerSchema

    3 GO

    4 

    5 /*

    6     Each "Customer" element should have an attribute named "CustomerNumber".

    7     Let us enhance the schema again.

    8 */

    9 CREATE XML SCHEMA COLLECTION CustomerSchema AS '

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

   11   <element name="Customers">

   12     <complexType>

   13       <sequence>

   14         <element name="Customer" minOccurs="0">

   15           <complexType>

   16             <attribute name="CustomerNumber" type="string" use="required" />

   17           </complexType>

   18         </element>

   19       </sequence>

   20     </complexType>

   21   </element>

   22 </schema>

   23 '

   24 GO

   25 

   26 /*

   27 use="required" specifies that the attribute "CustomerNumber" is mandatory. The following code

   28 will generate a compile time error

   29 

   30 DECLARE @cust AS XML(CustomerSchema)

   31 SET @cust = '

   32 <Customers>

   33     <Customer />

   34 </Customers>

   35 '

   36 

   37 Correct XML value is given below.

   38 */

   39 

   40 DECLARE @cust AS XML(CustomerSchema)

   41 SET @cust = '

   42 <Customers>

   43     <Customer CustomerNumber="A001"/>

   44 </Customers>

   45 '

At the next step, we will add the SCHEMA for the 3 child elements under the Customer element: Name, Address and Contact. [Code]

    1 

    2 DROP XML SCHEMA COLLECTION CustomerSchema

    3 GO

    4 

    5 /*

    6     Each "Customer" element should have "Name", "Address" and "Contact"

    7     nodes.

    8 */

    9 CREATE XML SCHEMA COLLECTION CustomerSchema AS '

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

   11   <element name="Customers">

   12     <complexType>

   13       <sequence>

   14         <element name="Customer" minOccurs="0">

   15           <complexType>

   16             <sequence>

   17               <element name="Name" minOccurs="1" maxOccurs="1" />

   18               <element name="Address" minOccurs="1" maxOccurs="1" />

   19               <element name="Contact" minOccurs="1" maxOccurs="1" />

   20             </sequence>

   21             <attribute name="CustomerNumber" type="string" use="required" />

   22           </complexType>

   23         </element>

   24       </sequence>

   25     </complexType>

   26   </element>

   27 </schema>

   28 '

   29 GO

   30 

   31 /*

   32 Note that I have set "minOccurs" and "maxOccurs" to 1 which specifies that

   33 each element should be present in the XML data EXACTLY once.

   34 

   35 We have 3 child elements under the "Customer" element. Note the usage of

   36 "sequence". "sequence" specifies that the elements should occur exactly in the same

   37 order. The following example will generate an error, because the "Address" element

   38 is placed after the "Contact" element

   39 */

   40 

   41 DECLARE @cust AS XML(CustomerSchema)

   42 SET @cust = '

   43 <Customers>

   44     <Customer CustomerNumber="A001">

   45         <Name />

   46         <Contact />

   47         <Address />

   48     </Customer>

   49 </Customers>

   50 '

   51 

   52 /*

   53 OUTPUT:

   54 

   55 Msg 6965, Level 16, State 1, Line 13

   56 XML Validation: Invalid content. Expected element(s):Address where element 'Contact' was specified. Location: /:Customers[1]/:Customer[1]/:Contact[1]

   57 

   58 Here is the correct structure. Note that the XML value is EXACTLY in the same order as defined

   59 in the SCHEMA.

   60 */

   61 

   62 DECLARE @cust AS XML(CustomerSchema)

   63 SET @cust = '

   64 <Customers>

   65     <Customer CustomerNumber="A001">

   66         <Name />

   67         <Address />

   68         <Contact />

   69     </Customer>

   70 </Customers>

   71 '

Let us move to the final step and complete the schema. [Code]

    1 

    2 DROP XML SCHEMA COLLECTION CustomerSchema

    3 GO

    4 

    5 /*

    6     Now let us enhance the schema further and add all the

    7     sub elements that we need under "Contact", "Name" and "Address"

    8 */

    9 CREATE XML SCHEMA COLLECTION CustomerSchema AS '

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

   11   <element name="Customers">

   12     <complexType>

   13       <sequence>

   14         <element name="Customer" minOccurs="0">

   15           <complexType>

   16             <sequence>

   17               <element name="Name" minOccurs="1" maxOccurs="1" >

   18                 <complexType>

   19                   <sequence>

   20                     <element name="FirstName" type="string" />

   21                     <element name="MiddleName" type="string" />

   22                     <element name="LastName" type="string" />

   23                   </sequence>

   24                 </complexType>

   25               </element>

   26               <element name="Address" minOccurs="1" maxOccurs="1" >

   27               <complexType>

   28                 <sequence>

   29                   <element name="Street" type="string" />

   30                   <element name="City" type="string" />

   31                   <element name="State" type="string" />

   32                   <element name="Zip" type="string" />

   33                 </sequence>

   34               </complexType>

   35             </element>

   36             <element name="Contact" minOccurs="1" maxOccurs="1" >

   37               <complexType>

   38                 <sequence>

   39                   <element name="Phone" type="string" />

   40                   <element name="Fax" type="string" />

   41                   <element name="Email" type="string" />

   42                 </sequence>

   43               </complexType>

   44             </element>

   45             </sequence>

   46             <attribute name="CustomerNumber" type="string" use="required" />

   47           </complexType>

   48         </element>

   49       </sequence>

   50     </complexType>

   51   </element>

   52 </schema>

   53 '

   54 GO

   55 

   56 /*

   57 Let us test our SCHEMA and see if the SCHEMA validator accepts our value.

   58 */

   59 

   60 DECLARE @cust AS XML(CustomerSchema)

   61 SET @cust = '

   62 <Customers>

   63   <Customer CustomerNumber="A001">

   64     <Name>

   65       <FirstName>Jacob</FirstName>

   66       <MiddleName>V</MiddleName>

   67       <LastName>Sebastian</LastName>

   68     </Name>

   69     <Address>

   70       <Street>401, Time Square</Street>

   71       <City>Ahmedabad</City>

   72       <State>Gujarat</State>

   73       <Zip>380006</Zip>

   74     </Address>

   75     <Contact>

   76       <Phone>999 999 9999</Phone>

   77       <Fax>888 888 8888</Fax>

   78       <Email>jacob@dotnetquest.com</Email>

   79     </Contact>

   80   </Customer>

   81 </Customers>

   82 '

   83 

   84 /*

   85 CHEERS! The schema is ready!

   86 */

Well, we are done. We worked so hard. It is time to go for a coffee. When you are back, you can download the SCHEMA that we just created here.

Conclusions

This article does not provide a full view of XML schemas. It demonstrates the basic usage scenarios. The primary purpose of this article is to introduce the basics of SCHEMAS and make it familiar to the developers around who want to start working with TYPED XML.

Total article views: 7309 | Views in the last 30 days: 14
 
Related Articles
ARTICLE

Custom Sequence Numbering

This article shows how to implement low cost custom sequence numbering logic via a cunning use of th...

FORUM

custom sequence generator

custom sequence

FORUM

SQL Bulk Load - Nested Elements

Loading Nested Elements via SQLXMLBULKLOAD

FORUM

Different schemas / tables / procedures -> jump to dbo and back to custom schema

Access custom table from default schema when calling procedure from dbo and logged in with custom sc...

FORUM

Best code for generating sequence numbers

Sequence generation

Tags
schemas/dtds    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones