Microsoft KB Archive/906566

= How to use XQuery to query XML data from a SQL Server 2000 database in SQL Server 2005 =

Article ID: 906566

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit

-



SUMMARY
This article discusses how to use XQuery in Microsoft SQL Server 2005 to query XML data from a Microsoft SQL Server 2000 database. You may want to include well-formed XML tags in the text data of the SQL Server 2000 database before you query the data from SQL Server 2005.

You can include the XML tags in text data for testing, for string parsing and comparison, and for easier reading. You may want to include XML tags when you store long text data that contains multiple elements of data in a SQL Server 2000 database. For example, a sales journal entry may contain names, telephone numbers, action items, and other information. When the data in the SQL Server 2000 database is migrated to SQL Server 2005, you can directly query the data by using XQuery in SQL Server 2005.

You can use this technique to read relational data and XML data from a computer that is running SQL Server 2000 in the same batch.



MORE INFORMATION
The following example shows how to use XQuery to query XML data from a SQL Server 2000 database.

Note This example assumes that you have created the following:
 * A linked server that is named SQL2kSRV for the computer that is running SQL Server 2000
 * A database that is named DBSQL2000 on the computer that is running SQL Server 2000

To use XQuery to query XML data from a SQL Server 2000 database, follow these steps:  Include well-formed XML tags in the text data that you want to store in a SQL Server 2000 database.  Store the data in a column that has a text data type such as nvarchar or ntext. To do this, run the following Transact-SQL statements in SQL Server 2000 Query Analyzer: CREATE TABLE [DBSQL2000].[tblTextLogs] ( [txtData] [nvarchar] (300)) INSERT INTO [DBSQL2000].[tblTextLogs] ([txtData]) VALUES ( '  The customer called and left a message. He wants to purchase $5 million widgets.   Call back David Jaffe next week.  ')  Open SQL Server Management Studio, and then create a new query.  Run the following Transact-SQL statement to create a temporary table that includes an xml data type column: CREATE TABLE #tblTextToXml (xmlField xml) </li>  Run the following Transact-SQL statement: INSERT INTO #tblTextToXml SELECT [txtData] FROM [SQL2kSRV].[DBSQL2000].[dbo].[tblTextLogs] This statement retrieves the text data from the SQL Server 2000 database. Then, the statement stores the data in the temporary table.

Note In SQL Server 2005, text data is automatically converted to an xml data type when text data is inserted into a column that has the xml data type. </li>  Use XQuery to query the data from the temporary table by using the following Transact-SQL statements. SELECT xmlField.query('/Customer') FROM #tblTextToXml WHERE xmlField.exist('/Customer') = 1 </li></ol>

Note You can create an actual table that has an xml data type in the SQL Server 2005 database instead of creating a temporary table.

<div class="references_section">