Where to Store XML Documents

A few years ago, XML documents were "the wave of the future." Now they are here to stay. Most likely, you have seen and used XML documents for configuration and other uses in applications you have used. SQL Server 2005 and the Microsoft .NET Framework use XML files extensively for setting configuration settings and implementing other features, such as SQL Server Integration Services (for which the XML is stored in .dtsx files) and SQL Server Reporting Services (for which the XML is stored in .rdl files). XML has a number of advantages, not the least of which is flexibility when the configuration requirements of your application change. As mentioned before, XML files also allow the user of the application to easily change configuration settings.

XML also stores hierarchial data very well. Some examples of hierarchical data are store receipts, bills of material, and healthcare service invoices. All of these examples involve parent records with varying levels of child records. Retrieving complete sets of this kind of data from a database engine can be complicated, but XML stores this data in a form that is easy to review. Because XML is so powerful and is becoming so widely used, Microsoft included the XML data type in SQL Server 2000 and SQL Server 2005 and implemented specific optimizations and T-SQL statements for managing XML data.


XML documents are already being used extensively to share data between internal and external consumers in a variety of businesses. In particular, healthcare applications use XML schemas to share data between external partners and internal systems. Microsoft BizTalk Server 2006 is designed for business process management and integration in situations just like this and uses XML documents for this purpose.

SQL Server 2005 XML Support

With SQL Server 2005, Microsoft introduced a number of new XML-specific features in the database engine. These improvements allow developers to access and manipulate XML data more easily. Here are a few of the improvements:

  • Native XML datatype

  • Support for XML schemas

  • Ability to use XQuery against XML data stored in XML datatyped columns and variables

  • Ability to index XML data stored in XML columns

  • Support for the XML Data Manipulation Language (XML-DML)

  • Improvements to existing SQL Server 2000 XML functionality, including the OPENROWSET, FOR XML, and OPENXML keywords.

Using the XML Datatype

SQL Server 2005 introduces a fully functional XML datatype. This datatype makes it possible to use XML-specific SQL to access and search XML data. This datatype is available for both tables and variables. If you store your data using the XML datatype, you can use SQL Server's implementation of the XQuery language to query the data. Prior to the introduction of the XML datatype, database designers had to extract the XML into a relational version of the data in order to query it. Having to extract the XML into tables and columns (called shredding the data) so that the data could be queried limited the flexibility of XML documents. Now, with the ability to use the XML datatype, developers can reference the contents of the document without first shredding it. Table 1-3 lists the XML data type methods supported in SQL Server 2005.

Table 1-3. XQuery Methods Supported in SQL Server 2005





.query(XQuery expression)

Selects data in the XML document or fragment, similar to a SELECT statement.


.value(XQuery expression, SQL datatype)

Combines the functionality of the query() method with the CONVERT function in SQL. It allows you to select a value from the XML document or fragment and convert it to the specified datatype.


.exist(XQuery expression)

Returns TRUE if the expression being searched for is found in the XML document, similar to the EXISTS statement in T-SQL.


.modify(XML-DML expression)

Allows you to insert, update, or delete nodes in the XML document. The modify() method must be used in a T-SQL UPDATE statement. (See SQL Server Books Online for more information about XML-DML.)


.nodes(XQuery expression) as TableName(ColumnName)

Allows you to shred the document, putting the results into a relational format.

The following example will show you how you can query XML data with XML variables.

Using the XQuery Query() Method


From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.


In Microsoft SQL Server Management Studio, create a new query by clicking the New Query toolbar button. (The completed query is included in the sample files as XQueryQueryMethod.sql.)


Declare a variable using the XML datatype by typing the following code in the query pane:



Fill the variable with an XML expression by adding the following code in the query pane:

SET @SampleXML = ' <root>     <L1>         <L2>This is the First Line</L2>     </L1>     <L1>         <L2>This is the Second Line</L2>     </L1> </root>'


Use the query() method to retrieve the values from the L2 node. Enter the following code in the query pane:

SELECT @SampleXML.query('/root/L1/L2)')


Execute the query by clicking the Execute toolbar button or by pressing the F5 function key. The following results will be displayed in the results pane:

