Oracle XML datahandling (import etc)

importing XML-files into an oracle database

create a table with a CLOB-column
CREATE TABLE CLOBTEST
(
  POID    NUMBER,
  POCLOB  CLOB
)

POID can/should be autoincrement…

using sqlloadr to load the XML-files into the table
with the unix find-command create a table with all your XML-files you want to import (see example below). the XML.ctl file looks like this:
LOAD DATA 
INFILE *
INTO TABLE CLOBtest TRUNCATE
FIELDS TERMINATED BY ‚,‘
(
    POID CHAR(10),
    clob_filename FILLER CHAR(100),
    POCLOB  LOBFILE(clob_filename) TERMINATED BY EOF
)
–sqlldr schema@db XML.ctl

–find /home/data/ -name *.XML
BEGINDATA
1,/home/data/DOC00001.XML
2,/home/data/DOC00001.XML
3,/home/data/DOC00001.XML

query to extract XML-data

the hard part is to query the XML-tags (elements, values etc) in the CLOB-column. here is an example. first convert the CLOB columns into a XMLType and then use extractvalue or other functions (extract…) to query your data

SELECT extractvalue(xmldata, ‚/700/710/711/‘) AS col1
FROM 
    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)
;
to query sub-tables in the XML-data
SELECT 

    t.id
    , li.ctry
FROM 

    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)    
p,
XMLTable
    (
        ‚/*‘ PASSING p.xmldata
        COLUMNS 
            id VARCHAR2(28) PATH ‚/product/@id‘,
            ctry XMLType PATH ‚/product/soldctry‘
    ) t
    ,
XMLTable
    (
        ‚ctry‘ PASSING t.ctry
        COLUMNS 
            ctry        VARCHAR2(2)  PATH ‚/‘
    ) li
ORDER BY
    t.id
    , li.ctry
;

the p-table is the conversion of the CLOB-datatype into an XML-document.
the t-table extracts the ID of the product-element and the country/countries inside the product.
the li-table extracts all country-names of the product.
the result is a list with all countries of a product.
ID CTRY
15A1 AT
15A1 BE
15A1 BG
15A1 CH
15A1 CY
01A2 AT
01A2 BE

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s