Xml Data Type

One of the major new features of SQL Server 2005 is the XML data type. With older versions of SQL Server, XML data have been stored inside a string or a blob. Now XML is a supported data type that allows combining SQL queries with XQuery expressions to search within XML data.

With Office 2003, it is possible to store Word and Excel documents as XML. Word and Excel also support using custom XML schemas, where only the content (and not the presentation) is stored with XML. The output of Office applications can be stored directly in SQL Server 2005, where it is possible to search within this data. Of course custom XML data can also be used for storing in SQL Server.

Important

Don't use XML types for relational data. If you do a search for some of the elements and if the schema is clearly defined for the data, by storing these elements in a relational fashion, the data can be accessed faster. If the data is hierarchical and some elements are optional and may change over time, storing XML data has many advantages.

Tables with XML Data

Creating tables with XML data is as simple as selecting the Xml data type with a column. The following CREATE TABLE SQL command creates the Events table with the column Id that is also the primary key and the column Event, which is of type xml:

 CREATE TABLE [dbo].[Events]( [Id] [int] IDENTITY(1,1) NOT NULL, [Number] [nchar] (10) NOT NULL, [Info] [xml] NOT NULL, CONSTRAINT [PK_Exams] PRIMARY KEY CLUSTERED  ( [Id] ASC ) ON [PRIMARY] ) ON [PRIMARY] 

