March 2009 - Posts
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.
<Customers>
<Customer CustomerNumber="A001">
<Name>
<FirstName>Jacob</FirstName>
<MiddleName>V</MiddleName>
<LastName>Sebastian</LastName>
</Name>
<Address>
<Street>302, Olive Arcade</Street>
<City>Ahmedabad</City>
<State>Gujarat</State>
<Zip>380006</Zip>
</Address>
<Contact>
<Phone>999 999 9999</Phone>
<Fax>888 888 8888</Fax>
<Email>jacob@dotnetquest.com</Email>
</Contact>
</Customer>
</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.
Let us first make the basic XML schema for our customer XML structure.
In the declaration below, we are creating an XML Schema Collection
with the name "CustomerSchema". The schema defines an element
named "Customers"
CREATE XML SCHEMA COLLECTION CustomerSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Customers">
</element>
</schema>'
GO
The "schema" element defines the schema. This is the root element of our schema.
"<element name="customers">" defines the root node of our XML structure.
Now let us create an XML variable which is bound to the "CustomerSchema"
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '<Customers></Customers>'
When you assign a value to a variable, which is bound to a schema, SQL Server
validates the value being assgned. For example, the following code will generate
an error, because the element "Customer" is not defined in the schema.
DECLARE @wrong AS XML(CustomerSchema)
SET @wrong = '<Customer></Customer>'
/*
OUTPUT:
Msg 6913, Level 16, State 1, Line 2
XML Validation: Declaration not found for element 'Customer'. Location: /*:Customer[1]
*/
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.
/*
Let us drop the previous SCHEMA and create the new version.
*/
DROP XML SCHEMA COLLECTION CustomerSchema
GO
/*
Let us enhance the schema so that the "Customers" element can contain
0 or more "Customer" elements.
*/
CREATE XML SCHEMA COLLECTION CustomerSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Customers">
<complexType>
<sequence>
<element name="Customer" minOccurs="0">
</element>
</sequence>
</complexType>
</element>
</schema>'
GO
Let us test the schema. "minOccurs=0" specifies that the element "Customer" is
not mandatory.
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '
<Customers>
<Customer></Customer>
</Customers>'
Let us move ahead. Each "Customer" element should contain a mandatory attribute named "CustomerNumber". Let us enhance the SCHEMA to support this.
DROP XML SCHEMA COLLECTION CustomerSchema
GO
Each "Customer" element should have an attribute named "CustomerNumber".
Let us enhance th schema again.
CREATE XML SCHEMA COLLECTION CustomerSchema AS
'<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Customers">
<complexType>
<sequence>
<element name="Customer" minOccurs="0">
<complexType>
<attribute name="CustomerNumber" type="string" use="required" />
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>'
use="required" specifies that the attribute "CustomerNumber" is mandatory. The following code
will generate a compile time error
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '
<Customers>
<Customer></Customer>
</Customers>'
Correct XML value is given below.
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '
<Customers>
<Customer CustomerNumber="A001"></Customer>
</Customers>'
At the next step, we will add the SCHEMA for the 3 child elements under the "Customer" element: Name, Address and Contact.
DROP XML SCHEMA COLLECTION CustomerSchema
GO
/*
Each "Customer" element should have "Name", "Address" and "Contact"
nodes.
*/
CREATE XML SCHEMA COLLECTION CustomerSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Customers">
<complexType>
<sequence>
<element name="Customer" minOccurs="0">
<complexType>
<sequence>
<element name="Name" minOccurs="1" maxOccurs="1" />
<element name="Address" minOccurs="1" maxOccurs="1" />
<element name="Contact" minOccurs="1" maxOccurs="1" />
</sequence>
<attribute name="CustomerNumber" type="string" use="required" />
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>'
GO
Note that I have set "minOccurs" and "maxOccurs" to 1 which specifies that
each element should be present in the XML data EXACTLY once.
We have 3 child elements under the "Customer" element. Note the usage of
"sequence". "sequence" specifies that the elements should occur exactly in the same
order. The following example will generate an error, because the "Address" element
is placed after the "Contact" element
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '
<Customers>
<Customer CustomerNumber="A001">
<Name />
<Contact />
<Address />
</Customer>
</Customers>'
/*
OUTPUT:
Msg 6965, Level 16, State 1, Line 13
XML Validation: Invalid content. Expected element(s):Address where element 'Contact' was specified. Location: /:Customers[1]/:Customer[1]/:Contact[1]
Here is the correct structure. Note that the XML value is EXACTLY in the same order as defined
in the SCHEMA.
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '
<Customers>
<Customer CustomerNumber="A001">
<Name />
<Address />
<Contact />
</Customer>
</Customers>'
Let us move to the final step and complete the schema.
DROP XML SCHEMA COLLECTION CustomerSchema
GO
/*
Now let us enhance the schema further and add all the
sub elements that we need under "Contact", "Name" and "Address"
*/
CREATE XML SCHEMA COLLECTION CustomerSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Customers">
<complexType>
<sequence>
<element name="Customer" minOccurs="0">
<complexType>
<sequence>
<element name="Name" minOccurs="1" maxOccurs="1" >
<complexType>
<sequence>
<element name="FirstName" type="string" />
<element name="MiddleName" type="string" />
<element name="LastName" type="string" />
</sequence>
</complexType>
</element>
<element name="Address" minOccurs="1" maxOccurs="1" >
<complexType>
<sequence>
<element name="Street" type="string" />
<element name="City" type="string" />
<element name="State" type="string" />
<element name="Zip" type="string" />
</sequence>
</complexType>
</element>
<element name="Contact" minOccurs="1" maxOccurs="1" >
<complexType>
<sequence>
<element name="Phone" type="string" />
<element name="Fax" type="string" />
<element name="Email" type="string" />
</sequence>
</complexType>
</element>
</sequence>
<attribute name="CustomerNumber" type="string" use="required" />
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>'
GO
Let us test our SCHEMA and see if the SCHEMA validator accepts our value.
DECLARE @cust AS XML(CustomerSchema)
SET @cust = '
<Customers>
<Customer CustomerNumber="A001">
<Name>
<FirstName>Jacob</FirstName>
<MiddleName>V</MiddleName>
<LastName>Sebastian</LastName>
</Name>
<Address>
<Street>401, Time Square</Street>
<City>Ahmedabad</City>
<State>Gujarat</State>
<Zip>380006</Zip>
</Address>
<Contact>
<Phone>999 999 9999</Phone>
<Fax>888 888 8888</Fax>
<Email>jacob@dotnetquest.com</Email>
</Contact>
</Customer>
</Customers>'
/*
CHEERS! The schema is ready!
*/
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.
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Customers">
<complexType>
<sequence>
<element name="Customer" minOccurs="0">
<complexType>
<sequence>
<element name="Name" minOccurs="1" maxOccurs="1" >
<complexType>
<sequence>
<element name="FirstName" type="string" />
<element name="MiddleName" type="string" />
<element name="LastName" type="string" />
</sequence>
</complexType>
</element>
<element name="Address" minOccurs="1" maxOccurs="1" >
<complexType>
<sequence>
<element name="Street" type="string" />
<element name="City" type="string" />
<element name="State" type="string" />
<element name="Zip" type="string" />
</sequence>
</complexType>
</element>
<element name="Contact" minOccurs="1" maxOccurs="1" >
<complexType>
<sequence>
<element name="Phone" type="string" />
<element name="Fax" type="string" />
<element name="Email" type="string" />
</sequence>
</complexType>
</element>
</sequence>
<attribute name="CustomerNumber" type="string" use="required" />
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>
Conclusions
This articles 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.
Introduction
There are times when we need to allow data with different XML structures. Let us take an example. Assume that we are working on a billing application and we need to store the payment details. Our application supports the following payment methods: Check, Cash and Credit Card. Based on the payment method, we will have to store additional information into the tables. If it is Check, then we need to store the Bank Name, Branch and Check Number. If the payment method is Cash then we need to store the Currency and the denomination. If the payment is made by Credit Card, then we need to store Card Type, Number, Expiry date etc.
The XML that our application should support are the following.
Cash Payment
<PaymentDetails>
<Type>Cash</Type>
<CashDetails>
<Currency>USD</Currency>
<Denomination>
<Hundreds>5</Hundreds>
<Tens>3</Tens>
<Ones>2</Ones>
</Denomination>
</CashDetails>
</PaymentDetails>
Payment By Check
<PaymentDetails>
<Type>Check</Type>
<CheckDetails>
<BankName>HSBC</BankName>
<Branch>NY City</Branch>
<CheckNumber>445908</CheckNumber>
</CheckDetails>
</PaymentDetails>
Payment By Credit Card
<PaymentDetails>
<Type>CreditCard</Type>
<CreditCardDetails>
<CardType>Visa</CardType>
<CardNumber>xxxx xxxx xxxx 9090</CardNumber>
<VerificationCode>896</VerificationCode>
<ExpiryDate>0911</ExpiryDate>
</CreditCardDetails>
</PaymentDetails>
How do we define a schema which supports all these different XML structures? Well, there are many ways to do it. The easiest way is to use the xsd:choice sequence indicator.
Step 1 - Define the Payment Method
Let us start defining the schema. The first step is to create the Payment Method enumeration.
<xsd:simpleType name="PaymentMethod">
<xsd:restriction base ="xsd:string">
<xsd:enumeration value="Cash"/>
<xsd:enumeration value="Check" />
<xsd:enumeration value="CreditCard" />
</xsd:restriction>
</xsd:simpleType>
Step 2 - Define Cash Details
Now let us define the schema for the cash payment details.
<xsd:complexType name="CashPayment">
<xsd:all>
<xsd:element name="Currency" type="xsd:string"/>
<xsd:element name="Denomination">
<xsd:complexType>
<xsd:all>
<xsd:element name="Hundreds" type="xsd:integer"/>
<xsd:element name="Tens" type="xsd:integer" />
<xsd:element name ="Ones" type="xsd:integer" />
</xsd:all>
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
Step 3 - Define Check Details
Here is the schema for the Check Details
<xsd:complexType name="CheckPayment">
<xsd:all>
<xsd:element name="BankName" type="xsd:string" />
<xsd:element name="Branch" type="xsd:string" />
<xsd:element name="CheckNumber" type="xsd:string" />
</xsd:all>
</xsd:complexType>
Step 4 - Define Credit Card Details
And finally, let us define the schema for Credit Card Details
<xsd:complexType name="CreditCardPayment">
<xsd:all>
<xsd:element name="CardType" type="xsd:string"/>
<xsd:element name="CardNumber" type="xsd:string" />
<xsd:element name="VerificationCode" type="xsd:string"/>
<xsd:element name="ExpiryDate" type="xsd:string" />
</xsd:all>
</xsd:complexType>
Step 5 - The final schema
Here is the complete schema.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="PaymentDetails">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Type" type="PaymentMethod" />
<xsd:choice>
<xsd:element name="CashDetails" type="CashPayment" />
<xsd:element name="CheckDetails" type="CheckPayment" />
<xsd:element name="CreditCardDetails" type="CreditCardPayment" />
</xsd:choice>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="PaymentMethod">
<xsd:restriction base ="xsd:string">
<xsd:enumeration value="Cash"/>
<xsd:enumeration value="Check" />
<xsd:enumeration value="CreditCard" />
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="CashPayment">
<xsd:all>
<xsd:element name="Currency" type="xsd:string"/>
<xsd:element name="Denomination">
<xsd:complexType>
<xsd:all>
<xsd:element name="Hundreds" type="xsd:integer"/>
<xsd:element name="Tens" type="xsd:integer" />
<xsd:element name ="Ones" type="xsd:integer" />
</xsd:all>
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
<xsd:complexType name="CreditCardPayment">
<xsd:all>
<xsd:element name="CardType" type="xsd:string"/>
<xsd:element name="CardNumber" type="xsd:string" />
<xsd:element name="VerificationCode" type="xsd:string"/>
<xsd:element name="ExpiryDate" type="xsd:string" />
</xsd:all>
</xsd:complexType>
<xsd:complexType name="CheckPayment">
<xsd:all>
<xsd:element name="BankName" type="xsd:string" />
<xsd:element name="Branch" type="xsd:string" />
<xsd:element name="CheckNumber" type="xsd:string" />
</xsd:all>
</xsd:complexType>
</xsd:schema>
Creating the Schema Collection
Let us create the schema collection now.
CREATE XML SCHEMA COLLECTION PaymentType
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="PaymentDetails">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Type" type="PaymentMethod" />
<xsd:choice>
<xsd:element name="CashDetails" type="CashPayment" />
<xsd:element name="CheckDetails" type="CheckPayment" />
<xsd:element name="CreditCardDetails" type="CreditCardPayment" />
</xsd:choice>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="PaymentMethod">
<xsd:restriction base ="xsd:string">
<xsd:enumeration value="Cash"/>
<xsd:enumeration value="Check" />
<xsd:enumeration value="CreditCard" />
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="CashPayment">
<xsd:all>
<xsd:element name="Currency" type="xsd:string"/>
<xsd:element name="Denomination">
<xsd:complexType>
<xsd:all>
<xsd:element name="Hundreds" type="xsd:integer"/>
<xsd:element name="Tens" type="xsd:integer" />
<xsd:element name ="Ones" type="xsd:integer" />
</xsd:all>
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
<xsd:complexType name="CreditCardPayment">
<xsd:all>
<xsd:element name="CardType" type="xsd:string"/>
<xsd:element name="CardNumber" type="xsd:string" />
<xsd:element name="VerificationCode" type="xsd:string"/>
<xsd:element name="ExpiryDate" type="xsd:string" />
</xsd:all>
</xsd:complexType>
<xsd:complexType name="CheckPayment">
<xsd:all>
<xsd:element name="BankName" type="xsd:string" />
<xsd:element name="Branch" type="xsd:string" />
<xsd:element name="CheckNumber" type="xsd:string" />
</xsd:all>
</xsd:complexType>
</xsd:schema>'
Testing the schema
We have created the schema that we needed. Now it is time to test it. Let us try to assign the different XML data that we discussed earlier and see if SQL Server 2005 validates them correctly.
DECLARE @x XML(PaymentType)
SET @x = '
<PaymentDetails>
<Type>CreditCard</Type>
<CreditCardDetails>
<CardType>Visa</CardType>
<CardNumber>xxxx xxxx xxxx 9090</CardNumber>
<VerificationCode>896</VerificationCode>
<ExpiryDate>0911</ExpiryDate>
</CreditCardDetails>
</PaymentDetails>
'
DECLARE @x XML(PaymentType)
SET @x = '
<PaymentDetails>
<Type>Cash</Type>
<CashDetails>
<Currency>USD</Currency>
<Denomination>
<Hundreds>5</Hundreds>
<Tens>3</Tens>
<Ones>2</Ones>
</Denomination>
</CashDetails>
</PaymentDetails>
'
DECLARE @x XML(PaymentType)
SET @x = '
<PaymentDetails>
<Type>Check</Type>
<CheckDetails>
<BankName>HSBC</BankName>
<Branch>NY City</Branch>
<CheckNumber>445908</CheckNumber>
</CheckDetails>
</PaymentDetails>
'
Conclusions
This session of focusses on explaining how to create variable content containers. It shows how to build a schema that supports 3 different XML structures. Based on the payment type, a different XML structure can be assigned to the XML variable or column bound to the schema that we defined
Introduction
XML is getting widely accepted as the format for data exchange. Wide adoption of architectures like SOA (Service Oriented Architecture) contributed to the growth of applications and services that talk to each other and exchange data in XML format. When applications exchange data, there will be strict rules that define the structure as well as the quality of data. If the data does not adhere to the rules, it may not be useful at all.
XSD helps you to define those rules which will validate the structure and quality of the data that your application sends or receives. Complex validation rules can be broken down to simple XSD definitions and can be stored to an XML SCHEMA COLLECTION. You could then ask SQL Server 2005 to validate the data based on the definitions in the XML SCHEMA COLLECTION.
In the previous few sessions, we have seen how to define rules which validate the structure of an XML document. We also learned how to define rules which validate the quality of data. Before we move out of XSD, I would like to present a few more XSD elements and attributes which define advanced characteristics of an XML document.
In the previous sessions, we have seen XML documents, composed of XML elements and attributes. We learned that if the element is a Simple Type it contains a value. If the element is a ComplexType then it will contain other elements and/or attributes. All the elements we have seen so far hold either a value or other child elements, but not both. In this session, we are going to have a look at Mixed types. Mixed Type is an element which will contain a value as well as other child elements.
Mixed Types
Mixed types are elements which contain value as well as other child elements. Mixed types usually contain long text data like the body of a letter, e-mail message or similar data where the data itself contains other specific pieces of information which needs to be extracted separately. The following example shows a case where a Mixed Type may be used.
SQLServerCentral sends an e-mail to the author when an article is scheduled for publication. Let us design a basic XML structure that holds the notification details. Here is a simple schema that holds the basic information.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ArticleNotification">
<xs:complexType>
<xs:sequence>
<xs:element name="Body" type="xs:string"/>
<xs:element name="Title" type="xs:string" />
<xs:element name="Url" type="xs:string" />
<xs:element name="ScheduledDate" type="xs:date"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
The above SCHEMA defines the following XML structure
<ArticleNotification>
<Body>The following article is scheduled for publication.</Body>
<Title>SQL Server XSD - Validating values with SCHEMA</Title>
<Url>http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx</Url>
<ScheduledDate>2007-01-01Z</ScheduledDate>
</ArticleNotification>
Mixed Mode gives us the freedom to alter the XML structure to be more descriptively. For example, the following XML structure is preferable over the previous one in this specific case.
<ArticleNotification>
Hi<Name>Jacob</Name>,
Please note that your article
<Title>SQL Server XSD - Validating values with SCHEMA</Title>,
posted at
<Url>http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx</Url>
is scheduled for publication
on <ScheduledDate>2007-01-01</ScheduledDate>.
</ArticleNotification>
If you would like to be a little more concise, you could go for a better structure as given below.
<ArticleNotification
Url="http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx">
Hi<Name>Jacob</Name>,
Please note that your article
<Title>SQL Server XSD - Validating values with SCHEMA</Title>
is scheduled for publication
on <ScheduledDate>2007-01-01</ScheduledDate>.
</ArticleNotification>
The XML structure I presented above is different from the structures that we have seen so far. In the above example, we have an element which has a value, an attribute and has other child elements. So, how do we define such elements in XSD? The answer is "by using Mixed Types". Let us write the XSD for the above XML structure.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ArticleNotification">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element name="Name" type="xs:string" />
<xs:element name="Title" type="xs:string"/>
<xs:element name="ScheduledDate" type="xs:date"/>
</xs:sequence>
<xs:attribute name="Url" type ="xs:string" />
</xs:complexType>
</xs:element>
</xs:schema>
Let us create the SCHEMA collection.
CREATE XML SCHEMA COLLECTION ArticleNotificationSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ArticleNotification">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element name="Name" type="xs:string" />
<xs:element name="Title" type="xs:string"/>
<xs:element name="ScheduledDate" type="xs:date"/>
</xs:sequence>
<xs:attribute name="Url" type ="xs:string" />
</xs:complexType>
</xs:element>
</xs:schema>
'
GO
Let us test the schema by assigning a value with the
desired structure to an XML variable which is bound to the
above schema.
DECLARE @art AS XML(ArticleNotificationSchema)
SET @art = '
<ArticleNotification Url="http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx">
Hi<Name>Jacob</Name>,
Please note that your article
<Title>SQL Server XSD - Validating values with SCHEMA</Title>
is scheduled for publication
on <ScheduledDate>2007-01-01Z</ScheduledDate>.
</ArticleNotification>
'
Note that I have added "Z" at the end of the date value. If we don't do that, SQL Server will generate an error message. SQL Server 2005's implementation of "xs:date" data type takes Time Zone information along with a date value. SQL Server 2005 will not accept a date value without the Time Zone information. The following XML is equally valid.
DECLARE @art AS XML(ArticleNotificationSchema)
SET @art = '
<ArticleNotification
Url="http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx">
Hi<Name>Jacob</Name>,
Please note that your article
<Title>SQL Server XSD - Validating values with SCHEMA</Title>
is scheduled for publication
on <ScheduledDate>2007-01-01+05:30</ScheduledDate>.
</ArticleNotification>
'
Note the value "+05:30" which indicates Indian Standard Time, which is GMT+05:30.
Let us try to read information from the XML variable that we just created. Reading some of the information from the XML variable might seem little tricky.
DECLARE @art AS XML(ArticleNotificationSchema)
SET @art = '
<ArticleNotification
Url="http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx">
Hi<Name>Jacob</Name>,
Please note that your article
<Title>SQL Server XSD - Validating values with SCHEMA</Title>
is scheduled for publication
on <ScheduledDate>2007-01-01Z</ScheduledDate>.
</ArticleNotification>
'
/*
Let us make sure that the value is stored correctly.
Try to retrieve the "Url" attribute from the XML.
*/
SELECT
x.a.value('(@Url)[1]', 'varchar(50)') AS Url
FROM @art.nodes('/ArticleNotification') x(a)
/*
OUTPUT:
Url
----------------------------------------------------------------------
http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx
(1 row(s) affected)
*/
/*
Reading "name" and "title" is pretty simple.
*/
SELECT
x.a.value('(Name)[1]', 'varchar(10)') AS [Name],
x.a.value('(Title)[1]','varchar(50)') AS Title,
x.a.value('(ScheduledDate)[1]','datetime') AS ScheduledDate
FROM @art.nodes('/ArticleNotification') x(a)
/*
OUTPUT:
Name Title ScheduledDate
------ ------------------------------------------------ -------------------
Jacob SQL Server XSD - Validating values with SCHEMA 2007-01-01 00:00:00
(1 row(s) affected)
*/
Good so far. Now how do we read the content of the ArticleNotification element? How do we retrieve the text from a mixed element? Here is the TSQL query which retrieves the whole text of the mixed element.
DECLARE @art AS XML(ArticleNotificationSchema)
SET @art = '
<ArticleNotification
Url="http://beyondrelational.com/content/Validating-values-with-SCHEMA.aspx">
Hi <Name>Jacob</Name>,
Please note that your article
<Title>SQL Server XSD - Validating values with SCHEMA</Title>
is scheduled for publication
on <ScheduledDate>2007-01-01Z</ScheduledDate>.
</ArticleNotification>
'
/*
Read the entire text from the "ArticleNotification" element.
*/
SELECT
x.a.value('(data(/))[1]', 'varchar(max)') AS Summary
FROM @art.nodes('/ArticleNotification') x(a)
/*
OUTPUT:
Summary
---------------------------------------------------
Hi Jacob,
Please note that your article SQL Server XSD - Validating values with SCHEMA is scheduled for publication
on 2007-01-01Z.
(1 row(s) affected)
*/
Conclusions
This article explains XSD Mixed types. Mixed types represents XML elements that can store a text value as well as child elements and/or attributes.
Introduction
The previous articles were focusing on TYPED XML and had been explaining how to generate an XML schema that can be used to validate the XML data. We have seen how to validate the existence of elements and attributes, how to validate data types and how to apply restrictions on the actual data.
XML Schemas usually are very complex and confusing. The more validation and rules you apply, the more complex the schema will become. This article will present a couple of approaches that will help you to make your schemas less complex and easier to read and understand.
Creating Custom Types
One of the ways to simplify your schema is by using Custom Types. If an element is having very complex validation rules, it will make more sense to move it to a Custom Type and then add an element of the custom type in the schema definition. Custom types are more useful when you need to reuse the validations on different elements. For example, the validation for Phone number can be used for other elements like Phone-Home, Phone-Office, Fax etc.
Let us look at an example which uses a custom type. I am updating the XML schema that we used in the previous example. The new version of the XML schema has a Custom Type: AgeType. Note that the validation rules do not change. The XML structure expected by the schema is the same. What we changed is only the way we defined it.
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
A schema may look very complex if there are lots of elements,
validations and restrictions. Many of the schemas that you might
use in a production environment would be very long and complex.
To simplify the schema development and to make it easier to understand
and manage, you can define your own custom types. These types can be used
as children of a "complexType".
The following example is using the Schema that we defined in the previous
session. I have modified the previous schema, to demonstrate the usage of
a custom type. In this version, I have defined a type named "AgeType".
You can find the definition of the type at the bottom of the schema definition.
Note that I have defined an element which is of type "AgeType" in the schema
definition.
Use of custom "types" will make the schema development simpler and easier.
It helps you to re-use the validation rules defined, in multiple instances
of the type.
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="FullName" type="xs:string" />
<xs:element name="Age" type="AgeType" />
<xs:element name="Nationality">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Indian" />
<xs:enumeration value="British" />
<xs:enumeration value="American" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Gender">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:pattern value="male|female" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Salary">
<xs:simpleType>
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
<xs:attribute name="EmployeeNumber" use="required" >
<xs:simpleType>
<xs:restriction base="xs:integer">
<xs:totalDigits value="6" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="LoginName" use="required">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="6" />
<xs:maxLength value="8" />
<xs:pattern value="([a-z])*" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
<xs:simpleType name="AgeType">
<xs:restriction base="xs:integer">
<xs:minInclusive value="18" />
<xs:maxInclusive value="65" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
'
GO
/*
The following is a correct XML value that validates with the above SCHEMA. The
XML value is the same that we used in the previous session. The only change
that we did in this version is a restructuring of the schema, which does not
affect the validation rules.
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee EmployeeNumber="1001" LoginName="jacobs" >
<FullName>Jacob</FullName>
<Age>30</Age>
<Nationality>Indian</Nationality>
<Gender>male</Gender>
<Salary>10000.00</Salary>
</Employee>
'
Probably it might be a good idea to move all the elements that we have to Custom Types. Let us do it.
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
Here is the final version of the schema. I have broken all the elements into various
custom types.
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="FullName" type="xs:string" />
<xs:element name="Age" type="AgeType" />
<xs:element name="Nationality" type="NationalityType" />
<xs:element name="Gender" type="GenderType" />
<xs:element name="Salary" type="SalaryType" />
</xs:sequence>
<xs:attribute name="EmployeeNumber" use="required" type="EmployeeNumberType" />
<xs:attribute name="LoginName" use="required" type="LoginNameType" />
</xs:complexType>
</xs:element>
<xs:simpleType name="AgeType">
<xs:restriction base="xs:integer">
<xs:minInclusive value="18" />
<xs:maxInclusive value="65" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NationalityType">
<xs:restriction base="xs:string">
<xs:enumeration value="Indian" />
<xs:enumeration value="British" />
<xs:enumeration value="American" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="GenderType">
<xs:restriction base="xs:string">
<xs:pattern value="male|female" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="SalaryType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="EmployeeNumberType" >
<xs:restriction base="xs:integer">
<xs:totalDigits value="6" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="LoginNameType">
<xs:restriction base="xs:string">
<xs:minLength value="6" />
<xs:maxLength value="8" />
<xs:pattern value="([a-z])*" />
</xs:restriction>
</xs:simpleType>
</xs:schema>
'
GO
/*
The following is a correct XML value that validates with the above SCHEMA. Note
that the changes in the schema structure did not affect the validation rules.
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee EmployeeNumber="1001" LoginName="jacobs" >
<FullName>Jacob</FullName>
<Age>30</Age>
<Nationality>Indian</Nationality>
<Gender>male</Gender>
<Salary>10000.00</Salary>
</Employee>
'
Inheriting from a Custom Type
Anyone who understands OOPS would also understand the power and freedom that inheritance can give to a class. It is the same with Custom Types in a SCHEMA definition. It supports inheritance. You can derive other custom types from a parent type. Use of inheritance can make your SCHEMA definition even simpler and easier to understand. I have put up a quick example that shows how to inherit from a Custom Type within a Schema definition.
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
We have just seen how to define custom types. This example explains
how to create a derived type which inherits the rules defined in a parent
type.
The following SCHEMA defines a type "EmployeeType". "ProjectManagerType"
and "SupportEngineerType" inherit from "EmployeeType". Each of the
derived types has their own elements and can have own validation rules.
All the elements, properties and validation rules defined in the parent
type will be available to the derived type too.
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Team">
<xs:complexType>
<xs:sequence>
<xs:element name="ProjectManager" type="ProjectManagerType" />
<xs:element name="SupportEngineer" type="SupportEngineerType" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="EmployeeType">
<xs:sequence>
<xs:element name="FullName" type="xs:string"/>
<xs:element name="Department" type="xs:string"/>
<xs:element name="ReportsTo" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ProjectManagerType">
<xs:complexContent>
<xs:extension base="EmployeeType">
<xs:sequence>
<xs:element name="ProjectName" type="xs:string"/>
<xs:element name="Technology" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="SupportEngineerType">
<xs:complexContent>
<xs:extension base="EmployeeType">
<xs:sequence>
<xs:element name="Phone" type="xs:string"/>
<xs:element name="Skype" type="xs:string"/>
<xs:element name="IM" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:schema>
'
GO
/*
Here is the XML that validates with the above schema. Note that we have
two elements: ProjectManager and SupportEngineer. Both of them have all
the elements defined in the EmployeeType: FullName, Department and
ReportsTo.
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Team>
<ProjectManager>
<FullName>Robert V</FullName>
<Department>Software</Department>
<ReportsTo>CEO</ReportsTo>
<ProjectName>Virtual Earth</ProjectName>
<Technology>ASP.NET</Technology>
</ProjectManager>
<SupportEngineer>
<FullName>Michael M</FullName>
<Department>Tech Support</Department>
<ReportsTo>Robert V</ReportsTo>
<Phone>999-999-9999</Phone>
<Skype>whoami</Skype>
<IM>whoami@whereami.com</IM>
</SupportEngineer>
</Team>
'
Conclusions
This article explains how to create custom types and create types that inherit from other types. Usage of custom types can make the XML schema less complex and easier to understand and manage.
Introduction
In the previous article we have seen how to work with TYPED XML and XML SCHEMAs. By using a TYPED XML which is bound to an XML SCHEMA, you can make sure that the value being stored to your XML column or variable is EXACTLY as per the format that you want them to be. SQL Server will perform this validation on your behalf.
Most of the times, you will be using an XML column or variable, when you expect an EXTERNAL application to supply a piece of information to your App. So there is a certain XML structure that the EXTERNAL application need to follow. When it sends the data to your application, it has to make sure that the data is as per the required format. Everything will work well, as long as you get the correct data. But what if the EXTERNAL application starts sending you data in an incorrect format? You certainly need to validate the data and reject it, because it does not abide with the required format. But how do you validate it? Well, SQL Server will do the validation for you, if you are using a TYPED XML column or variable. You can specify the structure of the data that you expect in an XML schema and bind the XML variable or column to that SCHEMA. The rest will be done by SQL Server.
In the previous workshop we have seen how to do this kind of validation. But the validation that we did previously were only on the structure of the XML, not on the value (actual data). For example, we have seen how to make sure that the data that we receive has the required ELEMENTS and ATTRIBUTES. However, that is not enough. We need to make sure that the value stored in the ELEMENTS and ATTRIBUTES are correct too. If the age element has a value of 500 or -3, then what sense does it make? Or it could be that your application is expecting a 6 digit employee number but you receive an employee number that is 9 characters long alpha-numeric value. Huh, we really need a way to validate the values!
In this session, we will look into a few examples that explain how to validate the values of ELEMENTS and ATTRIBUTES by using an XML SCHEMA.
Data Type Validation
One of the most important validations that we need to make is that of the data types. BirthDate should be a valid date value. Salary should be a Number and Age cannot have decimals. Let us now write a schema that performs data type validations.
Let us create a SCHEMA for storing Employee Information. The Schema is pretty simple and the elements are self explanatory. Please note that each element is defined with a specific data type. While storing the value to the XML variable or column which is bound to this SCHEMA, SQL Server will validate the elements as per the given SCHEMA.
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Employee">
<complexType>
<sequence>
<element name="FullName" type="string" />
<element name="Salary" type="decimal" />
<element name="Age" type="integer" />
<element name="Married" type="boolean" />
<element name="BirthDate" type ="date" />
<element name="ReportingTime" type="time" />
</sequence>
</complexType>
</element>
</schema>
'
GO
If there is no XML schema, the external application might be sending data in the
following format, which looks correct at first glance. But, since we have a strict
XML Schema, the following XML will not be accepted.
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>$10,000</Salary>
<Age>Thirty</Age>
<Married>Yes</Married>
<BirthDate>1975-03-14</BirthDate>
<ReportingTime>10:00 AM</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 4
XML Validation: Invalid simple type value: '$10,000'. Location: /:Employee[1]/:Salary[1]
"Salary" is defined as "decimal" and it cannot take the "$" sign. Let us correct that.
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10,000</Salary>
<Age>Thirty</Age>
<Married>Yes</Married>
<BirthDate>1975-03-14</BirthDate>
<ReportingTime>10:00 AM</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '10,000'. Location: /:Employee[1]/:Salary[1]
Hmmmm..Doesn't it accept comma (,) too? OK, no arguments. Let us correct it.
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>Thirty</Age>
<Married>Yes</Married>
<BirthDate>1975-03-14</BirthDate>
<ReportingTime>10:00 AM</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: 'Thirty'. Location: /:Employee[1]/:Age[1]
The SCHEMA defines "Age" as a numeric value. Hence it should be a number. Let us
correct it too.
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>Yes</Married>
<BirthDate>1975-03-14</BirthDate>
<ReportingTime>10:00 AM</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: 'Yes'. Location: /:Employee[1]/:Married[1]
"Married" is defined as "Boolean". It cannot take "Yes"/"No" or "True"/"False". The
"Boolean" field can store only "1" or "0"
*/
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14</BirthDate>
<ReportingTime>10:00 AM</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '1975-03-14'. Location: /:Employee[1]/:BirthDate[1]
Why does SQL Server reject the date value? Well, the XSD implementation of "date" data type in
SQL Server expects Time Zone information too. In the following example, I have put GMT + 5:30
as my Time Zone.
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime>10:00 AM</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '10:00 AM'. Location: /:Employee[1]/:ReportingTime[1]
Well, what is the problem here? Probably, it did not like "AM". Let us change that.
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime>10:00:00</ReportingTime>
</Employee>
'
/*
OUTPUT:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '10:00:00'. Location: /:Employee[1]/:ReportingTime[1]
SQL Server's implementation of "time" data type expects the Time Zone information too. The following
is the correct XML which validates with our EmployeeSchema.
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime>10:00:00+05:30</ReportingTime>
</Employee>
'
Using "fixed" attribute with elements
Some times we would come across situations where we always expect a fixed value. For example, the gender of the employee should be male or Marital Status should be Married. The following example shows how to do this type of validations by using the fixed attribute
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
Note the usage of attribute "fixed" which restricts the values to be always "1"
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Employee">
<complexType>
<sequence>
<element name="FullName" type="string" />
<element name="Salary" type="decimal" />
<element name="Age" type="integer" />
<element name="Married" type="boolean" fixed="1"/>
<element name="BirthDate" type ="date" />
<element name="ReportingTime" type="time" />
</sequence>
</complexType>
</element>
</schema>
'
GO
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>0</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime>10:00:00+05:30</ReportingTime>
</Employee>
'
/*
Msg 6921, Level 16, State 1, Line 3
XML Validation: Element or attribute 'Married' was defined as fixed, the element value has to be
equal to value of 'fixed' attribute specified in definition. Location: /:Employee[1]/:Married[1]
*/
Validating elements with "default" attribute
The default attribute allows to specify a default value for the given element or attribute. When default is specified the element or attribute becomes optional. The following example demonstrates the usage of default attribute.
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
This example adds the "default" attribute to specify a certain value
if the element is not present.
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Employee">
<complexType>
<sequence>
<element name="FullName" type="string" />
<element name="Salary" type="decimal" />
<element name="Age" type="integer" />
<element name="Married" type="boolean" fixed="1"/>
<element name="BirthDate" type ="date" />
<element name="ReportingTime" type="time" default="10:00:00+05:30"/>
</sequence>
</complexType>
</element>
</schema>
'
GO
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime />
</Employee>
'
/*
This XML validates correctly because there is a default value specified in the
SCHEMA for the element <Reportingtime>. If the default value is removed from
the SCHEMA definition, SQL Server will generate the following error while validating
the above XML value.
Msg 6926, Level 16, State 1, Line 3
XML Validation: Invalid simple type value: ''. Location: /:Employee[1]/:ReportingTime[1]
*/
Validating attributes with "use"
Attributes are optional by default. To make an attribute mandatory, you need to add the use attribute and set the value to required. The following example demonstrates it.
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
This version of the SCHEMA defines a new attribute in the "Employee" node.
The attribute is defined as "required". By default an attribute is "optional".
To make it mandatory, you need to use the "use" attribute and specify the value
"required"
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Employee">
<complexType>
<sequence>
<element name="FullName" type="string" />
<element name="Salary" type="decimal" />
<element name="Age" type="integer" />
<element name="Married" type="boolean" fixed="1"/>
<element name="BirthDate" type ="date" />
<element name="ReportingTime" type="time" default="10:00:00+05:30"/>
</sequence>
<attribute name="EmployeeNumber" type="integer" use="required" />
</complexType>
</element>
</schema>
'
GO
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee>
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime />
</Employee>
'
The above code will generate the following error because the attribute
"EmployeeNumber" is a required attribute.
Msg 6906, Level 16, State 1, Line 3
XML Validation: Required attribute 'EmployeeNumber' is missing. Location: /:Employee[1]
If the "required" modifier is not specified, then "optional" is assumed. Hence the above
XML will validate correctly.
The correct XML for the above SCHEMA is given below.
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee EmployeeNumber="1001">
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime />
</Employee>
'
/*
The above XML validates correctly. You will not be able to use an alpha-numeric
employee number, because the SCHEMA defines it to be an INTEGER.
*/
Validating attributes with "fixed" and "default"
The following example demonstrates how to write a schema which validates attributes with fixed and default.
DROP XML SCHEMA COLLECTION EmployeeSchema
GO
/*
This version of the SCHEMA demonstrates how to implement "fixed" and "default" restrictions
with attributes.
*/
CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Employee">
<complexType>
<sequence>
<element name="FullName" type="string" />
<element name="Salary" type="decimal" />
<element name="Age" type="integer" />
<element name="Married" type="boolean" fixed="1"/>
<element name="BirthDate" type ="date" />
<element name="ReportingTime" type="time" default="10:00:00+05:30"/>
</sequence>
<attribute name="EmployeeNumber" type="integer" use="required" />
<attribute name="Language" type="string" fixed="EN" />
<attribute name="Nationality" type="string" default="Alien" />
</complexType>
</element>
</schema>
'
GO
DECLARE @emp AS XML(EmployeeSchema)
SET @emp = '
<Employee EmployeeNumber="1001" Language="EN" >
<FullName>Jacob</FullName>
<Salary>10000</Salary>
<Age>30</Age>
<Married>1</Married>
<BirthDate>1975-03-14+05:30</BirthDate>
<ReportingTime />
</Employee>
'
/*
Language should always be "EN" because a fixed value is specified in the SCHEMA.
If you enter any other language, you will get the following error.
Msg 6921, Level 16, State 2, Line 2
XML Validation: Element or attribute 'Language' was defined as fixed, the element value has to be equal
to value of 'fixed' attribute specified in definition. Location: /:Employee[1]/@:Language
"Nationality" can be ignored. If you ignore this attribute, the default value of "Alien" is assumed.
*/
Conclusions
This article presented a few examples that shows how to do data type validations using an XML Schema. It also demonstrates the usages of XSD attributes "fixed", "default" and "use".
I was not very fortunate with my blogging platforms since the last few years. I started with blogger in 2006 and then thought it is a better idea to host blogs on my own web site. I used to blog about some SQL Server stuff and some .NET stuff those days. I registered the domain http://www.dotnetquest.com and installed a free blog application and started my blogs there.
It was going well, but soon I started getting problems with the bandwidth. My hosting provider was not able to increase the bandwidth and my pages started getting an error in the second half of every month. After a few rounds of conversation with the hosting provider I decided to move away.
I came back to blogger.com. My blogs were hosted at http://jacobsebastian.blogspot.com. By this time I stopped blogging on .NET completely and started focusing entirely on SQL Server. Over period of time, I started blogging more on the XML side of SQL Server and that is the time when I know that I can host blogger.com blogs on a custom domain. I then configured my blogs at http://www.sqlserverandxml.com.
I am pretty sure many people liked my XML blogs there. I had a number of tutorials on XML related topics. Very soon I ended up with a large number of links pointing to various blog posts at my site. Then came the trouble again.
In the first week of January 2009, many people contacted me, telling that my web pages are not opening up. I was taking a short family vacation at that time and did not have access to Internet for over 2 weeks. Later on, I realized that my domain name expired in the last week of December. I tried to renew it, but it was too late. Some one else had already taken the domain name.
It was really hard time. Many people looking for my posts end up with errors. I pulled my blog back to http://jacobsebastian.blogspot.com. Later on, I found that the domain name is put on sale and I tried to buy it back from the people who own it. However, it looks like they want to keep it on auction for ever, or perhaps they want to wait for the highest bid or whatever, nothing worked out.
The only option I was left with was to find a new domain and put my blog there. I spent quite some time finding a domain name that satisfies me, but did not succeed. The best name I could find was www.sqlserver.me. Though I was not happy with the extension "me", I decided to go ahead with that and configured http://jacobsebastian.blogspot.com there. http://blog.sqlserver.me became my blog address. But I was not happy.
My search for a domain name that fully satisfies me, continued. A few days back, I found www.sqlserverstuff.com and registered it. It looked little better, but still I was not very happy with that.
Finally, I came across www.beyondrelational.com and I liked the name. It is closely related to the stuff that I am involved into. Most of the stuff I blog are related to the "Beyond Relational" side of SQL Server. I decided to go ahead with that. I soon configured my blogger blog to run on http://blog.beyondrelational.com.
I wanted to build www.beyondrelational.com as a source where people would find information on all beyond relational aspects of SQL Server. After some research, I realized that Community Server is a good choice for such a site. I just got Community Server installed and this is my first blog.
Yes, I am very much excited to move to www.beyondrelational.com. I am not quite sure how I will move my existing blogs from blogger.com, but I think there must be a smooth way. I am concerned about the existing links and need to find a way to do a smooth redirection to the new blog site.
Welcome to my new blog!
In XQuery Lab 18, we saw how to delete an element from an XML variable or column. Let us now see, how to delete more than one element from an XML column or variable.
Here is the sample XML that we will examine in this lab.
<Employees>
<Employee>
<Name>Jacob</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Steve</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Bob</Name>
<Team>ASP.NET</Team>
</Employee>
</Employees>
Let us write a query to delete the "Team" element from all employee nodes. Here is the query.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>
<Name>Jacob</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Steve</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Bob</Name>
<Team>ASP.NET</Team>
</Employee>
</Employees>'
SET @x.modify('
delete (/Employees/Employee/Team),
')
SELECT @x
/*
Output:
<Employees>
<Employee>
<Name>Jacob</Name>
</Employee>
<Employee>
<Name>Steve</Name>
</Employee>
<Employee>
<Name>Bob</Name>
</Employee>
</Employees>
*/
The following example shows how to apply a filter in the delete operation. The example given below deletes all "Team" elements where the value is "SQL Server".
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>
<Name>Jacob</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Steve</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Bob</Name>
<Team>ASP.NET</Team>
</Employee>
</Employees>'
SET @x.modify('
delete (/Employees/Employee/Team[. = "SQL Server"]),
')
SELECT @x
/*
Output:
<Employees>
<Employee>
<Name>Jacob</Name>
</Employee>
<Employee>
<Name>Steve</Name>
</Employee>
<Employee>
<Name>Bob</Name>
<Team>ASP.NET</Team>
</Employee>
</Employees>
*/
The above example operates an XML variable. Now let us see how to perform the same operation on an XML column.
DECLARE @t TABLE (data XML)
INSERT INTO @t(data) SELECT '
<Employees>
<Employee>
<Name>Jacob</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Steve</Name>
<Team>SQL Server</Team>
</Employee>
<Employee>
<Name>Bob</Name>
<Team>ASP.NET</Team>
</Employee>
</Employees>'
UPDATE @t
SET data.modify('
delete (/Employees/Employee/Team[. = "SQL Server"]),
')
SELECT * FROM @t
/*
Output:
<Employees>
<Employee>
<Name>Jacob</Name>
</Employee>
<Employee>
<Name>Steve</Name>
</Employee>
<Employee>
<Name>Bob</Name>
<Team>ASP.NET</Team>
</Employee>
</Employees>
*/