В начале пути работы с XML

rvk
Дата: 06.06.2006 23:29:16
Здравствуйте.
Начинаю работать с XML. Надо разобрать XML в плоские таблицы. Версия ORACLE 9.2.0.7
ExtractValue() и XmlSequence() работают очень медленно, пробую парсер.

DROP TABLE in_xml;
CREATE TABLE in_xml (data_c clob);
INSERT INTO in_xml VALUES (
'<?xml version="1.0" encoding="windows-1251" ?>
<MAIN>
 <M_ID>123</M_ID>
 <D_BEG>01/01/2006</D_BEG>
 <D_END>30/06/2006</D_END>
 <DATAROWS> 
  <DATAROW> 
    <S_ID>1</S_ID>
    <S_FIO>ИВАНОВ</S_FIO>
    <PROJECTS>
      <PROJECT>
         <P_ID>2</P_ID>
         <P_SUM>1000</P_SUM>
      </PROJECT>
      <PROJECT>
         <P_ID>5</P_ID>
         <P_SUM>3000</P_SUM>
      </PROJECT>
      <PROJECT>
         <P_ID>8</P_ID>
         <P_SUM>1000</P_SUM>
      </PROJECT>
    </PROJECTS>
  </DATAROW> 
  <DATAROW> 
    <S_ID>2</S_ID>
    <S_FIO>ПЕТРОВ</S_FIO>
    <PROJECTS>
      <PROJECT>
         <P_ID>2</P_ID>
         <P_SUM>2000</P_SUM>
      </PROJECT>
      <PROJECT>
         <P_ID>4</P_ID>
         <P_SUM>8000</P_SUM>
      </PROJECT>      
    </PROJECTS>
  </DATAROW>
 </DATAROWS> 
</MAIN>');

DROP TABLE MAIN;
DROP TABLE DATAROW;
DROP TABLE PROJECT;

CREATE TABLE MAIN(M_ID number(5), D_BEG date, D_END date);
CREATE TABLE DATAROW(M_ID number(5),S_ID number(5), S_FIO varchar2(50));
CREATE TABLE PROJECT(M_ID number(5),S_ID number(5),P_ID number(2),P_SUM number(6,2) );

declare                     
  clob_data       clob ;  
  data_pars       dbms_xmlparser.parser;
  data_doc        dbms_xmldom.domdocument;
  data_nl         dbms_xmldom.domnodelist;
  data_n          dbms_xmldom.domnode;  
  type            type_main is table of main%ROWTYPE;
  main_tab        type_main :=type_main();
  type            type_drow is table of  datarow%ROWTYPE;
  drow_tab        type_drow :=type_drow();
  type            type_pr is table of  project%ROWTYPE;
  pr_tab          type_pr :=type_pr();  
begin
  select data_c into clob_data from in_xml where rownum=1;   
  data_pars := dbms_xmlparser.newparser;
  dbms_xmlparser.parseclob(data_pars, clob_data);
  data_doc := dbms_xmlparser.getdocument(data_pars);
  dbms_xmlparser.freeparser(data_pars);
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN');
  dbms_session.set_nls('nls_date_format','''dd/mm/yyyy''');  
  data_n := dbms_xmldom.item(data_nl, 0); 
   main_tab.extend;     
  dbms_xslprocessor.valueof(data_n,'M_ID',  main_tab(1).M_ID);
  dbms_xslprocessor.valueof(data_n,'D_BEG', main_tab(1).D_BEG);
  dbms_xslprocessor.valueof(data_n,'D_END', main_tab(1).D_END);
  INSERT  INTO main VALUES(main_tab(1).M_ID, main_tab(1).D_BEG, main_tab(1).D_END);
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN/DATAROWS/DATAROW'); 
  for i in 0 .. dbms_xmldom.getlength(data_nl)-1  loop
    data_n := dbms_xmldom.item(data_nl, i);   
    drow_tab.extend;   
    dbms_xslprocessor.valueof(data_n,'S_ID', drow_tab(i+1).S_ID);
    dbms_xslprocessor.valueof(data_n,'S_FIO',drow_tab(i+1).S_FIO ); 
    INSERT  INTO datarow VALUES (main_tab(1).M_ID, drow_tab(i+1).S_ID,  drow_tab(i+1).S_FIO );
  end loop;                            
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN/DATAROWS/DATAROW/PROJECTS/PROJECT');  
  for i in 0 .. dbms_xmldom.getlength(data_nl)-1  loop
    data_n := dbms_xmldom.item(data_nl, i); 
    pr_tab.extend;     
    dbms_xslprocessor.valueof(data_n,'P_ID', pr_tab(i+1).P_ID);
    dbms_xslprocessor.valueof(data_n,'P_SUM',pr_tab(i+1).P_SUM );      
    INSERT  INTO project(M_ID ,P_ID ,P_SUM) VALUES (main_tab(1).M_ID, pr_tab(i+1).P_ID,  pr_tab(i+1).P_SUM );
  end loop;                             
  dbms_xmlparser.freeParser(data_pars); 
end;
/
PL/SQL procedure successfully completed.

SELECT * FROM MAIN;
      M_ID D_BEG      D_END
---------- ---------- ----------
       123 01/01/2006 30/06/2006

SELECT * FROM DATAROW;
      M_ID       S_ID S_FIO
---------- ---------- --------------------------------
       123          1 ИВАНОВ
       123          2 ПЕТРОВ

SELECT * FROM PROJECT;
      M_ID       S_ID       P_ID      P_SUM
---------- ---------- ---------- ----------
       123                     2       1000
       123                     5       3000
       123                     8       1000
       123                     2       2000
       123                     4       8000
       

А хотелось, чтобы таблица PROJECT была заполнена следующим образом: поле S_ID бралось из соответствующего <DATAROW>
M_ID S_ID P_ID P_SUM
---------- ---------- ---------- ----------
123 1 2 1000
123 1 5 3000
123 1 8 1000
123 2 2 2000
123 2 4 8000

Не могу придумать что куда добавить. Подскажите, пожалуйста, как это можно сделать.
Заранее спасибо.
rvk
Дата: 07.06.2006 09:55:32
Здравствуйте еще раз.
Еще раз прошу совета более опытных товарищей.
Ловец Стрекоз
Дата: 07.06.2006 10:24:10
Из более опытных товарищей позволю процитировать Криса Дейта, а именно заметить следующее:
есть сущность Люди, сущность Проекты, между ними связь многие ко многим.
Внимание вопрос, можно ли смоделировать данный факт двумя таблицами???
rvk
Дата: 07.06.2006 11:23:38
Ловец Стрекоз
Из более опытных товарищей позволю процитировать Криса Дейта, а именно заметить следующее:
есть сущность Люди, сущность Проекты, между ними связь многие ко многим.
Внимание вопрос, можно ли смоделировать данный факт двумя таблицами???

Так тут не две таблицы. Как раз для отображения связи многие ко многим и нужно перенести ключ таблицы Люди в таблицу связей с Проектами.(ключ Проекта уже есть).
Ловец Стрекоз
Дата: 07.06.2006 12:06:41
Пардон за накат. Не стал особо разбираться увидив такие таблички. Всетаки лучше точнее описывать структуры данных. Иначе в дальнейшем будет худо...


У меня получилось так...
DROP TABLE in_xml;
CREATE TABLE in_xml (data_c clob);
INSERT INTO in_xml VALUES (
'<?xml version="1.0" encoding="windows-1251" ?>
<MAIN>
 <M_ID>123</M_ID>
 <D_BEG>01/01/2006</D_BEG>
 <D_END>30/06/2006</D_END>
 <DATAROWS> 
  <DATAROW> 
    <S_ID>1</S_ID>
    <S_FIO>ÈÂÀÍÎÂ</S_FIO>
    <PROJECTS>
      <PROJECT>
         <P_ID>2</P_ID>
         <P_SUM>1000</P_SUM>
      </PROJECT>
      <PROJECT>
         <P_ID>5</P_ID>
         <P_SUM>3000</P_SUM>
      </PROJECT>
      <PROJECT>
         <P_ID>8</P_ID>
         <P_SUM>1000</P_SUM>
      </PROJECT>
    </PROJECTS>
  </DATAROW> 
  <DATAROW> 
    <S_ID>2</S_ID>
    <S_FIO>ÏÅÒÐÎÂ</S_FIO>
    <PROJECTS>
      <PROJECT>
         <P_ID>2</P_ID>
         <P_SUM>2000</P_SUM>
      </PROJECT>
      <PROJECT>
         <P_ID>4</P_ID>
         <P_SUM>8000</P_SUM>
      </PROJECT>      
    </PROJECTS>
  </DATAROW>
 </DATAROWS> 
</MAIN>');

DROP TABLE MAIN;
DROP TABLE DATAROW;
DROP TABLE PROJECT;

CREATE TABLE MAIN(M_ID number(5), D_BEG date, D_END date);
CREATE TABLE DATAROW(M_ID number(5),S_ID number(5), S_FIO varchar2(50));
CREATE TABLE PROJECT(M_ID number(5),S_ID number(5),P_ID number(2),P_SUM number(6,2) );

declare                     
  clob_data       clob ;  
  data_pars       dbms_xmlparser.parser;
  data_doc        dbms_xmldom.domdocument;
  data_xl         dbms_xmldom.domnodelist;
  data_nl         dbms_xmldom.domnodelist;
  data_x          dbms_xmldom.domnode;  
  data_n          dbms_xmldom.domnode;  
  type            type_main is table of main%ROWTYPE;
  main_tab        type_main :=type_main();
  type            type_drow is table of  datarow%ROWTYPE;
  drow_tab        type_drow :=type_drow();
  drow_x_tab      type_drow :=type_drow();
  type            type_pr is table of  project%ROWTYPE;
  pr_tab          type_pr :=type_pr();  
begin
  select data_c into clob_data from in_xml where rownum=1;   
  data_pars := dbms_xmlparser.newparser;
  dbms_xmlparser.parseclob(data_pars, clob_data);
  data_doc := dbms_xmlparser.getdocument(data_pars);
  dbms_xmlparser.freeparser(data_pars);
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN');
  dbms_session.set_nls('nls_date_format','''dd/mm/yyyy''');  
  data_n := dbms_xmldom.item(data_nl, 0); 
   main_tab.extend;     
  dbms_xslprocessor.valueof(data_n,'M_ID',  main_tab(1).M_ID);
  dbms_xslprocessor.valueof(data_n,'D_BEG', main_tab(1).D_BEG);
  dbms_xslprocessor.valueof(data_n,'D_END', main_tab(1).D_END);
  INSERT  INTO main VALUES(main_tab(1).M_ID, main_tab(1).D_BEG, main_tab(1).D_END);
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN/DATAROWS/DATAROW'); 
  for i in 0 .. dbms_xmldom.getlength(data_nl)-1  loop
    data_n := dbms_xmldom.item(data_nl, i);   
    drow_tab.extend;   
    dbms_xslprocessor.valueof(data_n,'S_ID', drow_tab(i+1).S_ID);
    dbms_xslprocessor.valueof(data_n,'S_FIO',drow_tab(i+1).S_FIO ); 
    INSERT  INTO datarow VALUES (main_tab(1).M_ID, drow_tab(i+1).S_ID,  drow_tab(i+1).S_FIO );
  end loop;
  data_xl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN/DATAROWS/DATAROW');  
  for j in 0 .. dbms_xmldom.getlength(data_xl)-1  loop  
  data_x := dbms_xmldom.item(data_xl, j);  
  drow_x_tab.extend;
  dbms_xslprocessor.valueof(data_x,'S_ID', drow_x_tab(j+1).S_ID);                       
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN/DATAROWS/DATAROW[S_ID='||drow_x_tab(j+1).S_ID||']/PROJECTS/PROJECT');  
  for i in 0 .. dbms_xmldom.getlength(data_nl)-1  loop
    data_n := dbms_xmldom.item(data_nl, i); 
    pr_tab.extend;     
    dbms_xslprocessor.valueof(data_n,'P_ID', pr_tab(i+1).P_ID);
    dbms_xslprocessor.valueof(data_n,'P_SUM',pr_tab(i+1).P_SUM );      
    INSERT  INTO project(M_ID ,S_ID, P_ID ,P_SUM) VALUES (main_tab(1).M_ID, drow_x_tab(j+1).S_ID, pr_tab(i+1).P_ID,  pr_tab(i+1).P_SUM );
  end loop; 
  end loop;                            
  dbms_xmlparser.freeParser(data_pars); 
end;
/


SELECT * FROM MAIN;


SELECT * FROM DATAROW;


SELECT * FROM PROJECT;

rvk
Дата: 07.06.2006 23:27:11
2 Ловец Стрекоз
Спасибо, завтра попробую Ваш вариант.
У меня еще другая заморочка получается
Версия ORACLE 9.2.0.7

DROP TABLE in_xml;
CREATE TABLE in_xml (data_c clob);
INSERT INTO in_xml VALUES (
'<?xml version="1.0" encoding="windows-1251" ?>
<MAIN>
 <M_ID>123</M_ID>
 <D_BEG>01/01/2006</D_BEG>
 <D_END>30/06/2006</D_END>
 <DATAROWS> 
  <DATAROW> 
    <S_ID>1</S_ID>
    <S_FIO>ИВАНОВ</S_FIO>
     <PROJECTS>
      <PROJECT>
         <P_ID>8</P_ID>
         <P_SUM>1000</P_SUM>
      </PROJECT>
    </PROJECTS>
  </DATAROW> 
  <DATAROW> 
    <S_ID>2</S_ID>
    <S_FIO>ПЕТРОВ</S_FIO>
    <PROJECTS/>     
  </DATAROW>
 </DATAROWS> 
</MAIN>');

DROP TABLE MAIN;
DROP TABLE DATAROW;
DROP TABLE PROJECT;

CREATE TABLE MAIN(M_ID number(5), D_BEG date, D_END date);
CREATE TABLE DATAROW(M_ID number(5),S_ID number(5), S_FIO varchar2(50));
CREATE TABLE PROJECT(M_ID number(5),S_ID number(5),P_ID number(2),P_SUM number(6,2) );

declare                       
  data_xml xmltype;
  m_id_ number;
begin
  select xmltype(data_c) into data_xml from in_xml where rownum=1;   
  
  INSERT  INTO main 
   Values( EXTRACTVALUE(data_xml, 'MAIN/M_ID') , EXTRACTVALUE(data_xml, 'MAIN/D_BEG'),  EXTRACTVALUE(data_xml, 'MAIN/D_END'))
   Returning M_ID into m_id_;    
 
  INSERT  INTO datarow 
   select M_ID_, EXTRACTVALUE(value(s), '/DATAROW/S_ID'),   EXTRACTVALUE(value(s), '/DATAROW/S_FIO') 
    from TABLE (XMLSEQUENCE(EXTRACT(data_xml, 'MAIN/DATAROWS/DATAROW'))) s;  
end; 
/
declare
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at line 11
А если зделать так, то работает:
select  substr(EXTRACTVALUE(value(s), '/DATAROW/S_ID'),1,5), substr(  EXTRACTVALUE(value(s), '/DATAROW/S_FIO'),1,10) 
 from in_xml ,TABLE (XMLSEQUENCE(EXTRACT(xmltype(data_c), 'MAIN/DATAROWS/DATAROW'))) s;
 
 SUBST SUBSTR(EXT
----- ----------
1     ИВАНОВ
2     ПЕТРОВ
И как с этим бороться?
Ловец Стрекоз
Дата: 08.06.2006 10:44:26
Я попытался отладить данный код под 9.2.0.7 EE SunOS

INSERT  INTO datarow 
   select M_ID_, EXTRACTVALUE(value(s), '/DATAROW/S_ID'),   EXTRACTVALUE(value(s), '/DATAROW/S_FIO') 
    from TABLE (XMLSEQUENCE(EXTRACT(data_xml, 'MAIN/DATAROWS/DATAROW'))) s;
подобные конструкции валятся с ORA-22905


select  substr(EXTRACTVALUE(value(s), '/DATAROW/S_ID'),1,5), substr(  EXTRACTVALUE(value(s), '/DATAROW/S_FIO'),1,10) 
 from in_xml ,TABLE (XMLSEQUENCE(EXTRACT(xmltype(data_c), 'MAIN/DATAROWS/DATAROW'))) s;
подобные с ORA-03113, может знаете как избавиться от этого?

вместе с тем, под 10gR2, все Ok.
Ловец Стрекоз
Дата: 08.06.2006 15:44:41
rvk

Начинаю работать с XML. Надо разобрать XML в плоские таблицы. Версия ORACLE 9.2.0.7
ExtractValue() и XmlSequence() работают очень медленно, пробую парсер.

Перечисленные технологии не последнее слово техники. Лично мое мнение, реализованное в только вот защищенном дипломе, следующее

XMLType---(функция toObject)--->Object Tables---(instead of insert)--->Flat Tables
rvk
Дата: 09.06.2006 10:54:36
Ловец Стрекоз
Лично мое мнение, реализованное в только вот защищенном дипломе, следующее

XMLType---(функция toObject)--->Object Tables---(instead of insert)--->Flat Tables

А можно примерчик?

Ловец Стрекоз
Я попытался отладить данный код под 9.2.0.7 EE SunOS


INSERT INTO datarow
select M_ID_, EXTRACTVALUE(value(s), '/DATAROW/S_ID'), EXTRACTVALUE(value(s), '/DATAROW/S_FIO')
from TABLE (XMLSEQUENCE(EXTRACT(data_xml, 'MAIN/DATAROWS/DATAROW'))) s;

подобные конструкции валятся с ORA-22905


Так получаетя, что XMLSEQUENCE не работает с переменной, только с полем таблицы. Я попробовала подставить поле, работает но дого.

declare                       
  data_xml xmltype;
  m_id_ number;
ri varchar2(30);
begin
  select xmltype(data_c),rowid into data_xml,ri from in_xml where rownum=1;   
  
  INSERT  INTO main 
   Values( EXTRACTVALUE(data_xml, 'MAIN/M_ID') , EXTRACTVALUE(data_xml, 'MAIN/D_BEG'),  EXTRACTVALUE(data_xml, 'MAIN/D_END'))
   Returning M_ID into m_id_;    
 
  INSERT  INTO datarow 
   select M_ID_, EXTRACTVALUE(value(s), '/DATAROW/S_ID'),   EXTRACTVALUE(value(s), '/DATAROW/S_FIO') 
    from TABLE (XMLSEQUENCE(EXTRACT((select xmltype(data_c) from in_xml where rowid=ri), 'MAIN/DATAROWS/DATAROW'))) s;  
end; 
/


Ловец Стрекоз

select substr(EXTRACTVALUE(value(s), '/DATAROW/S_ID'),1,5), substr( EXTRACTVALUE(value(s), '/DATAROW/S_FIO'),1,10)
from in_xml ,TABLE (XMLSEQUENCE(EXTRACT(xmltype(data_c), 'MAIN/DATAROWS/DATAROW'))) s;

подобные с ORA-03113, может знаете как избавиться от этого?

вместе с тем, под 10gR2, все Ok.

Такая конструкция у меня и в 9 и в 10 работает, т.к. используется таблица.
Правда в примере тип поля CLOB, может сразу попробывать xmltype?
rvk
Дата: 09.06.2006 11:05:26
А еще обнаружила если в вышеуказанной процедуре попытаться извлечь данные из XML в переменную не той длины выдаются разные ошибки

DROP TABLE in_xml;
CREATE TABLE in_xml (data_c clob);
INSERT INTO in_xml VALUES (
'<?xml version="1.0" encoding="windows-1251" ?>
<MAIN>
 <M_ID>123</M_ID>
 D_BEG>zzz</D_BEG>
 <D_END>30/06/2006</D_END>
 <DATAROWS> 
  <DATAROW> 
    <S_ID>1</S_ID>
    <S_FIO>ИВАНОВ</S_FIO>
    <PROJECTS>
      <PROJECT>
         <P_ID>2</P_ID>
         <P_SUM>1000</P_SUM>
      </PROJECT>      
    </PROJECTS>
  </DATAROW>   
 </DATAROWS> 
</MAIN>');

DROP TABLE MAIN;

CREATE TABLE MAIN(M_ID number(5), D_BEG date, D_END date);


declare                     
  clob_data       clob ;  
  data_pars       dbms_xmlparser.parser;
  data_doc        dbms_xmldom.domdocument;
  data_nl         dbms_xmldom.domnodelist;
  data_n          dbms_xmldom.domnode;  
  type            type_main is table of main%ROWTYPE;
  main_tab        type_main :=type_main();  
begin
  select data_c into clob_data from in_xml where rownum=1;   
  data_pars := dbms_xmlparser.newparser;
  dbms_xmlparser.parseclob(data_pars, clob_data);
  data_doc := dbms_xmlparser.getdocument(data_pars);
  dbms_xmlparser.freeparser(data_pars);
  data_nl := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(data_doc),'MAIN');
  dbms_session.set_nls('nls_date_format','''dd/mm/yyyy''');  
  data_n := dbms_xmldom.item(data_nl, 0); 
   main_tab.extend;     
  dbms_xslprocessor.valueof(data_n,'M_ID',  main_tab(1).M_ID);
  dbms_xslprocessor.valueof(data_n,'D_BEG', main_tab(1).D_BEG);
  dbms_xslprocessor.valueof(data_n,'D_END', main_tab(1).D_END);
  INSERT  INTO main VALUES(main_tab(1).M_ID, main_tab(1).D_BEG, main_tab(1).D_END);   
  dbms_xmlparser.freeParser(data_pars); 
end;
/
При попытке вставить 'D_BEG' выдается:
В 9.2.0.7 Oracle
ORA-31100: XDB Locking Internal Error

В 10 Oracle
ORA-24331: user buffer too small

А если будет 9.2.0.5 или 9.2.0.6 будет другя ошибка? Кто-нибудь знает?