问题描述
我在 SQL Server 2008 中有一个变量,其中包含 XML 列的一部分,但我无法查询它.XML 可以包含或不包含前缀ns0".我想从 xml 中检索 [from].我试过的查询是这样的:
I have a variable in SQL Server 2008 that contains a part of a XML column and I am having trouble querying it. The XML can contain a prefix "ns0" or not. I want to retrieve [from] from the xml. The query I tried was this:
DECLARE @Params XML
SET @Params = '<filter>
<ns0:from>2016-09-19</ns0:from>
<ns0:to>2017-01-01<ns0:to>
</filter>';
SELECT @Params.value('(/*:filter/*:from)[1]', 'Varchar(max)') AS [from]
我没有用于声明任何命名空间的 XML 标头.当我尝试执行此操作时,出现此错误:
I don't have a XML header to declare any namespace. When I try to execute this, I got this error:
XML parsing: line 1, character 10, undeclared prefix
但是当我尝试这个时,一切正常:
But when I try this, everything works fine:
SET @Params = '<filter>
<from>2016-09-19<from>
<to>2017-01-01<to>
</filter>
SELECT @Params.value('(/*:filter/*:from)[1]', 'Varchar(max)') AS [from]
如何使用 XPath 查询检索 [from] 给定上述带有前缀或不带前缀的 xml 示例?
How can I retrieve [from] using an XPath query given the above xml example with a prefix or without prefix?
推荐答案
你的例子在两个方面是无效的:
Your example is invalid in two ways:
- 如果没有相应的命名空间声明,则不允许有命名空间前缀.
- 您的结束标签没有在任何地方都包含
/
...
- It is not allowed to have a namespace prefix without a corresponding namespace declaration.
- Your closing tags do not include the
/
everywhere...
这是一个丑陋的黑客,但你可以试试这个:
This is an ugly hack, but you might try this:
DECLARE @Params XML
SET @Params = REPLACE('<filter>
<ns0:from>2016-09-19</ns0:from>
<ns0:to>2017-01-01</ns0:to>
</filter>','ns0:','');
SELECT @Params.value('(/*:filter/*:from)[1]', 'date') AS [from];
如果您事先不知道所有命名空间前缀,这将变得非常棘手...
If you do not know all namespace prefixes in advance this will get really tricky...
这篇关于带有未声明前缀的 SQL Server Xml 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!