About use="required" attribute in Yukon

The use="required" attribute is important because it causes the schema to validate any XML and requires this attribute not only to be present, but also to be an interger / decimal / byte value.

Create XML schema collection testSchema as

N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customer">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Order">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="OrderID" type="xs:integer" use="required" />
<xs:attribute name="OrderQty" type="xs:decimal" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
go

Code snippet which would work

Declare @xmlString Xml(testSchema)
Set @xmlString =
N'<?xml version="1.0" ?>
<Customer>
<Order OrderID="11" OrderQty="1.5">Rice</Order>
<Order OrderID="22" OrderQty="12.50">Wheat</Order>
<Order OrderID="33" OrderQty="101.5">Oil</Order>
<Order OrderID="44" OrderQty="12.25">Mustard</Order>
</Customer>'

Select @xmlString.value('count(/Customer/Order)','int') as "Total Orders"

Code snippet which would throw an Error:

If our data contains the attribute without any specified value as shown in the below example it would throw an error.

Declare @xmlString Xml(testSchema)
Set @xmlString =
N'<?xml version="1.0" ?>
<Customer>
<Order OrderID="" OrderQty="1.5">Rice</Order>
<Order OrderID="22" OrderQty="12.50">Wheat</Order>
<Order OrderID="33" OrderQty="101.5">Oil</Order>
<Order OrderID="44" OrderQty="12.25">Mustard</Order>
</Customer>'

Select @xmlString.value('count(/Customer/Order)','int') as "Total Orders"
go

This would throw an error as follows:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: ''

Technorati tags: SQL, Databases, SQL Server, SQL Server 2005