获取XML字段XQuery SQL Server 2008中的节点数

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>

2 个解决方案

#1

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

#2

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（它有点因此需要使用SUM进行CAST）

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);

#1