Daten gezielt aus XML-Struktur auslesen

[jogi01]

Oracle Version 10.2.

hallo zusammen,

folgende struktur:

<DATABASE>
<SCHEMANAME>
<TABLENAME
GUTD_ID="40"
GUTD_NUMBER="117"
GUTD_VARCHAR="test no 09.11.10 12:23:43,653521 +01:00"
GUTD_DATE="2010-11-09"
>GUARD_TEST_DATA
</TABLENAME>myschema
</SCHEMANAME>mydb
</DATABASE>

nun möchte ich gezielt einzelne werte mit PL/SQL auslesen können.

Hintergrund:
ich erhalte über eine Qeue diese XML-Struktur.
anhand der einträge "DATENBANK", "SCHEMANAME" und "TABELNAME" erfahre ich die Herkunft der Daten. auf basis dieser informationen entsprechend für einige Values weiterverarbeitungen anstossen.

habe schon mit extract und extractvalues experimentiert. komme aber nicht wirklich weiter.

vielen dank für eure hilfe.

jogi

[dbwizard]

Oracle Version 10.2.

hallo zusammen,

folgende struktur:


habe schon mit extract und extractvalues experimentiert. komme aber nicht wirklich weiter.

vielen dank für eure hilfe.

jogi

Hallo,

Kannst du a) das "Komplette" XML posten, also inkl evtl. vorhandenen Namespaces etc und b) deine "Versuche", also wie hast du es mit XPATH / XQUERY versucht ?


Danke und Gruss

[jogi01]

Code

Dieser trigger baut das XML-Zusammen und verschickt das Entstandene XML mit einer Qeue.
Ich experimentiere innerhalb des triggers, da ich ja beim qeueing auch keine tabelle als datenbasis habe.

P.L entspricht DBMS_OUTPUT.PUT_LINE

CREATE OR REPLACE TRIGGER GUTD_TRARI_XML AFTER INSERT ON GUARD_TEST_DATA FOR EACH ROW
declare

  v_xml XMLType;
  v_dummy varchar2(100) DEFAULT 'NICHT FINDBAR';
  v_test_rec GUARD_TEST_DATA%rowtype;

BEGIN 

SELECT XMLELEMENT ("EINTRAG_ZEIT",
       XMLELEMENT ("SERVER_HOST",
       XMLELEMENT ("DATABASE",
       XMLELEMENT ("SCHEMANAME",
       XMLELEMENT ("TABLENAME",
                   xmlattributes (:new.GUTD_ID  AS "GUTD_ID"
                                 ,:new.GUTD_NUMBER  AS "GUTD_NUMBER"
                                 ,:new.GUTD_VARCHAR AS "GUTD_VARCHAR"
                                 ,:new.GUTD_DATE AS "GUTD_DATE" 
                                 ),
                   'GUARD_TEST_DATA'
                  )
                  ,'MYSCHEMA'
                  )
                  ,sys_context('USERENV', 'DB_NAME')
                  )
                  ,sys_context('USERENV', 'SERVER_HOST')
                  )
                  ,current_timestamp
                  )
  INTO v_xml
  FROM dual;


  -- 1. Versuch
  p.l(v_xml.extract('/EINTRAG_ZEIT/SERVER_HOST/DATABASE').getstringval());
  
  p.l('********************** Einzelnen Wert Extract *****************');
  begin
    p.l(v_xml.extract('/EINTRAG_ZEIT/SERVER_HOST/DATABASE/SCHEMANAME/TABLENAME/GUTD_ID').getstringval());
  exception
    when others then
      p.l(sqlerrm);
  end;
  
  
  -- 2. Versuch 
  p.l('********************** Einzelnen Wert mit ExtractValue *****************');
  begin
    SELECT nvl(extractvalue(v_xml,'/EINTRAG_ZEIT/SERVER_HOST/DATABASE/SCHEMANAME/TABLENAME@GUTD_ID'),'{NULL}')
      INTO v_dummy
      from dual;
    p.l(v_dummy);
  exception
    when others then
      p.l(sqlerrm);
  end;
  
