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

XML Workshop VII - Validating values with SCHEMA

By Jacob Sebastian,

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.

Total article views: 4826 | Views in the last 30 days: 14
 
Related Articles
FORUM

How to add attribute from Parent element to child element's attribute using visual studio designer

How to add attribute from Parent element to child element's attribute using visual studio designer

FORUM

help on xml schema

adding attribute to element using xml schema in sql server

FORUM

FOR XML EXPLICIT add attribute to element

How to add an attribute to an element

FORUM

Replacing Schema with new schema of Database objects

Replace "Emp" schema with "Employee" Schema

FORUM

XML schema - Element name used to identify item problem...

need to use the element name to identify value when importing into SQL 2005 tables

Tags
schemas/dtds    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

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

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

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