Error in fetching data from xmltype

Question

I am new to XMLTYPE data handling, so it may just be a very simple query which I am unable to figure out. Please let me know where I am wrong.

Error I am getting while trying with the below code.

ORA-06550: line 28, column 54:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 22, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 29, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

Here is my code.

DECLARE
     x XMLTYPE :=XMLTYPE('<?xml version="1.0" ?> 
                         <person>   
                         <row>       
                         <name>Tom</name>       
                         <Address>           
                         <State>California</State>           
                         <City>Los angeles</City>       
                         </Address>   
                         </row>   
                         <row>       
                         <name>Jim</name>       
                         <Address>           
                         <State>California</State>           
                         <City>Los angeles</City>       
                         </Address>   
                         </row>                         
                         </person>');
     l_city VARCHAR2(20);
     l_state VARCHAR2(20);
     l_name VARCHAR2(20);
BEGIN

     SELECT EXTRACT(VALUE(p),'//name/text()')          AS "Name",
         extract(value(p),'/Address//State/text()') AS "State",
         extract(value(p),'/Address//City/text()')  AS "City"
     FROM   dual,
         TABLE(XMLSEQUENCE(EXTRACT(x,'//person/row/'))) p;
END;

Answer 1

You may try below. Please note, in your case XMLSEQUENCE is giving you VARRAY of top level nodes, hence INTO alone may not suffice.

DECLARE
   TYPE l_obj IS RECORD
   (
      name    VARCHAR2 (200),
      state   VARCHAR2 (200),
      city    VARCHAR2 (200)
   );

   TYPE l_list_table IS TABLE OF l_obj;

   l_list   l_list_table;
   x        XMLTYPE := XMLTYPE ('<?xml version="1.0" ?> 
<person>   
<row>       
<name>Tom</name>       
<Address>           
<State>California</State>           
<City>Los angeles</City>       
</Address>   
</row>   
<row>       
<name>Jim</name>       
<Address>           
<State>California</State>           
<City>Los angeles</City>       
</Address>   
</row>
</person>');
BEGIN
   SELECT EXTRACTVALUE (VALUE (p), '//name/text()'),
          EXTRACTVALUE (VALUE (p), '//Address/State/text()'),
          EXTRACTVALUE (VALUE (p), '//Address/City/text()')
     BULK COLLECT INTO l_list --please use LIMIT clause
     FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/person/row/*'))) p;

   FOR idx IN l_list.FIRST .. l_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (l_list(idx).name ||', '|| l_list (idx).state || ', ' || l_list (idx).city);
   END LOOP;
END;