Introduction
In the previous workshop 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. [code] [schema]
1 /*
2 Let us create a SCHEMA for storing Employee Information. The Schema
3 is pretty simple and the elements are self explanatory. Please note that
4 each element is defined with a specific data type. While storing the value
5 to the XML variable or column which is bound to this SCHEMA, SQL Server will
6 validate the elements as per the given SCHEMA.
7 */
8 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
9 <schema xmlns="http://www.w3.org/2001/XMLSchema">
10 <element name="Employee">
11 <complexType>
12 <sequence>
13 <element name="FullName" type="string" />
14 <element name="Salary" type="decimal" />
15 <element name="Age" type="integer" />
16 <element name="Married" type="boolean" />
17 <element name="BirthDate" type ="date" />
18 <element name="ReportingTime" type="time" />
19 </sequence>
20 </complexType>
21 </element>
22 </schema>
23 '
24 GO
25
26 /*
27 If there is no XML schema, the external application might be sending data in the
28 following format, which looks correct at first glance. But, since we have a strict
29 XML Schema, the following XML will not be accepted.
30 */
31
32 DECLARE @emp AS XML(EmployeeSchema)
33 SET @emp = '
34 <Employee>
35 <FullName>Jacob</FullName>
36 <Salary>$10,000</Salary>
37 <Age>Thirty</Age>
38 <Married>Yes</Married>
39 <BirthDate>1975-03-14</BirthDate>
40 <ReportingTime>10:00 AM</ReportingTime>
41 </Employee>
42 '
43
44 /*
45 OUTPUT:
46
47 Msg 6926, Level 16, State 1, Line 4
48 XML Validation: Invalid simple type value: '$10,000'. Location: /:Employee[1]/:Salary[1]
49
50 "Salary" is defined as "decimal" and it cannot take the "$" sign. Let us correct that.
51 */
52
53 DECLARE @emp AS XML(EmployeeSchema)
54 SET @emp = '
55 <Employee>
56 <FullName>Jacob</FullName>
57 <Salary>10,000</Salary>
58 <Age>Thirty</Age>
59 <Married>Yes</Married>
60 <BirthDate>1975-03-14</BirthDate>
61 <ReportingTime>10:00 AM</ReportingTime>
62 </Employee>
63 '
64
65 /*
66 OUTPUT:
67
68 Msg 6926, Level 16, State 1, Line 2
69 XML Validation: Invalid simple type value: '10,000'. Location: /:Employee[1]/:Salary[1]
70
71 Hmmmm..Doesn't it accept comma (,) too? OK, no arguments. Let us correct it.
72 */
73
74 DECLARE @emp AS XML(EmployeeSchema)
75 SET @emp = '
76 <Employee>
77 <FullName>Jacob</FullName>
78 <Salary>10000</Salary>
79 <Age>Thirty</Age>
80 <Married>Yes</Married>
81 <BirthDate>1975-03-14</BirthDate>
82 <ReportingTime>10:00 AM</ReportingTime>
83 </Employee>
84 '
85
86 /*
87 OUTPUT:
88
89 Msg 6926, Level 16, State 1, Line 2
90 XML Validation: Invalid simple type value: 'Thirty'. Location: /:Employee[1]/:Age[1]
91
92 The SCHEMA defines "Age" as a numeric value. Hence it should be a number. Let us
93 correct it too.
94 */
95
96 DECLARE @emp AS XML(EmployeeSchema)
97 SET @emp = '
98 <Employee>
99 <FullName>Jacob</FullName>
100 <Salary>10000</Salary>
101 <Age>30</Age>
102 <Married>Yes</Married>
103 <BirthDate>1975-03-14</BirthDate>
104 <ReportingTime>10:00 AM</ReportingTime>
105 </Employee>
106 '
107 /*
108 OUTPUT:
109
110 Msg 6926, Level 16, State 1, Line 2
111 XML Validation: Invalid simple type value: 'Yes'. Location: /:Employee[1]/:Married[1]
112
113 "Married" is defined as "Boolean". It cannot take "Yes"/"No" or "True"/"False". The
114 "Boolean" field can store only "1" or "0"
115 */
116
117 DECLARE @emp AS XML(EmployeeSchema)
118 SET @emp = '
119 <Employee>
120 <FullName>Jacob</FullName>
121 <Salary>10000</Salary>
122 <Age>30</Age>
123 <Married>1</Married>
124 <BirthDate>1975-03-14</BirthDate>
125 <ReportingTime>10:00 AM</ReportingTime>
126 </Employee>
127 '
128
129 /*
130 OUTPUT:
131
132 Msg 6926, Level 16, State 1, Line 2
133 XML Validation: Invalid simple type value: '1975-03-14'. Location: /:Employee[1]/:BirthDate[1]
134
135 Why does SQL Server reject the date value? Well, the XSD implementation of "date" data type in
136 SQL Server expects Time Zone information too. In the following example, I have put GMT + 5:30
137 as my Time Zone.
138 */
139
140 DECLARE @emp AS XML(EmployeeSchema)
141 SET @emp = '
142 <Employee>
143 <FullName>Jacob</FullName>
144 <Salary>10000</Salary>
145 <Age>30</Age>
146 <Married>1</Married>
147 <BirthDate>1975-03-14+05:30</BirthDate>
148 <ReportingTime>10:00 AM</ReportingTime>
149 </Employee>
150 '
151 /*
152 OUTPUT:
153 Msg 6926, Level 16, State 1, Line 2
154 XML Validation: Invalid simple type value: '10:00 AM'. Location: /:Employee[1]/:ReportingTime[1]
155
156 Well, what is the problem here? Probably, it did not like "AM". Let us change that.
157 */
158
159 DECLARE @emp AS XML(EmployeeSchema)
160 SET @emp = '
161 <Employee>
162 <FullName>Jacob</FullName>
163 <Salary>10000</Salary>
164 <Age>30</Age>
165 <Married>1</Married>
166 <BirthDate>1975-03-14+05:30</BirthDate>
167 <ReportingTime>10:00:00</ReportingTime>
168 </Employee>
169 '
170 /*
171 OUTPUT:
172
173 Msg 6926, Level 16, State 1, Line 2
174 XML Validation: Invalid simple type value: '10:00:00'. Location: /:Employee[1]/:ReportingTime[1]
175
176 SQL Server's implementation of "time" data type expects the Time Zone information too. The following
177 is the correct XML which validates with our EmployeeSchema.
178 */
179
180 DECLARE @emp AS XML(EmployeeSchema)
181 SET @emp = '
182 <Employee>
183 <FullName>Jacob</FullName>
184 <Salary>10000</Salary>
185 <Age>30</Age>
186 <Married>1</Married>
187 <BirthDate>1975-03-14+05:30</BirthDate>
188 <ReportingTime>10:00:00+05:30</ReportingTime>
189 </Employee>
190 '
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. [code] [schema]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3
4 /*
5 Note the usage of attribute "fixed" which restricts the values to be always "1"
6 */
7
8 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
9 <schema xmlns="http://www.w3.org/2001/XMLSchema">
10 <element name="Employee">
11 <complexType>
12 <sequence>
13 <element name="FullName" type="string" />
14 <element name="Salary" type="decimal" />
15 <element name="Age" type="integer" />
16 <element name="Married" type="boolean" fixed="1"/>
17 <element name="BirthDate" type ="date" />
18 <element name="ReportingTime" type="time" />
19 </sequence>
20 </complexType>
21 </element>
22 </schema>
23 '
24 GO
25
26 DECLARE @emp AS XML(EmployeeSchema)
27 SET @emp = '
28 <Employee>
29 <FullName>Jacob</FullName>
30 <Salary>10000</Salary>
31 <Age>30</Age>
32 <Married>0</Married>
33 <BirthDate>1975-03-14+05:30</BirthDate>
34 <ReportingTime>10:00:00+05:30</ReportingTime>
35 </Employee>
36 '
37
38 /*
39 Msg 6921, Level 16, State 1, Line 3
40 XML Validation: Element or attribute 'Married' was defined as fixed, the element value has to be
41 equal to value of 'fixed' attribute specified in definition. Location: /:Employee[1]/:Married[1]
42 */
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. [code] [schema]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3
4 /*
5 This example adds the "default" attribute to specify a certain value
6 if the element is not present.
7 */
8
9 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
10 <schema xmlns="http://www.w3.org/2001/XMLSchema">
11 <element name="Employee">
12 <complexType>
13 <sequence>
14 <element name="FullName" type="string" />
15 <element name="Salary" type="decimal" />
16 <element name="Age" type="integer" />
17 <element name="Married" type="boolean" fixed="1"/>
18 <element name="BirthDate" type ="date" />
19 <element name="ReportingTime" type="time" default="10:00:00+05:30"/>
20 </sequence>
21 </complexType>
22 </element>
23 </schema>
24 '
25 GO
26
27 DECLARE @emp AS XML(EmployeeSchema)
28 SET @emp = '
29 <Employee>
30 <FullName>Jacob</FullName>
31 <Salary>10000</Salary>
32 <Age>30</Age>
33 <Married>1</Married>
34 <BirthDate>1975-03-14+05:30</BirthDate>
35 <ReportingTime />
36 </Employee>
37 '
38 /*
39 This XML validates correctly because there is a default value specified in the
40 SCHEMA for the element <Reportingtime>. If the default value is removed from
41 the SCHEMA definition, SQL Server will generate the following error while validating
42 the above XML value.
43
44 Msg 6926, Level 16, State 1, Line 3
45 XML Validation: Invalid simple type value: ''. Location: /:Employee[1]/:ReportingTime[1]
46 */
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. [code] [schema]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3
4 /*
5 This version of the SCHEMA defines a new attribute in the "Employee" node.
6 The attribute is defined as "required". By default an attribute is "optional".
7 To make it mandatory, you need to use the "use" attribute and specify the value
8 "required"
9 */
10
11 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
12 <schema xmlns="http://www.w3.org/2001/XMLSchema">
13 <element name="Employee">
14 <complexType>
15 <sequence>
16 <element name="FullName" type="string" />
17 <element name="Salary" type="decimal" />
18 <element name="Age" type="integer" />
19 <element name="Married" type="boolean" fixed="1"/>
20 <element name="BirthDate" type ="date" />
21 <element name="ReportingTime" type="time" default="10:00:00+05:30"/>
22 </sequence>
23 <attribute name="EmployeeNumber" type="integer" use="required" />
24 </complexType>
25 </element>
26 </schema>
27 '
28 GO
29
30 DECLARE @emp AS XML(EmployeeSchema)
31 SET @emp = '
32 <Employee>
33 <FullName>Jacob</FullName>
34 <Salary>10000</Salary>
35 <Age>30</Age>
36 <Married>1</Married>
37 <BirthDate>1975-03-14+05:30</BirthDate>
38 <ReportingTime />
39 </Employee>
40 '
41
42 /*
43 The above code will generate the following error because the attribute
44 "EmployeeNumber" is a required attribute.
45
46 Msg 6906, Level 16, State 1, Line 3
47 XML Validation: Required attribute 'EmployeeNumber' is missing. Location: /:Employee[1]
48
49 If the "required" modifier is not specified, then "optional" is assumed. Hence the above
50 XML will validate correctly.
51
52 The correct XML for the above SCHEMA is given below.
53 */
54
55 DECLARE @emp AS XML(EmployeeSchema)
56 SET @emp = '
57 <Employee EmployeeNumber="1001">
58 <FullName>Jacob</FullName>
59 <Salary>10000</Salary>
60 <Age>30</Age>
61 <Married>1</Married>
62 <BirthDate>1975-03-14+05:30</BirthDate>
63 <ReportingTime />
64 </Employee>
65 '
66
67 /*
68 The above XML validates correctly. You will not be able to use an alpha-numeric
69 employee number, because the SCHEMA defines it to be an INTEGER.
70 */
Validating attributes with fixed and default
The following example demonstrates how to write a schema which validates attributes with fixed and default. [code] [schema]
1 DROP XML SCHEMA COLLECTION EmployeeSchema
2 GO
3
4 /*
5 This version of the SCHEMA demonstrates how to implement "fixed" and "default" restrictions
6 with attributes.
7 */
8 CREATE XML SCHEMA COLLECTION EmployeeSchema AS '
9 <schema xmlns="http://www.w3.org/2001/XMLSchema">
10 <element name="Employee">
11 <complexType>
12 <sequence>
13 <element name="FullName" type="string" />
14 <element name="Salary" type="decimal" />
15 <element name="Age" type="integer" />
16 <element name="Married" type="boolean" fixed="1"/>
17 <element name="BirthDate" type ="date" />
18 <element name="ReportingTime" type="time" default="10:00:00+05:30"/>
19 </sequence>
20 <attribute name="EmployeeNumber" type="integer" use="required" />
21 <attribute name="Language" type="string" fixed="EN" />
22 <attribute name="Nationality" type="string" default="Alien" />
23 </complexType>
24 </element>
25 </schema>
26 '
27 GO
28
29 DECLARE @emp AS XML(EmployeeSchema)
30 SET @emp = '
31 <Employee EmployeeNumber="1001" Language="EN" >
32 <FullName>Jacob</FullName>
33 <Salary>10000</Salary>
34 <Age>30</Age>
35 <Married>1</Married>
36 <BirthDate>1975-03-14+05:30</BirthDate>
37 <ReportingTime />
38 </Employee>
39 '
40 /*
41 Language should always be "EN" because a fixed value is specified in the SCHEMA.
42 If you enter any other language, you will get the following error.
43
44 Msg 6921, Level 16, State 2, Line 2
45 XML Validation: Element or attribute 'Language' was defined as fixed, the element value has to be equal
46 to value of 'fixed' attribute specified in definition. Location: /:Employee[1]/@:Language
47
48 "Nationality" can be ignored. If you ignore this attribute, the default value of "Alien" is assumed.
49 */
Conclusions
This session presented a few examples that shows how to do data type validations using an XML Schema. I will come up with a few more workshops which explains XML Schemas in much more detail.