PL/SQL: Count number of nodes in xml
I would like to be able to save the result as a variable to be used as an upper limit for a loop iterator.
I guess this is related to your another question ?
You don't need to know the number of nodes as your don't have to explicitly loop xml by yourself. You might be trying to solve your real problem in a suboptimal way.
Below how you can find the number you're looking for with XMLQUERY:
declare
v_data constant xmltype := xmltype('<row>
<date name="date1" id="101"></date>
<element1 name="ele1" id="111">
<stuff></stuff>
<stuff></stuff>
<stuff></stuff>
</element1>
<element2 name="ele2" id="121"></element2>
<element3 name="ele3" id="131"></element3>
</row>');
v_count xmltype;
begin
select xmlquery('count($doc/row/descendant::*)'
passing v_data as "doc"
returning content)
into v_count from dual;
dbms_output.put_line('count = ' || v_count.getstringval);
end;
/
quotemyname
Updated on June 17, 2022Comments
-
quotemyname almost 2 years
I am working with Oracle.
Is there a way to count the number of nodes (including descendants) within an XML file using PL/SQL?
I would like to be able to save the result as a variable to be used as an upper limit for a loop iterator.
I have the following xml, and I want to count the number of nodes within the row node:
<row> <date name="date1" id="101"></date> <element1 name="ele1" id="111"> <stuff></stuff> <stuff></stuff> <stuff></stuff> </element1> <element2 name="ele2" id="121"></element2> <element3 name="ele3" id="131></element15> </row>
The result should be 7.
@johnbk I am working with Oracle
The idea here is that after I get the number of nodes I can use it in:
nodeCount := 1; FOR i IN 1 .. numNodes LOOP xpath1 := '/row/*[' || nodeCount || ']/@name'; SELECT EXTRACT(form_xml, xpath1) as other_name; nodeCount := nodeCount +1; END LOOP;
Thanks for your help.
-
quotemyname over 12 yearsJust curious, so I can understand the language better, what would you do if I had to query to get the v_data and cannot 'declare' it? It's coming in off a query that grabs the info from a database. Would you SELECT table.data INTO v_data?
-
user272735 over 12 years@quotemyname: That is one option but you can get XML data also directly from database table. This gets the XML data from table
so11t
columnxmldata
whereso11t.id = 1
:select xmlquery('count($doc/row/descendant::*)' passing t.xmldata as "doc" returning content) into v_count from so11t t where id = 1;