Counting elements in XML based on search criteria
641220Jul 23 2010 — edited Jul 24 2010Hi again,
I been trying to return a count of <c> in a specific <subno> element. I tried the below query but never worked. I managed to do it using XMLTABLE ( '/IN-prov/Subscriber/Subno[@Id="9393983"]/Commands/C'..etc without the node exists function. It works.. but the problem is, its not useful because i cannot change the number dynamically. When I tried it inside a PLSQL code and replaced it with XMLTABLE ( '/IN-prov/Subscriber/Subno[@Id="'||NUMBER||'"]/Commands/C'.. it returned an error because it cannot accept anything else but a string.. so, I tried the below approach with is not working yet..
WITH Param_Xml AS (SELECT xmltype('<IN-prov>
<Subscriber>
<Subno Id="9393983">
<Commands>
<C>
<Head>Head 1</Head>
<Value>Value of head one</Value>
</C>
<C>
<Head>Head 2</Head>
<Value>Value of head 2</Value>
</C>
<C>
<Head>Head 3</Head>
<Value>Value of head 3</Value>
</C>
</Commands>
</Subno>
<Subno Id="9393981">
<Commands>
<C>
<Head>Head 1</Head>
<Value>Value of head one</Value>
</C>
<C>
<Head>Head 2</Head>
<Value>Value of head 2</Value>
</C>
<C>
<Head>Head 3</Head>
<Value>Value of head 3</Value>
</C>
</Commands>
</Subno>
</Subscriber>
</IN-prov>') AS Xmlcol FROM DUAL)
SELECT count(*)
FROM Param_Xml, XMLTABLE ( '/IN-prov/Subscriber/Subno/Commands/C'
PASSING Xmlcol
) U
where existsnode(xmlcol, '/IN-prov/Subscriber[Subno/@Id="9393981"=1
Appreciate the help :)