带有未声明前缀的 SQL Server Xml 查询

2021-10-02 00:00:00 xml-namespaces xml xpath sql-server-2008 sql-server

我在 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:

  1. 如果没有相应的命名空间声明，则不允许有命名空间前缀.
  2. 您的结束标签没有在任何地方都包含/...
  1. It is not allowed to have a namespace prefix without a corresponding namespace declaration.
  2. 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...

相关文章