XML Workshop 7 - Validating values with SCHEMA

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

10

11

12

13

14

15

16

17

18

19

20

21

22

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

35 Jacob

36 $10,000

37 Thirty

38 Yes

39 1975-03-14

40 10:00 AM

41

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

56 Jacob

57 10,000

58 Thirty

59 Yes

60 1975-03-14

61 10:00 AM

62

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

77 Jacob

78 10000

79 Thirty

80 Yes

81 1975-03-14

82 10:00 AM

83

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

99 Jacob

100 10000

101 30

102 Yes

103 1975-03-14

104 10:00 AM

105

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

120 Jacob

121 10000

122 30

123 1

124 1975-03-14

125 10:00 AM

126

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

143 Jacob

144 10000

145 30

146 1

147 1975-03-14+05:30

148 10:00 AM

149

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

162 Jacob

163 10000

164 30

165 1

166 1975-03-14+05:30

167 10:00:00

168

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

183 Jacob

184 10000

185 30

186 1

187 1975-03-14+05:30

188 10:00:00+05:30

189

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 thefixed 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

10

11

12

13

14

15

16

17

18

19

20

21

22

23 '

24 GO

25

26 DECLARE @emp AS XML(EmployeeSchema)

27 SET @emp = '

28

29 Jacob

30 10000

31 30

32 0

33 1975-03-14+05:30

34 10:00:00+05:30

35

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

11

12

13

14

15

16

17

18

19

20

21

22

23

24 '

25 GO

26

27 DECLARE @emp AS XML(EmployeeSchema)

28 SET @emp = '

29

30 Jacob

31 10000

32 30

33 1

34 1975-03-14+05:30

35

36

37 '

38 /*

39 This XML validates correctly because there is a default value specified in the

40 SCHEMA for the element . 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 torequired. 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

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27 '

28 GO

29

30 DECLARE @emp AS XML(EmployeeSchema)

31 SET @emp = '

32

33 Jacob

34 10000

35 30

36 1

37 1975-03-14+05:30

38

39

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

58 Jacob

59 10000

60 30

61 1

62 1975-03-14+05:30

63

64

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

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26 '

27 GO

28

29 DECLARE @emp AS XML(EmployeeSchema)

30 SET @emp = '

31

32 Jacob

33 10000

34 30

35 1

36 1975-03-14+05:30

37

38

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.