import xml to oracle database table

Question

I have this sample xml my requirement is to parse it and fetch values from it's various nodes and insert them into one of oracle table.

<?xml version="1.0" encoding="UTF-8"?>
<Reporting>
    <Selection>69</Selection>
    <MonthEndDate>9/30/2016</MonthEndDate>
    <Email>
        <Name>abc</Name>
        <Address>[email protected]</Address>
    </Email>
    <Request>
        <Port_id_list>
            <Port_id>1901</Port_id>
            <Port_id>1902</Port_id>
            <Port_id>1903</Port_id>
        </Port_id_list>
    </Request>
</Reporting>

How can we do this? Please help.

hinotf · Accepted Answer · 2016-12-02 10:53:45Z

1

EDITED

For example:

WITH my_data AS
(SELECT xmltype('<?xml version="1.0" encoding="UTF-8"?>
<Reporting>
    <Selection>69</Selection>
    <MonthEndDate>9/30/2016</MonthEndDate>
    <Email>
        <Name>abc</Name>
        <Address>[email protected]</Address>
    </Email>
    <Request>
        <Port_id_list>
            <Port_id>1901</Port_id>
            <Port_id>1902</Port_id>
            <Port_id>1903</Port_id>
        </Port_id_list>
    </Request>
</Reporting>') my_xml
  FROM dual)
SELECT extractValue(md.my_xml, 'Reporting/Selection/text()') selection_id,
       extractValue(value(port_ids), 'Port_id/text()') port_id
  FROM my_data md,
       TABLE(XMLSequence (md.my_xml.extract ('Reporting/Request/Port_id_list/Port_id'))) port_ids;

If your XML has node with children create one-to-many with TABLE and XMLSequence.

edited Dec 2, 2016 at 10:53

answered Dec 2, 2016 at 10:30

hinotf

1,1481 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yatinbc Over a year ago

For single node it worked, but for multiple sequence it is throwing error. When I used path as c_port_id varchar2(4000) PATH 'Request/Port_id_list/Port_id/text()' gives me error expected singleton sequence got multiple sequence.

hinotf Over a year ago

Wait few minutes I give you example via XMLSequence operator

Marmite Bomber · Accepted Answer · 2016-12-02 12:57:34Z

You should preferably use XMLTABLE as the extractValue was deprecated

Here an example selecting teh ports with the (denormalized) parten attributes. I added also posr sequence to preserve order of the ports.

WITH t AS
(SELECT xmltype('<?xml version="1.0" encoding="UTF-8"?>
<Reporting>
    <Selection>69</Selection>
    <MonthEndDate>9/30/2016</MonthEndDate>
    <Email>
        <Name>abc</Name>
        <Address>[email protected]</Address>
    </Email>
    <Request>
        <Port_id_list>
            <Port_id>1901</Port_id>
            <Port_id>1902</Port_id>
            <Port_id>1903</Port_id>
        </Port_id_list>
    </Request>
</Reporting>')  xml
  FROM dual)
select  
x.Selection,x.MonthEndDate,x.emailName, x.emailAddress,
o.port_seq, o.port_id 
from t,   
        XMLTable(
          'for $i in /Reporting     
           return $i'
          passing t.xml 
          columns
                 Selection varchar2(30) path 'Selection',
                 MonthEndDate varchar2(30) path 'MonthEndDate',
                 emailName varchar2(30) path 'Email/Name',
                 emailAddress varchar2(30) path 'Email/Address',
                 Port_id_list XMLType path '//Port_id'
                  ) x,
         XMLTable(
          './Port_id'
          passing  (x.Port_id_list)
          columns
                port_seq for ordinality,
                port_id varchar2(30) path '/Port_id' 
                  ) o                   
;

SELECTION                      MONTHENDDATE                   EMAILNAME                      EMAILADDRESS                     PORT_SEQ PORT_ID                      
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------
69                             9/30/2016                      abc                            [email protected]                           1 1901                           
69                             9/30/2016                      abc                            [email protected]                           2 1902                           
69                             9/30/2016                      abc                            [email protected]                           3 1903

Collectives™ on Stack Overflow

import xml to oracle database table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related