获取 XML 字段中的节点数 XQuery SQL Server 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5438391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the count of nodes in an XML field XQuery SQL Server 2008
提问by BumbleBee
I am trying to get the count of nodes in an XML field. but I always see 0 as the result. Here is how my query looks like.
我正在尝试获取 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);
My XML Looks like :
我的 XML 看起来像：
<Version number ="1">
<books>
<book>
<name> </name>
<author></author>
</book>
<book>
<name> </name>
<author></author>
</book>
</books>
</Version>
回答by marc_s
I think your XPath expression is wrong - try this instead:
我认为你的 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)
Or even simpler:
或者更简单：
DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].usp_GetBooks @EditionId=400
SELECT
XmlResult.value('count(/Version/books/book)', 'int')
FROM
@XmlTable
回答by gbn
Works for me with the XML pattern you give
使用您提供的 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 method is quite useful too. I use NULLIF to change 0 to NULL (it is bit so would need CAST with SUM)
Exist 方法也非常有用。我使用 NULLIF 将 0 更改为 NULL（它有点需要 CAST 和 SUM）
SELECT COUNT(NULLIF(XmlResult.exist('./books/book'), 0)) FROM @XmlTable
Edit, after update
编辑，更新后
The XML you posted is wrong too.
您发布的 XML 也是错误的。
You are not specifying the root note correctly:
您没有正确指定根音：
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);
