SQL Server XML 数据类型查询问题 [英] SQL Server XML Data Type query issue
问题描述
请参阅下面的 SQL Server 2005 脚本
Please see below SQL Server 2005 script
Declare @xmlData XML
SET @xmlData = '<?xml version="1.0"?>
<bookstore xmlns="http://myBooks">
<book genre="autobiography" publicationdate="1981"
ISBN="1-861003-11-0">
<title>The Autobiography of Benjamin Franklin</title>
<author>
<first-name>Benjamin</first-name>
<last-name>Franklin</last-name>
</author>
<price>8.99</price>
</book>
<book genre="novel" publicationdate="1967"
ISBN="0-201-63361-2">
<title>The Confidence Man</title>
<author>
<first-name>Herman</first-name>
<last-name>Melville</last-name>
</author>
<price>11.99</price>
</book>
<book genre="philosophy" publicationdate="1991"
ISBN="1-861001-57-6">
<title>The Gorgias</title>
<author>
<first-name>Sidas</first-name>
<last-name>Plato</last-name>
</author>
<price>9.99</price>
</book>
</bookstore>'
Select T.Item.query('.')
From @xmlData.nodes('/bookstore/book') AS T(Item)
这个脚本应该给我所有书籍节点的列表.但它没有给出预期的行为.如果我删除 XMLNS 那么它工作正常.谁能解释一下?
This script should give me list of all books node. But it is not giving expected behaviour. If i remove XMLNS then it is working fine. Can anyone explain?
以下工作正常.
Declare @xmlData XML
SET @xmlData = '<?xml version="1.0"?>
<bookstore>
<book genre="autobiography" publicationdate="1981"
ISBN="1-861003-11-0">
<title>The Autobiography of Benjamin Franklin</title>
<author>
<first-name>Benjamin</first-name>
<last-name>Franklin</last-name>
</author>
<price>8.99</price>
</book>
<book genre="novel" publicationdate="1967"
ISBN="0-201-63361-2">
<title>The Confidence Man</title>
<author>
<first-name>Herman</first-name>
<last-name>Melville</last-name>
</author>
<price>11.99</price>
</book>
<book genre="philosophy" publicationdate="1991"
ISBN="1-861001-57-6">
<title>The Gorgias</title>
<author>
<first-name>Sidas</first-name>
<last-name>Plato</last-name>
</author>
<price>9.99</price>
</book>
</bookstore>'
Select T.Item.query('.')
From @xmlData.nodes('/bookstore/book') AS T(Item)
谁能解释我如何更正第一个脚本?我想用 xmlns 运行脚本.
Can anyone please explain how can I correct first scritp? I want to run script with xmlns.
推荐答案
正如您所说 - 这是因为您的原始 XML 数据位于 XML 命名空间中,如果是这种情况,您还需要在你的 XQuery:
As you say - it's because your original XML data is in a XML namespace, and if that's the case, you also need to make use of that XML namespace in your XQuery:
SELECT
T.Item.query('.')
FROM
@xmlData.nodes('declare namespace ns="http://myBooks";/ns:bookstore/ns:book')
AS T(Item)
您需要将该 declare namespace ns="http://myBooks";
部分插入到您的 XQuery 中,然后使用定义的名称空间前缀 ns
(您可以使用这里的任何东西,真的)引用 XML 对象.
You need to insert that declare namespace ns="http://myBooks";
part into your XQuery and then use the defined namespace prefix ns
(you can use anything here, really) to reference the XML objects.
马克
这篇关于SQL Server XML 数据类型查询问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!