<L2> This is the First Line<\L2><L2> This is the Second Line<\L2>

While this is a simple example, it should give you some insight into what can be done using the new XQuery functionality in SQL Server. You could use the data() method to return data from an element without the XML tags, or use the exist() method to verify whether or not a specified node exists.

The following code (included in the sample files as XQueryQueryDataMethod.sql) provides an example of using the data() method to return a specific piece of XML data without XML tags:

DECLARE @SampleXML XML SET @SampleXML = ' <root>     <L1>         <L2>This is the First Line</L2>     </L1>     <L1>         <L2>This is the Second Line</L2>     </L1> </root>' SELECT @SampleXML.query('data(/root/L1[L2 = "This is the Second Line"])')

Your result set should be This is the Second Line.

The following code (included in the sample files as XQueryQueryExistMethod.sql) provides an example of using the exist() method to determine whether a specific piece of XML data is present in a node:

DECLARE @SampleXML XML, @Exists bit SET @SampleXML = ' <root>     <L1>         <L2>This is the First Line</L2>     </L1>     <L1>         <L2>This is the Second Line</L2>     </L1> </root>' SET @Exists = @SampleXML.exist('/root/L1/L2[text() =      "This is the First Line"]') SELECT @Exists

Your result set should be 1.

One of the advantages of using an XML variable is that you can cast a column that has a different datatype, like TEXT or VARCHAR, as XML in order to use XQuery methods on data in that column. If you have an existing environment in SQL Server 2000 where you use XML, you are most likely storing your data in a TEXT or VARCHAR column. In SQL Server Management Studio, you can view the data as XML. This was not possible in Query Analyzer, which returned the data in multiple rows. The following code sample shows how to cast text data as XML.

SELECT CAST(textdata AS XML) FROM dbo.SomeTable WHERE SomeColumnID = 1

Figure 1-1 shows the results displayed as a row from a query that returns XML data in SQL Server Management Studio.

Figure 1-1. SQL Server Management Studio's result from an XML Query displayed as a row.

If you click the link in the results shown in Figure 1-1, you will see the results displayed as XML, as shown in Figure 1-2.

Figure 1-2. XML as viewed in SQL Server Management Studio after clicking the link in the results pane.

XML: Typed or Untyped?

SQL Server 2005 supports XML schemas with the XML datatype. Figure 1-3 shows the location of schema collections in SQL Server Management Studio. If you use schemas, or schema collections as they are called in SQL Server, SQL Server will apply the schema to the XML stored in the table. Applying a schema enforces datatypes and constraints by parsing the XML against the schema that has been loaded into the XML datatype column. By using typed XML columns, you will be able to guarantee the format of the XML data.

Figure 1-3. The location of XML Schemas in SQL Server Management Studio.

If you store XML data as untyped, that is, if you do not apply a schema to the data directly, it does not change the fact that the datatype is XML, and you can still use the XML functions with that data. In either case, a number of T-SQL statements and functions are generally available to both typed and untyped XML columns.

By now you can see the benefits of using the XML datatype. For new applications with XML requirements, you will of course want to use the XML datatype. However, if you are in the process of upgrading the database engine for an existing system, the decision may not be easy. For example, if you have existing code that deals with XML data in a TEXT or NTEXT column, it may be in your best interest to keep your data as is. Unless switching to the XML datatype provides functionality you absolutely need, consider using the XML datatype for future development or down the line in a separate application upgrade.


If you are upgrading your database engine, it is best to upgrade the engine separately from making major upgrades to your application. While the XML datatype is a great new feature, the amount of redevelopment and testing required to implement significant application changes can be detrimental to your database engine upgrade if you try to upgrade your application at the same time.

Using the File System with XML Data

Even though SQL Server 2005 has made significant improvements to XML stored in the database, it can still access XML documents stored outside of the database. By using the FOR XML and OPENXML statements, you will be able to write to XML files and shred the files for insertion into a database. Both of these statements were available in SQL Server 2000 and function the same way in SQL Server 2005. Furthermore, you can use OPENROWSET to bulk-load XML documents into the database.

Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net