341
Chapter 14: Using XML Data
14
Querying XML wasn’t a walk in the park either, in the beginning. The same XML API’s that
were used to produce XML were used to query the XML. This caused a lot of overhead in any
application that produced or consumed XML. Something better was needed, and the answer
came with SQL Server 2000 in the form of the OPENXML function. The OPENXML function
requires a three-step process, using a couple of system stored procedures to prepare an
XML document handle and then another to release the handle, while calling OPENXML in
between to obtain the result set.Because of the way OPENXML was implemented (a function call between two system stored
procedure calls) it made it diffi cult in some circumstances to implement. For example, you
could not use it in set-based operations, and this is what SQL Server excels at!Luckily, SQL Server 2005 came to the rescue with the XML data type which enables you
to store complete XML documents or XML fragments. Included with the XML data type
was support for XQuery, a language specifi cally designed to query XML documents. This
functionality alone makes the OPENXML clause nearly obsolete because using XQuery is
more lightweight, more powerful, and much easier to use. It also does not have the limita-
tions of the OPENXML function.However, even though later versions of SQL Server came with better support for producing
and querying XML, protecting and ensuring the validity of the XML can’t be left behind.
Any production application should include a robust validation process to information being
exchanged, and even more so when exchanging XML data simply because the chances of
invalid values are much greater.For example, an application passing the value “thirty” to the @age parameter of a stored
procedure (@age INT) would receive a conversion error immediately as SQL Server would
perform an implicit data type validation.XML, however, is different. SQL Server can’t detect an error in an XML document. For
example, given the element “<Employee age=”too old to code” />”, the @age attribute
is not associated with a data type, and SQL Server simply does not know how to vali-
date it.The solution is the support for schemas, included with SQL Server 2005. Schema Defi nition
Language (XSD) is a language specifi cally used to describe and validate XML documents.
The validation is based on structure and format rules, providing the ability to validate an
XML document against the schema.Starting with SQL Server 2005, SQL Server supports XML Schemas via XML Schema
Collection objects, and you learn more about schemas and schema collections shortly. The
great thing is that you can apply schemas to an XML data type column, a variable, and a
parameter. By applying schemas, you can provide a more stringent validation of XML to
help many of the validation scenarios you might fi nd when dealing with non-XML data,
such as the following:c14.indd 341c14.indd 341 7/30/2012 4:49:00 PM7/30/2012 4:49:00 PM
http://www.it-ebooks.info