获取XML字段中的节点数XQuery SQL Server2008
获取XML字段中的节点数XQuery SQL Server2008
提问于 2011-03-26 05:38:21
我正在尝试获取XML字段中的节点数。但我总是看到0作为结果。下面是我的查询的样子。
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].usp_GetBooks @EditionId=400
--select * from @XmlTable
SELECT
--Count number of nodes
COUNT(*) AS BooksCount
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./books/book') XmlTableFunction(XmlColumn2);我的XML看起来像这样：
<Version number ="1">
<books>
<book>
<name> </name>
<author></author>
</book>
<book>
<name> </name>
<author></author>
</book>
</books>
</Version>回答 2
Stack Overflow用户
回答已采纳
发布于 2011-03-26 06:04:38
我认为您的XPath表达式是错误的-请尝试以下代码：
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].usp_GetBooks @EditionId=400
SELECT
COUNT(*) AS BooksCount
FROM
(SELECT XmlResult FROM @XmlTable) AS XmlTable(XmlColumn)
CROSS APPLY
XmlColumn.nodes('/Version/books/book') XmlTableFunction(XmlColumn2)或者更简单：
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].usp_GetBooks @EditionId=400
SELECT
XmlResult.value('count(/Version/books/book)', 'int')
FROM
@XmlTableStack Overflow用户
发布于 2011-03-26 05:50:39
对于您给出的XML模式，我很满意。
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable VALUES ('<books><book><title>GWTW</title></book></books>')
INSERT INTO @XmlTable VALUES ('<foo />')
INSERT INTO @XmlTable VALUES ('<books />')
SELECT
COUNT(*) AS BooksCount
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./books/book') XmlTableFunction(XmlColumn2);Exist方法也非常有用。我使用NULLIF将0更改为NULL (它是位，因此需要使用SUM进行转换)
SELECT COUNT(NULLIF(XmlResult.exist('./books/book'), 0)) FROM @XmlTable编辑，更新后
您发布的XML也是错误的。
您没有正确指定根注释：
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable VALUES ('
<Version number ="1">
<books>
<book>
<name> </name>
<author></author>
</book>
<book>
<name> </name>
<author></author>
</book>
</books>
</Version>')
SELECT
COUNT(*)
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('/Version/books/book') XmlTableFunction(XmlColumn2);
SELECT
COUNT(*)
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('*/books/book') XmlTableFunction(XmlColumn2);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5438391
复制相关文章
相似问题
腾讯云开发者
