Oracle XML : Skip Not exist Node

Question

I have a problem proceed xml data to insert in oracle table, here is my xml :

<begin>
    <entry>
        <lastname>gordon</lastname>
        <NumberList>
            <number>100</number>
            <codelist>
                 <code>213</code>
            <codelist>
        <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>

What result I expected is :

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

When accessing not exist node, 1 row data will not appear ( like second row data).

Update : here's my current code

 DECLARE
       X XMLTYPE := XMLTYPE('
<begin>
    <entry>
        <lastname>gordon</lastname>
        <NumberList>
            <number>100</number>
            <codelist>
                 <code>213</code>
            </codelist>
        </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>');
    BEGIN
       FOR R IN (SELECT EXTRACTVALUE(VALUE(P), '/entry/lastname/text()') AS lastname,
                        EXTRACTVALUE(VALUE(P), '/entry/address/addresslist/text()') AS address,
                        EXTRACT(VALUE(P), '/entry/NumberList') AS NumberList
                   FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//begin/entry'))) P)
       LOOP
          FOR R1 IN (SELECT EXTRACTVALUE(VALUE(T1), 'NumberList/number/text()') AS numbers,
                        EXTRACTVALUE(VALUE(T1), 'NumberList/codelist/code/text()') AS code
                       FROM TABLE(XMLSEQUENCE(EXTRACT(R.NumberList, '/NumberList'))) T1)
          LOOP
             DBMS_OUTPUT.PUT_LINE(r.lastname||' | '||R1.numbers||' | '||R1.code||' | '||r.address);
          END LOOP;
       END LOOP;
    END;

before inserting check for null values.If null exists then dont insert else continue with your insert.. — Sasidharan
– Sasidharan, Commented Sep 3, 2013 at 4:09
the problem is, second 2 not appear, because not exist child node "number" and "code" — zrosystem
– zrosystem, Commented Sep 3, 2013 at 4:15
first you should close the tag that are opened, in your code, close <codelist> with </codelist> — ajmalmhd04
– ajmalmhd04, Commented Sep 3, 2013 at 6:45
@ajmalmhd04 i never thought i could happen, how?so no child node anymore? — zrosystem
– zrosystem, Commented Sep 3, 2013 at 7:20

APC · Accepted Answer · 2018-07-19 07:25:39Z

Here is the target table:

CREATE TABLE my_test
     (
          LastName varchar2(20),
          phone   NUMBER,
          code    varchar2(20),
          address VARCHAR2(100)
     );

The code to populate it:

SET serveroutput ON
DECLARE
l_xml xmltype;
l_val VARCHAR2(1000) := '<begin>
    <entry>
        <lastname>gordon</lastname>
        <numberlist>
            <number>100</number>
            <codelist>
                 <code>213</code>
            </codelist>
        </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>';
     l_lastname varchar2(50);
     l_phone number;
     l_code number;
     l_address  varchar2(200);
BEGIN
     l_xml        := xmltype(l_val);

     FOR x IN
     (SELECT VALUE(p) col_val
     FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/begin/entry'))) p
     )
     loop

IF x.col_val.existsnode('/entry/lastname/text()') > 0 THEN
         l_lastname := x.col_val.extract('/entry/lastname/text()').getstringval();
END IF;
if x.col_val.existsnode('/entry/numberlist/number/text()') > 0 then
     l_phone := x.col_val.extract('/entry/numberlist/number/text()').getstringval();
end if;
if x.col_val.existsnode('/entry/numberlist/codelist/code/text()') > 0 then
     l_code := x.col_val.extract('/entry/numberlist/codelist/code/text()').getstringval();
end if;
IF x.col_val.existsnode('/entry/address/addresslist/text()') > 0 THEN
         l_address := x.col_val.extract('/entry/address/addresslist/text()').getstringval();
end if;
INSERT INTO my_test
     (
          lastname,
          phone,
          code,
          address
     )
     VALUES
     (
          l_lastname,
          l_phone,
          l_code,
          l_address
     );
     l_lastname := null;
     l_phone := null;
     l_code := null;
     l_address := null;
     end loop;
commit;
end;

this working !!! very simple code, i think existnode is what i need:)) thx in advance !!

user272735 · Accepted Answer · 2013-09-03 06:20:13Z

2

You didn't say how you've tried to parsexml. Here is an example with XMLTABLE that gives you the excepted results:

with xmldata(d) as (select xmltype('<begin>
    <entry>
        <lastname>gordon</lastname>
        <NumberList>
            <number>100</number>
            <codelist>
                 <code>213</code>
            </codelist>
        </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>') from dual
)
select x.*
from xmldata,
     xmltable('begin/entry' passing xmldata.d
              columns
              last_name varchar2(10) path 'lastname',
              number_ number path 'NumberList/number',
              code number path 'NumberList/codelist/code',
              address varchar2(50) path 'address/addresslist'
     ) x
;

Results:

LAST_NAME  NUMBER_  CODE ADDRESS
---------- ------- ----- --------------------------------------------------
gordon         100   213 Jl. jalan pelan-pelan ke Bekasi, Indonesia
mark                     Jl. jalan cepet-cepet ke Jakarta, Indonesia

Hope this helps !

answered Sep 3, 2013 at 6:20

user272735

10.6k9 gold badges72 silver badges100 bronze badges

2 Comments

zrosystem Over a year ago

this raise "unsupported column aliasing", anyway, i add my code above, please check :)

ajmalmhd04 Over a year ago

okay. the above wouldbe working on alter session set cursor_sharing = exact;

Collectives™ on Stack Overflow

Oracle XML : Skip Not exist Node

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related