Introduction
This is the 8th installment of my XML Workshop which aims at explaining the details of working with XML in TSQL. You can find the the other articles in this series here. In the previous articles, we have seen how to read values from XML variables and columns, generate XML structures using FOR XML with AUTO, RAW, PATH and EXPLICIT.
The last two 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
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. [See sql1.sql and schema1.xml in the code download]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3 /*
4 A schema may look very complex if there are lots of elements,
5 validations and restrictions. Many of the schemas that you might
6 use in a production environment would be very long and complex.
7
8 To simplify the schema development and to make it easier to understand
9 and manage, you can define your own custom types. These types can be used
10 as children of a "complexType".
11
12 The following example is using the Schema that we defined in the previous
13 session. I have modified the previous schema, to demonstrate the usage of
14 a custom type. In this version, I have defined a type named "AgeType".
15 You can find the definition of the type at the bottom of the schema definition.
16 Note that I have defined an element which is of type "AgeType" in the schema
17 definition.
18
19 Use of custom "types" will make the schema development simpler and easier.
20 It helps you to re-use the validation rules defined, in multiple instances
21 of the type.
22 */
23 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
24 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
25 <xs:element name="Employee">
26 <xs:complexType>
27 <xs:sequence>
28 <xs:element name="FullName" type="xs:string" />
29 <xs:element name="Age" type="AgeType"
/>
30 <xs:element name="Nationality">
31 <xs:simpleType>
32 <xs:restriction base="xs:string">
33 <xs:enumeration value="Indian" />
34 <xs:enumeration value="British" />
35 <xs:enumeration value="American" />
36 </xs:restriction>
37 </xs:simpleType>
38 </xs:element>
39 <xs:element name="Gender">
40 <xs:simpleType>
41 <xs:restriction base="xs:string">
42 <xs:pattern value="male|female" />
43 </xs:restriction>
44 </xs:simpleType>
45 </xs:element>
46 <xs:element name="Salary">
47 <xs:simpleType>
48 <xs:restriction base="xs:decimal">
49 <xs:fractionDigits value="2" />
50 </xs:restriction>
51 </xs:simpleType>
52 </xs:element>
53 </xs:sequence>
54 <xs:attribute name="EmployeeNumber" use="required" >
55 <xs:simpleType>
56 <xs:restriction base="xs:integer">
57 <xs:totalDigits value="6" />
58 </xs:restriction>
59 </xs:simpleType>
60 </xs:attribute>
61 <xs:attribute name="LoginName" use="required">
62 <xs:simpleType>
63 <xs:restriction base="xs:string">
64 <xs:minLength value="6" />
65 <xs:maxLength value="8" />
66 <xs:pattern value="([a-z])*" />
67 </xs:restriction>
68 </xs:simpleType>
69 </xs:attribute>
70 </xs:complexType>
71 </xs:element>
72 <xs:simpleType name="AgeType">
73 <xs:restriction base="xs:integer">
74 <xs:minInclusive value="18" />
75 <xs:maxInclusive value="65" />
76 </xs:restriction>
77 </xs:simpleType>
78 </xs:schema>
79 '
80 GO
81
82 /*
83 The following is a correct XML value that validates with the above SCHEMA. The
84 XML value is the same that we used in the previous session. The only change
85 that we did in this version is a restructuring of the schema, which does not
86 affect the validation rules.
87 */
88 DECLARE @emp AS XML(EmployeeSchema)
89 SET @emp = '
90 <Employee EmployeeNumber="1001" LoginName="jacobs" >
91 <FullName>Jacob</FullName>
92 <Age>30</Age>
93 <Nationality>Indian</Nationality>
94 <Gender>male</Gender>
95 <Salary>10000.00</Salary>
96 </Employee>
97 '
Probably it might be a good idea to move all the elements that we have to Custom
Types. Let us do it. [ see sql2.sql and schema2.xml in the code download]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3 /*
4 Here is the final version of the schema. I have broken all the elements into various
5 custom types.
6 */
7 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
8 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
9 <xs:element name="Employee">
10 <xs:complexType>
11 <xs:sequence>
12 <xs:element name="FullName" type="xs:string" />
13 <xs:element name="Age" type="AgeType"
/>
14 <xs:element name="Nationality" type="NationalityType"
/>
15 <xs:element name="Gender" type="GenderType"
/>
16 <xs:element name="Salary" type="SalaryType"
/>
17 </xs:sequence>
18 <xs:attribute name="EmployeeNumber" use="required" type="EmployeeNumberType"
/>
19 <xs:attribute name="LoginName" use="required" type="LoginNameType"
/>
20 </xs:complexType>
21 </xs:element>
22 <xs:simpleType name="AgeType">
23 <xs:restriction base="xs:integer">
24 <xs:minInclusive value="18" />
25 <xs:maxInclusive value="65" />
26 </xs:restriction>
27 </xs:simpleType>
28 <xs:simpleType name="NationalityType">
29 <xs:restriction base="xs:string">
30 <xs:enumeration value="Indian" />
31 <xs:enumeration value="British" />
32 <xs:enumeration value="American" />
33 </xs:restriction>
34 </xs:simpleType>
35 <xs:simpleType name="GenderType">
36 <xs:restriction base="xs:string">
37 <xs:pattern value="male|female" />
38 </xs:restriction>
39 </xs:simpleType>
40 <xs:simpleType name="SalaryType">
41 <xs:restriction base="xs:decimal">
42 <xs:fractionDigits value="2" />
43 </xs:restriction>
44 </xs:simpleType>
45 <xs:simpleType name="EmployeeNumberType" >
46 <xs:restriction base="xs:integer">
47 <xs:totalDigits value="6" />
48 </xs:restriction>
49 </xs:simpleType>
50 <xs:simpleType name="LoginNameType">
51 <xs:restriction base="xs:string">
52 <xs:minLength value="6" />
53 <xs:maxLength value="8" />
54 <xs:pattern value="([a-z])*" />
55 </xs:restriction>
56 </xs:simpleType>
57 </xs:schema>
58 '
59 GO
60
61 /*
62 The following is a correct XML value that validates with the above SCHEMA. Note
63 that the changes in the schema structure did not affect the validation rules.
64 */
65 DECLARE @emp AS XML(EmployeeSchema)
66 SET @emp = '
67 <Employee EmployeeNumber="1001" LoginName="jacobs" >
68 <FullName>Jacob</FullName>
69 <Age>30</Age>
70 <Nationality>Indian</Nationality>
71 <Gender>male</Gender>
72 <Salary>10000.00</Salary>
73 </Employee>
74 '
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.[ See sql3.sql and schema3.xml in the code download]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3 /*
4 We have just seen how to define custom types. This example explains
5 how to create a derived type which inherits the rules defined in a parent
6 type.
7
8 The following SCHEMA defines a type "EmployeeType". "ProjectManagerType"
9 and "SupportEngineerType" inherit from "EmployeeType". Each of the
10 derived types has their own elements and can have own validation rules.
11 All the elements, properties and validation rules defined in the parent
12 type will be available to the derived type too.
13 */
14 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
15 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
16 <xs:element name="Team">
17 <xs:complexType>
18 <xs:sequence>
19 <xs:element name="ProjectManager" type="ProjectManagerType"
/>
20 <xs:element name="SupportEngineer" type="SupportEngineerType"
/>
21 </xs:sequence>
22 </xs:complexType>
23 </xs:element>
24
<xs:complexType name="EmployeeType">
<!-- This is our base class -->
25 <xs:sequence>
26 <xs:element name="FullName" type="xs:string"/>
27 <xs:element name="Department" type="xs:string"/>
28 <xs:element name="ReportsTo" type="xs:string"/>
29 </xs:sequence>
30 </xs:complexType>
31
<xs:complexType name="ProjectManagerType">
<!-- This is a derived class -->
32 <xs:complexContent>
33
<xs:extension base="EmployeeType">
<!-- Note the usage of "base" -->
34 <xs:sequence>
35 <xs:element name="ProjectName" type="xs:string"/>
36 <xs:element name="Technology" type="xs:string"/>
37 </xs:sequence>
38 </xs:extension>
39 </xs:complexContent>
40 </xs:complexType>
41 <xs:complexType name="SupportEngineerType"><!--
This is a derived class -->
42 <xs:complexContent>
43 <xs:extension base="EmployeeType"><!--
Note the usage of "base" -->
44 <xs:sequence>
45 <xs:element name="Phone" type="xs:string"/>
46 <xs:element name="Skype" type="xs:string"/>
47 <xs:element name="IM" type="xs:string"/>
48 </xs:sequence>
49 </xs:extension>
50 </xs:complexContent>
51 </xs:complexType>
52 </xs:schema>
53 '
54 GO
55
56 /*
57 Here is the XML that validates with the above schema. Note that we have
58 two elements: ProjectManager and SupportEngineer. Both of them have all
59 the elements defined in the EmployeeType: FullName, Department and
60 ReportsTo.
61 */
62
63 DECLARE @emp AS XML(EmployeeSchema)
64 SET @emp = '
65 <Team>
66 <ProjectManager>
67 <FullName>Robert V</FullName>
68 <Department>Software</Department>
69 <ReportsTo>CEO</ReportsTo>
70 <ProjectName>Virtual Earth</ProjectName>
71 <Technology>ASP.NET</Technology>
72 </ProjectManager>
73 <SupportEngineer>
74 <FullName>Michael M</FullName>
75 <Department>Tech Support</Department>
76 <ReportsTo>Robert V</ReportsTo>
77 <Phone>999-999-9999</Phone>
78 <Skype>whoami</Skype>
79 <IM>whoami@whereami.com</IM>
80 </SupportEngineer>
81 </Team>
82 '
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.