oracle - 从 xmltype 获取数据时出错

Question

关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。












想改进这个问题？将问题更新为 on-topic对于堆栈溢出。

8年前关闭。




Improve this question




我是 XMLTYPE 的新手数据处理，所以它可能只是一个我无法弄清楚的非常简单的查询。请让我知道我错在哪里。

尝试使用以下代码时遇到错误。

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:

这是我的代码。

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

你可以试试下面。请注意，在您的情况下，XMLSEQUENCE 为您提供顶级节点的 VARRAY，因此仅 INTO 可能还不够。

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;