The SQL Server XML Data Type

SQL Server 2005 has introduced a new XML data type that allows storage into a database of XML documents, plus direct functional access to that XML document. The first thing to examine is how to create a table in a SQL Server database that contains an XML data type. The easiest way to change metadata in SQL Server 2005 is by using the SQL Server Management Studio tool in SQL Server Express edition. Figure 6-1 shows the creation of a simple table, containing a single XML data type field.

Figure 6-1: Creating a SQL Server table including an XML data type field

At this stage you have to decide a number of things:

• Will the XML data type be typed or untyped?

• Will it be used to store a complete XML document or just a fragment?

• Will one or more schema definitions be associated with the XML data type?

An untyped XML data type is used to contain XML documents that you know are properly formed . A typed XML data type allows specification of conformance according to a schema collection. So, the picture in Figure 6-1 will change to that shown in Figure 6-2, which now contains two XML fields: one untyped ( XMLUntyped ), and the other typed ( XMLType ).

Figure 6-2: Untyped versus typed XML data type fields

For a typed XML data type, a schema collection has to be specified. Typed XML data types go into the topic area of XML Schema Definition schemas (XSD schemas), which are covered later in this chapter. It is best to keep things simple to begin with. For now, only an untyped XML data type is covered. Figure 6-3 shows a new table saved as dbo.XML where only a single XML data type field is stored as an untyped XML data type. The ID field has been set as the primary key for the table.

Figure 6-3: A table containing an XML data type

XML data types can be used to store XML data based on one or more schemas, if the data type will contain complete XML documents or XML document fragments . A fragment is an incomplete XML document in that it contains only a part of an XML document. A fragment does not necessarily have a root element. Without a schema collection defined, an untyped XML data type has its Is XML Document option set to No in the management studio, as shown in Figure 6-1. An untyped XML document is not allowed to be set to anything but No and can be either a complete XML document, or an XML fragment.

The demographics XML document used in this book is 4GB in file size , with no white space in the file. SQL Server 2005 has an upper limit of 2GB for an XML data type.

SQL Server XML Data Type Methods

Some will call methods functions, others call them methods. Once again, a function is a procedural programming and relational database term used to describe a process that executes when called. A function can accept parameters and pass back a single value as a returned response. The single returned value of a function allows a function to be embedded as part of an expression. A method, on the other hand, is a chunk of processing that acts on an object, and only on that object. The SQL Server XML data type is effectively a class, where functions operating on that data type are in effect methods. Those methods exist solely to operate on the content of an XML data type object.

Software vendors use all sorts of terminology for all sorts of things. Quite often terms used by different relational database vendors can mean exactly the same thing. In this book I am attempting to standard ize terminology a little so that you, the reader, get a little more of a consistent mental picture when read ing about the XML capabilities of different databases.

What are the methods allowed for an XML data type in a SQL Server database?

• query(expression [, namespace]) : Extracts nodes from an XML document using a pattern matching string (or expression), and from an XML document stored in an XML data type in a SQL Server database.

• value(expression, typecast -datatype [, namespace]) : Returns a single value of an element or attribute contained within an XML document. The result is type-cast into a specified data type.

• exist(expression [, namespace]) : Returns a Boolean result if an expression finds a value (returns a 1) in an XML document or not (returns a 0).

• nodes(expression [, namespace]) : Returns a relational or flattened structural equivalent of an XML hierarchical node structure.

• modify(expression [, namespace]) : Changes XML document element and attribute values.

The nodes() method is supposed to return a flattened relational structural equivalent of an XML doc ument hierarchy. The nodes() method is in SQL Server documentation but does not appear to exist as an executable function in my installed version for SQL Server 2005 Express edition in the default namespace.

The exact details of the preceding methods will be covered later in this chapter. As you can see, the preceding methods allow for both extraction manipulation of XML document data, which is stored within XML data types.