在文本字段中查询xml存储

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?

我有一个sql server 2005表,它在文本字段中有xml存储。我想知道是否有一种简单的方法(使用TSQL)从xml数据中检索一个值,而无需逐行解析文本?

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.

可能会进行逐行解析,因为xml的数量非常小。但是,如果可能的话,我想找到一种更高性能的方法。

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.

注意:xml是通过FoxPro CURSORTOXML函数(在FoxPro前端应用程序中)创建的,然后保存到sql server表中的文本字段。 xml架构作为存储的xml的一部分内置。

Any suggestions would be appreciated!

任何建议,将不胜感激!

3 个解决方案

#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数据并将其转换为XML数据类型,并使用XQuery轻松查询XML或使用XPath从中提取值。

XML Support In SQL Server 2005

SQL Server 2005中的XML支持

e.g.

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

#2

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

是。这里的示例“将XML导入SQL Server”和另一个“在SQL Server中搜索XML”

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

否则,请你发布xml,我们可以提供一些想法...根据阅读价值,属性,等级等等,有一些略有不同的解决方案

#3

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

您将需要对两种解决方案进行性能测试(转换为XML与逐行解析)。

#1