Home » SQL & PL/SQL » SQL & PL/SQL » how to travrse xml which come from webservice
how to travrse xml which come from webservice [message #185663] Wed, 02 August 2006 23:37 Go to next message
jilhewar_santosh
Messages: 13
Registered: July 2006
Location: mumbai
Junior Member
in my procedure xml come as varchar like
<root>
<record>
<name>a</name>
<age>20</age>
</record>
<record>
<name>b</name>
<age>40</age>
</record>
:
:
:
now i want traverse this xml in a loop so how i do in pl/sql

Re: how to travrse xml which come from webservice [message #185678 is a reply to message #185663] Thu, 03 August 2006 01:26 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
You haven't mentioned specifically what you need to do with the XML. Read the XML DB documentation, that should guide you to several ways to do this.

This example uses SQL/XML for traversal:

SQL> DECLARE
  2    v VARCHAR2(32000) :=
  3  '<root><record>
  4  <name>a</name>
  5  <age>20</age>
  6  </record>
  7  <record>
  8  <name>b</name>
  9  <age>40</age>
 10  </record></root>';
 11    x           XMLType;
 12    countnode   NUMBER(4);
 13  BEGIN
 14    x := XMLType(v);
 15  
 16  -- Get count of records in the XML
 17  SELECT COUNT(ve.EXTRACT('/*').getStringVal()) INTO countnode
 18  FROM TABLE(XMLSEQUENCE(EXTRACT(x,'/root/record'))) ve;
 19  
 20  DBMS_OUTPUT.put_line('No of elements: '||countnode);
 21  
 22  -- Extract and display each record
 23  FOR i IN 1..countnode LOOP
 24    DBMS_OUTPUT.put_line('Node '||i||': ');
 25    DBMS_OUTPUT.put_line(x.extract('/root/record[' ||i|| ']').getStringVal());
 26  END LOOP;
 27  
 28  END;
 29  /
No of elements: 2
Node 1:
<record>
  <name>a</name>
  <age>20</age>
</record>

Node 2:
<record>
  <name>b</name>
  <age>40</age>
</record>

PL/SQL procedure successfully completed.
Previous Topic: I know column name but dont know from which table
Next Topic: sql loader count in a variable
Goto Forum:
  


Current Time: Sat Dec 03 21:57:12 CST 2016

Total time taken to generate the page: 0.09386 seconds