XML-Oracle : Mehrere Kind-Knoten extrahieren

Ich habe ein xml-code :

<begin>
    <entry>
        <lastname>gordon</lastname>
        <NumberList>
            <number>100</number>
            <codelist>
                 <code>213</code>
                 <code>214</code>
            <codelist>
            <login>
                 <user>user1</user>
                 <user>user2</user>
            </login>
        <NumberList>
        <address>
            <addresslist>Jl. jalan pelan-pelan ke Bekasi, Indonesia</addresslist>
        </address>
    </entry>
    <entry>
        <lastname>mark</lastname>
        <address>
            <addresslist>Jl. jalan cepet-cepet ke Jakarta, Indonesia</addresslist>
        </address>
    </entry>
</begin>

mein code:

FOR r IN (SELECT VALUE(p) col_val,
                 EXTRACT(VALUE(P), '/entry/codelist') AS code,
                 EXTRACT(VALUE(P), '/entry/login') AS login
           FROM TABLE(XMLSequence(Extract(x,'/begin/entry'))) p)
LOOP
   IF r.col_val.existsnode('/entry/lastname/text()') > 0 
   THEN
      vc_lastname := r.col_val.extract('/sdnEntry/lastname/text()').getstringval();
   END IF;

   IF r.col_val.existsnode('/entry/address/addresslist/text()') > 0 
   THEN
    vc_address := r.col_val.extract('/sdnEntry/address/addresslist/text()').getstringval();
   END IF;

   IF r.col_val.existsnode('/entry/codelist/id/code/text()') > 0 AND r.col_val.existsnode('/entry/login/user/text()') > 0 
   THEN
      FOR R1 IN (SELECT EXTRACTVALUE(VALUE(T1), '/codelist/code/text()') AS code
                   FROM TABLE(XMLSEQUENCE(EXTRACT(R.code, '/codelist'))) T1)
      LOOP
         DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R1.code||' - '||R2.address);
      END LOOP;

      FOR R2 IN (SELECT
                        EXTRACTVALUE(VALUE(T1), '/login/user/text()') AS user
                   FROM TABLE(XMLSEQUENCE(EXTRACT(R.address, 'login/'))) T1)
      LOOP
         DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName||' - '||R2.user||' - '||R2.address);
      END LOOP;
  ELSE
        DBMS_OUTPUT.PUT_LINE(vc_uid||' - '||vc_firstName);
  END IF;

Mein problem : Wie loop-Kind-Knoten, so werden die Daten werden wie diese :

LastName | Number | code    | user  |   address
gordon   | 100    | 213     | user1 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
gordon   | 100    | 213     | user2 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
gordon   | 100    | 214     | user1 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
gordon   | 100    | 214     | user2 |Jl. jalan pelan-pelan ke Bekasi, Indonesia
mark     | Null   | null    | null  |Jl. jalan cepet-cepet ke Jakarta, Indonesia

Jede Hilfe zu schätzen wissen.

InformationsquelleAutor zrosystem | 2013-09-03
Schreibe einen Kommentar