問題描述
我在 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 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！