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>

Douro

What does the XML look like? zero means you no rows from the CROSS APPLY... – Vanda

@gbn. when I do select * from @XmlTable I see atleast 3 book elements for EditionId = 400 – Douro

Your XML sample is not valid - the first <book> ought to close with a </book> (it doesn't - right now), and the <Version number =1> is not valid - the 1 needs to be in quotes: <Version number="1"> – Housework

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

Housework

Answer 2

Works for me with the XML pattern you give

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)

SELECT COUNT(NULLIF(XmlResult.exist('./books/book'), 0)) FROM @XmlTable

Edit, after update

The XML you posted is wrong too.

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

Vanda