SQL Server XML查询文本数据类型(SQL Server XML queries against text data type)

SQL Server XML查询文本数据类型(SQL Server XML queries against text data type)

我有一个包含XML内容的ntext数据类型列，如下所示：

CREATE TABLE [dbo].[SampleTable]( [SampleId] [bigint] NOT NULL, [XMLContent] [ntext] NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ( [SampleId] ASC ) )

我可以对SQL Server中的Ntext列运行XML查询吗？

到目前为止，我可以使用类似的查询来解决方法，但希望有更好的方法。

I have a ntext data type column which contains XML content, like this:

CREATE TABLE [dbo].[SampleTable]( [SampleId] [bigint] NOT NULL, [XMLContent] [ntext] NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ( [SampleId] ASC ) )

Can I run XML queries against a Ntext column in SQL Server?

So far I can do workaround using like queries but would like a better approach.

最满意答案

你不能。 为了使用XQuery - 您必须使用XML数据类型。

如果那些列确实只包含XML - 什么阻止你将它改为XML （这应该是首先，真的）？

如果你不能改变它（再次：为什么不改变？？！），那么你需要为每个操作进行CAST（XMLContent as XML）：

SELECT CAST(XMLContent AS XML).value('.....', '..')

等等 - 这不是一个非常有用和有用的方法。

此外： TEXT和NTEXT已弃用，不应再使用 - 使用更合适的数据类型，如XML或(N)VARCHAR(MAX)

No you cannot. In order to use XQuery - you must be using the XML datatype.

If that columns really does contain only XML - what's stopping you from changing it to be XML (which is should be in the first place, really)?

If you can't change it (again: why not!?!), then you need to CAST(XMLContent as XML) for every single operation:

SELECT CAST(XMLContent AS XML).value('.....', '..')

and so forth - not a very useful and helpful approach.

Furthermore: TEXT and NTEXT are deprecated and should not be used anymore - use the more appropriate datatypes like XML or (N)VARCHAR(MAX) instead