Search code examples
sql-serverxmlsql-server-2005foxpro

querying xml store in a text field

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!

Solution

  • 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>');