How to use XQuery to query XML data from a SQL Server 2000 database in SQL Server 2005 (906566)

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 (
'<Customer ID="77" Name="David Jaffe">
<Lead>
The customer called and left a message. He wants to purchase $5 million widgets.
</Lead>
<ActionItem>
Call back David Jaffe next week.
</ActionItem>
</Customer>')

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)
```
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.
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
```

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.

How to use XQuery to query XML data from a SQL Server 2000 database in SQL Server 2005 (906566)

SUMMARY

MORE INFORMATION

REFERENCES