我正在尝试获取
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>
我认为你的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
- @XmlTable
