count xmltype node

[Steve Jiang]

I need to count the number of node in a varchar2 variable.

The xml data like below, I hope to count how many node test_id in the xml file. In this xml data, we have 2 test_id (as 5 and 51). so I need to get 2 as result in a PL/SQL package.


I looked extract and extractvalue

<report db_version="11.2.0.0.1">

<report_id><![CDATA[/orarep/sqltune/auto_summary?section=task_stats]]></report_i
d>
<summary>
<statistics>
<task_stats>
<counts>

<stat name="executions">7</stat>
</counts>
<top_profiles>

<test_id>5</test_id>
<test_id>51</tets_id>
</top_profiles>

</task_stats>
</statistics>
</summary>
</report>


The blow query returns 551 as result, Do you have any suggestions to sepearte the data and get the count for the node number?


select nvl(XMLType(:rept).extract('//test_id/').getstringval(), '(NULL)') into :te from dual;



Thanks,
[ August 26, 2008: Message edited by: Steve Jiang ]