問(wèn)題描述
我在 SQL Server 2008 中有一個(gè)變量，其中包含 XML 列的一部分，但我無(wú)法查詢它.XML 可以包含或不包含前綴ns0".我想從 xml 中檢索 [from].我試過(guò)的查詢是這樣的:
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]
我沒(méi)有用于聲明任何命名空間的 XML 標(biāo)頭.當(dāng)我嘗試執(zhí)行此操作時(shí)，出現(xiàn)此錯(cuò)誤:
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
但是當(dāng)我嘗試這個(gè)時(shí)，一切正常:
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?
推薦答案
你的例子在兩個(gè)方面是無(wú)效的:
Your example is invalid in two ways:
- 如果沒(méi)有相應(yīng)的命名空間聲明，則不允許有命名空間前綴.
- 您的結(jié)束標(biāo)簽沒(méi)有在任何地方都包含
/...
- It is not allowed to have a namespace prefix without a corresponding namespace declaration.
- Your closing tags do not include the
/everywhere...
這是一個(gè)丑陋的黑客，但你可以試試這個(gè):
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...
這篇關(guān)于帶有未聲明前綴的 SQL Server Xml 查詢的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！