For a simple test, the table is filled with these data:

 INSERT INTO Exams values('70-315,  <Exam Number="70-315"> <Title>Developing and Implementing Web Applications with Microsoft Visual C#</Title> <Certification Name="MCAD" Status="Elective" /> <Certification Name="MCSD" Status="Core" /> <Course>2310</Course> <Course>2389</Course> <Course>2640</Course> <Topic>Creating User Services</Topic> <Topic>Creating and Managing Components and .NET Assemblies</Topic> <Topic>Consuming and Manipulating Data</Topic> <Topic>Testing and Debugging</Topic> <Topic>Deploying a Web Application</Topic> <Topic>Maintaining and Supporting a Web Application</Topic> <Topic>Configuring and Securing a Web Application</Topic> </Exam>') INSERT INTO Exams values(70-316,  <Exam Number="70-316"> <Title>Developing and Implementing Windows-based Applications with Microsoft Visual C#</Title> <Certification Name="MCAD" Status="Elective" /> <Certification Name="MCSD" Status="Core" /> <Course>2555</Course> <Course>2389</Course> <Topic>Creating User Services</Topic> <Topic>Creating and Managing Components and .NET Assemblies</Topic> <Topic>Consuming and Manipulating Data</Topic> <Topic>Testing and Debugging</Topic> <Topic>Deploying a Windows-based Application</Topic> <Topic>Maintaining and Supporting a Windows-based Application</Topic> <Topic>Configuring and Securing a Windows-based Application</Topic> </Exam>') INSERT INTO Exams values('70-320,  <Exam Number="70-320"> <Title>Developing XML Web Services and Server Components with Microsoft Visual C#</Title> <Certification Name="MCAD" Status="Core" /> <Certification Name="MCSD" Status="Core" /> <Course>2524</Course> <Course>2389</Course> <Course>2557</Course> <Course>2663</Course> <Topic>Creating and Managing Microsoft Windows Services, Serviced Components, .NET Remoting Objects and XML Web Services</Topic> <Topic>Consuming and Manipulating Data</Topic> <Topic>Testing and Debugging</Topic> <Topic>Deploying Windows Services, Serviced Components, .NET Remoting Objects, and XML Web Services</Topic> </Exam>') 

You can read the XML data with ADO.NET using a SqlDataReader object. The SqlDataReader method GetSqlXml() returns a SqlXml object. The SqlXml class has a property Value that returns the complete XML representation and a CreateReader()method that returns a XmlReader object.

Note

Using the XmlReader class is discussed in Chapter 21, "Manipulating XML."

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Xml; class Program { static void Main(string[] args) { string dsn = @"server=localhost;database=WroxDemo;trusted_connection=true"; SqlConnection connection = new SqlConnection(dsn); SqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Id, Number, Info FROM Exams"; connection.Open(); SqlDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection); while (reader.Read()) { SqlXml xml = reader.GetSqlXml(2); Console.WriteLine(xml.Value); XmlReader xmlReader = xml.CreateReader(); // use the reader } reader.Close(); } } } 

Query of Data

Until now, you haven't seen the really cool features of the XML data type. SQL SELECT statements can be combined with XML XQuery.

A SELECT statement combined with an XQuery expression to read into the XML value is shown here:

 SELECT Id, Number, Info.query('/Exam/Course') AS Course FROM Exams 

The XQuery expression /Exam/Course accesses the Course elements that are children of the Exam element. The result of this query returns the ids, exam numbers, and courses:

 1  70-315 <Course>2310</Course><Course>2389</Course><Course>2640</Course> 2  70-316 <Course>2555</Course><Course>2389</Course> 3  70-320 <Course>2524</Course><Course>2389</Course><Course>2557</Course> <Course>2663</Course> 

With an XQuery expression you can create more complex statements to query data within the XML content of a cell. The next example converts the XML from the exam information to XML that lists information about courses:

 SELECT Info.query(' for $course in /Exam/Course return <Course> <Exam>{ data(/Exam[1]/@Number) }<Exam> <Number>{ data($course) }</Number> </Course>') AS Course FROM Exams WHERE Id=1 

Here just a single row is selected with SELECT Info... FROM Exams WHERE Id = 1. With the result of this SQL query, the for and return statements of an XQuery expression are used. for $course in /Exam/Course iterates through all Course elements. $course declares a variable that is set with every iteration (similar to a C# foreach statement). Following the return statement the result of the query for every row is defined. The result for every course element is surrounded with the <Course> element. Embedded inside the <Course> element are <Exam> and <Number>. The text within the <Exam> element is defined with data(/Exam[1]/@Number). data() is an XQuery function that returns the value ofthe node specified with the argument. The node /Exam[1] is used to access the first <Exam> element;@Number specifies the XML attribute Number. The text within the element <Number> is defined from the variable $course.

Note

Contrary to C#, where the first element in a collection is accessed with an index of 0, with XPath the first element in a collection is accessed with an index of 1.

The result of this query is shown here:

 <Course> <Exam>70-315</Exam> <Number>2310></Number> </Course> <Course> <Exam>70-315</Exam> <Number>2389</Number> </Course> <Course <Exam>70-315</Exam> <Number>2640</Number> </Course> 

XQuery in SQL Server allows using several other XQuery functions for getting minimum, maximum, or summary values, working with strings, numbers, checking for positions within collections, and so on.

XML Data Modification Language (XML DML)

XQuery as it is defined by the W3C (http://www.w3c.org) only allows querying of data. Because of this XQuery restriction, Microsoft defined an extension to XQuery that has the name XML Data Modification Language (XML DML). With XML DML it is possible to modify XML data. For this the following keywords extend XQuery: insert, delete, and replace value of.

This section looks at some examples to insert, delete, and modify XML contents within a cell.

You can use the insert keyword to insert some XML content within an XML column without replacing the complete XML cell. Here <Course>2555</Course> is inserted as the last child-element of the first Exam element:

 UPDATE Exams SET Info.modify(' insert <Course>2555</Course> as last into Exam[1]') WHERE id=3 

XML content can be deleted with the delete keyword. Within the first Exam element, the fifth Course element is deleted:

 UPDATE Exams SET Info.modify(' delete /Exam[1]/Course[5]) FROM Exams WHERE id=3 

It is also possible to change XML content. Here, the keyword replace value of is used. The expression /Exam/Course[text() = 2310] accesses only the children-elements Course where the text content contains the string 2310. From these elements, only the text content is accessed for replacement. with 2644 specifies that the new course number is 2644:

 UPDATE Exams SET Info.modify(' replace value of /Exam/Course[text() = 2310]/text()[1] with 2644') FROM Events 

XML Indexes

Indexes can also be set to XML types. With XML indexes, these index types must be distinguished between a primary and a secondary XML index. A primary XML index is created for the complete persisted representation of the XML value.

The following SQL command CREATE PRIMARY XML INDEX creates the index idx_events on the Event column:

 CREATE PRIMARY XML INDEX idx_exams on Exams (Info) 

Primary indexes don't help if the query contains an XPath expression to directly access XML elements of the XML type. For XPath and XQuery expressions, XML secondary indexes can be used. If an XML secondary index is created, the primary index must already exist. With secondary indexes, these index types must be distinguished:

• PATH index

• VALUE index

• PROPERTY index

A PATH index is used if exists() or query() functions are used and XML elements are accessed with an XPath expression. Using the XPath expression /Event/Location it might be useful to do a PATH index:

 CREATE XML INDEX idx_examNumbers on Exams (Info) USING XML INDEX idx_exams FOR PATH 

The PROPERTY index is used if properties are fetched from elements with the value() function. The FOR PROPERTY statement with the index creation defines a PROPERTY index:

 CREATE XML INDEX idx_examsNumbers on Exams (Info) USING XML INDEX idx_exams FOR PROPERTY 

If elements are searched through the tree with an XPath descendant-or-self axis expression, the best performance might be achieved with a VALUE index. The XPath expression //Certification searches all Certification elements with the descendant-or-self axis. The expression [@Name="MCAD"] returns only the elements where the attribute Name has the value MCAD:

 SELECT Info FROM Exams  WHERE Info.exists('//Certification[@Name="MCAD"]') = 1 

The VALUE index is created with the FOR VALUE statement:

 CREATE XML INDEX idx_examNumbers on Exams (Info) USING XML INDEX idx_exams FOR VALUE 

Strongly Typed XML

The XML data type in SQL Server can also be strongly typed with XML schemas. With a strongly typed XML column it is verified if the data conforms to the schema when XML data is inserted.

A XML schema can be created with the CREATE XML SCHEMA COLLECTION statement. The statement shown here creates the XML schema CourseSchema that defines the element Course with the child elements Number and Title:

 CREATE XML SCHEMA COLLECTION CourseSchema AS '<?xml version="1.0" encoding="UTF-8"?> <xs:schema  targetNamespace="http://thinktecture.com/Courses.xsd"  elementFormDefault="qualified" xmlns="http://thinktecture.com/Courses.xsd"  xmlns:mstns="http://thinktecture.com/Courses.xsd"  xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:complexType name="CourseElt"> <xs:sequence> <xs:element name="Number" type="xs:string" maxOccurs="1" minOccurs="1" /> <xs:element name="Title" type="xs:string" maxOccurs="1" minOccurs="1" /> <xs:element name="Any" type="xs:anyType" maxOccurs="unbounded" minOccurs="0" /> </xs:sequence> </xs:complexType> <xs:element name="Course" type="CourseElt"> </xs:element> </xs:schema>' 

With the Visual Studio 2005 Database project type, there's no support to add a schema to the database. This feature is not available from the GUI by Visual Studio 2005 but must be done manually. For creating an XML schema with Visual Studio 2005, you can use an empty Visual Studio project type. You can copy the XML syntax of the schema and copy it to the CREATE XML SCHEMA statement.

Besides using Visual Studio you can copy the XML syntax to the SQL Server Management Studio to create and view the XML schemas (see Figure 20-4). The object explorer lists the XML schemas below the Types entry.

Figure 20-4

The XML schema can be assigned to a column by setting it with the xml data type:

 CREATE TABLE Courses ( [Id] [int] IDENTITY(1,1) NOT NULL, [Course] [xml]([dbo].[CourseSchema]) NOT NULL ) 

By creating the table with Visual Studio 2005 or with the SQL Server Management Studio, the XML schema can be assigned to a column by setting the property XML schema namespace.