Announcement

Collapse
No announcement yet.

Daten gezielt aus XML-Struktur auslesen

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Daten gezielt aus XML-Struktur auslesen

    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
    Zuletzt editiert von jogi01; 09.11.2010, 13:41. Reason: fehlende informationen

  • #2
    Originally posted by jogi01 View Post
    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

    Comment


    • #3
      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

      Code:
      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/[email protected]_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:

      Code:
      <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/[email protected]_ID' aufgetreten
      Zuletzt editiert von jogi01; 09.11.2010, 15:25. Reason: Fehlermeldung gepostet

      Comment


      • #4
        Hier eine funktionierende Abfrage zum "Weiterbauen" auf Deinen Daten:
        Code:
        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

        Comment


        • #5
          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

          Comment


          • #6
            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...
            Code:
            <DATABASE>
             <SCHEMANAME>
              <TABLENAME GUTD_ID="51" GUTD_NUMBER="117" ...GUTD_DATE="2010-11-09">GUARD_TEST_DATA
              </TABLENAME>MYSCHEMA
             </SCHEMANAME>mydb
            </DATABASE>entw

            Comment


            • #7
              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.

              Comment


              • #8
                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):
                Code:
                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;

                Comment


                • #9
                  habe den aufbau umgestaltet... jetzt funzts

                  Code:
                  <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

                  Code:
                    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):
                  Code:
                   
                    p.l('Eintrag_zeit: '||v_xml.extract('/ROWSET/@EINTRAG_ZEIT').getstringval());
                  die einzelnen Daten im Forest so:
                  Code:
                    
                  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...

                  Comment

                  Working...
                  X