Get the count of nodes in an XML field XQuery SQL Server 2008

Question

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.

 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 :

<Version number ="1"> 
<books>
<book>
  <name> </name>
  <author></author>
</book>
<book>
  <name> </name>
  <author></author>
</book>
</books>
</Version>

Answer 1

I think your XPath expression is wrong - try this instead:

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