END;
/

der inhalt von v_xml stellt sich so dar (1:1 Ausgabe mit DBMS_OUTPUT:

<EINTRAG_ZEIT>
  <SERVER_HOST>
    <DATABASE>
      <SCHEMANAME>
        <TABLENAME GUTD_ID="51" GUTD_NUMBER="117" GUTD_VARCHAR="test no 09.11.10 14:17:19,078438 +01:00" GUTD_DATE="2010-11-09">GUARD_TEST_DATA</TABLENAME>MYSCHEMA
      
</SCHEMANAME>mydb
    </DATABASE>entw
  </SERVER_HOST>2010-11-09T14:17:19.128004
</EINTRAG_ZEIT>

diese fehler erhalte ich:
********************** Einzelnen Wert Extract *****************
ORA-30625: Methoden-Dispatch mit Argument NULL SELF ist nicht zulässig
********************** Einzelnen Wert mit ExtractValue *****************
ORA-31011: XML-Parsing nicht erfolgreich
ORA-19202: Fehler bei XML-Verarbeitung
LPX-00601: Invalid token in: '/EINTRAG_ZEIT/SERVER_HOST/DATABASE/SCHEMANAME/TABLENAME@GUTD_ID' aufgetreten

[jum]

Hier eine funktionierende Abfrage zum "Weiterbauen" auf Deinen Daten:

WITH xdata AS 
 (SELECT XMLtype ('
    <EINTRAG_ZEIT>
     <SERVER_HOST>
      <DATABASE>
       <SCHEMANAME>
         <TABLENAME GUTD_ID="51" GUTD_NUMBER="117" GUTD_VARCHAR="test no 09.11.10 14:17:19,078438 +01:00" GUTD_DATE="2010-11-09">GUARD_TEST_DATA</TABLENAME>MYSCHEMA
       </SCHEMANAME>mydb
      </DATABASE>entw
    </SERVER_HOST>2010-11-09T14:17:19.128004
   </EINTRAG_ZEIT>') xml FROM dual) 
SELECT  
  extractvalue(xml,'//TABLENAME')  tabnam
 FROM xdata;
TABNAM                                                                          
--------------------------------------------
GUARD_TEST_DATA                                                                 
1 row selected.

Die Struktur der XML ist allerdings etwas ungewöhnlich

[jogi01]

Die Struktur der XML ist allerdings etwas ungewöhnlich

Warum?
also ich bin zwar fit in PL/SQL jedoch XML ist echt neuland für mich.

was wäre denn deiner meinung nach "gewöhnlicher"?
bin für vorschläge offen.
diese lösung ist nur entstanden da wir die datenherkunft mitliefern müssen, und wir hier der meinung waren dies "so" darzustellen.

danke.

jogi

[jum]

Irgendwie scheinen der <SCHEMANAME> MYSCHEMA und die <DATABASE> mydb usw. "in der Luft zu hängen" , sie folgen dem schließenden Tag, aber ich bin kein XML-Experte...

<DATABASE>
 <SCHEMANAME>
  <TABLENAME GUTD_ID="51" GUTD_NUMBER="117" ...GUTD_DATE="2010-11-09">GUARD_TEST_DATA
  </TABLENAME>MYSCHEMA
 </SCHEMANAME>mydb
</DATABASE>entw

[jogi01]

ja das fiel mir auch gleich auf und ich steige da auch nicht wirklich durch.
dabei benutze ich ja XMLELEMENT und xmlattributes.
bin dem aber nicht weiter nachgegangen.

wobei ich aber auch sagen muss dass ich bald schon dazu neige keinen XMLTYPE mehr zu nehmen sondern einen CLOB und baller (sorry) schreibe die informationen mit nem definierten trenner (z.B. #) dort hinnein.

das erscheint mir bald einfacher als die TAGs aus dem XML-Type rauszuholen.

bin vor beginn den programmierarbeiten echt davon ausgegangen irgendein DBMS Package vorzufinden mit dem man "gescheidig" die daten aus solch einer blöden struktur fischen kann.


eine frage nochmal zu deinem statement:

die ablauflogik ist mir schon klar, aber sobald ich '//TABLENAME' duchr ein anderes attribut in dem XML ersetze erhalte ich entweder NULL oder nen error.

ich schnall einfach nicht wie das adressieren eines TAGs funzt.

danke noch mal.

[jum]

XML in ORACLE ist schon sehr leistungsfähig und flexibel, ich denke die Entscheidung dafür ist sicher nicht unbegründet. Die Theorie dazu findet sich wie meist im Feinen Manual , ohne wirds vermutlich nicht gehen... Hier speziell sind identisch (von unten relativer, bis ganz oben absoluter Pfad):

WITH xdata AS 
 (SELECT XMLtype ('
    <EINTRAG_ZEIT>
     <SERVER_HOST>
      <DATABASE>
       <SCHEMANAME>
         <TABLENAME GUTD_ID="51" GUTD_NUMBER="117" GUTD_VARCHAR="test no 09.11.10 14:17:19,078438 +01:00" GUTD_DATE="2010-11-09">GUARD_TEST_DATA</TABLENAME>MYSCHEMA
       </SCHEMANAME>mydb
      </DATABASE>entw
    </SERVER_HOST>2010-11-09T14:17:19.128004
   </EINTRAG_ZEIT>') xml FROM dual) 
SELECT  
   extractvalue(xml,'//TABLENAME')  tabnam,
   extractvalue(xml,'//SCHEMANAME/TABLENAME')  tab1,
   extractvalue(xml,'/EINTRAG_ZEIT/SERVER_HOST/DATABASE/SCHEMANAME/TABLENAME')  tab2
 FROM xdata;

[jogi01]

habe den aufbau umgestaltet... jetzt funzts

<ROWSET 
       EINTRAG_ZEIT="2010-11-10T16:03:36.870693" 
       SERVER_HOST="entw" 
       DATABASE="mydb" 
       SCHEMANAME="MYSCHEMA" 
       TABLENAME="GUARD_TEST_DATA">
  <GUTD_ID>57</GUTD_ID>
  <GUTD_NUMBER>117</GUTD_NUMBER>
  <GUTD_VARCHAR>test no 10.11.10 16:03:36,870131 +01:00</GUTD_VARCHAR>
  <GUTD_DATE>2010-11-10</GUTD_DATE>
</ROWSET>

mit diesem aufbau

  SELECT XMLElement("ROWSET", 
                       xmlattributes (current_timestamp as "EINTRAG_ZEIT"
                                     ,sys_context('USERENV', 'SERVER_HOST') AS "SERVER_HOST"
                                     ,sys_context('USERENV', 'DB_NAME') AS "DATABASE"
                                     ,'MYSCHEMA' AS "SCHEMANAME"
                                     ,'GUARD_TEST_DATA' AS "TABLENAME"
                                     ),
                           xmlforest (:new.GUTD_ID AS GUTD_ID
                                     ,:new.GUTD_NUMBER AS GUTD_NUMBER
                                     ,:new.GUTD_VARCHAR AS GUTD_VARCHAR
                                     ,:new.GUTD_DATE AS GUTD_DATE
                                     )
                      )
      INTO v_xml
      FROM dual;

nun können die Attributes und die Daten im Forest verhältnismäßig leicht ausgelesen werden (p.l entspricht dbms_output.put_line):

 
  p.l('Eintrag_zeit: '||v_xml.extract('/ROWSET/@EINTRAG_ZEIT').getstringval());

die einzelnen Daten im Forest so:

  
select EXTRACTVALUE(v_xml, '/ROWSET/GUTD_ID')
    into v_value
    from dual;

Leider kann EXTRACTVALUE nicht als klassische funktion aufgerufen werden, warum auch immer. daher das SELECT.

naja ist vllt. nicht die eleganteste lösung, aber sie rennt und ist lesbar.

für meine nächste frage mache ich nen neuen thread auf...

danke für die hilfe...