querying xml store in a text field

Question

I have a sql server 2005 table which has xml store in a text field. I am wondering if there is an easy way (using TSQL) of retrieving the a value from the xml data without doing a line by line parsing of the text?

Doing a line-by-line parsing is a possibility because the amount of xml is pretty small. However, if possible I would like to find a higher performance method.

NOTE: The xml was created by means of a FoxPro CURSORTOXML function (in a FoxPro front-end application), and was then subsequently saved to a text field in a sql server table. The xml schema is built-in as part of the stored xml.

Any suggestions would be appreciated!

Answer 1

You can fetch the XML data from the column and convert it to the XML datatype, and query the XML easily using XQuery or use XPath to extract values from it.

XML Support In SQL Server 2005

e.g.

DECLARE @xml XML
Select @xml = CAST(ColData AS XML)
@xml.value('<xquery expression goes here>');

Answer 2

Yes. An example here "Importing XML into SQL Server" and another "Search XML in SQL Server"

Otherwise, please can you post the xml and we can offer some ideas... there are slightly different solutions based on whether reading values, attributes, hierarchy etc

Answer 3

You will want to do performance testing on both solutions (convert to XML vs. line-by-line parsing).