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.
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.
The following example will show you how you can query XML data with XML variables.
Using the XQuery Query() Method
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.
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.
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.