我正在尝试获取
XML字段中的节点数.但我总是看到0的结果.这是我的查询的样子.
@H_301_10@
我认为你的XPath表达式是错误的 – 尝试这样做：
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>
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
