This discussion is archived
2 Replies Latest reply: Jan 10, 2013 10:33 AM by 984142 RSS

Inporting XML data into a table

984142 Newbie
Currently Being Moderated
I have the followiing XML document

<?xml version="1.0" encoding="utf-8"?>
<agents count="1382">
<agent>
<name>Nancy Palmer</name>
<email>npalmer@apr.com</email>
<agentid>MLSL:00525350</agentid>
<officeid>58</officeid>
<website>http://www.nancypalmer.com</website>
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_8418_NANCY_PALMER_COLOR_HEAD_SHOT_HIGH_QUALITY_2011.jpg</photo>
<phone_direct>6504344313</phone_direct>
<phone_cell>6504920200</phone_cell>
<mod_time>2012-08-31T05:15:06.933</mod_time>
</agent>

<agent>
<name>Genella Williamson</name>
<email>genella@apr.com</email>
<agentid>MLSL:00755754</agentid>
<officeid>58</officeid>
<website>http://www.apr.com/genella</website>
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_8426_genella.jpg</photo>
<phone_direct>6504344319</phone_direct>
<phone_cell>6507870839</phone_cell>
<mod_time>2010-10-30T15:15:07.603</mod_time>
</agent>

<agent>
<name>Diana Langley</name>
<email>dlangley@apr.com</email>
<agentid>MLSL:01256202,SFAR:805608</agentid>
<officeid>50</officeid>
<website>http://www.apr.com/DLangley</website>
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_7848_dlangley.jpg</photo>
<phone_direct/>
<phone_cell/>
<mod_time>2011-06-06T05:15:06.587</mod_time>
</agent>
</agents>


I want to load it to the following table

SQL> desc apr_agent
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(100)
EMAIL VARCHAR2(100)
OFFICEID VARCHAR2(50)
WEBSITE VARCHAR2(1000)
PHOTO VARCHAR2(1000)
PHONE_DIRECT VARCHAR2(100)
PHONE_CELL VARCHAR2(100)
MOD_DATE VARCHAR2(100)

SQL>


So I created a directory
create or replace directory APR_STG as '/opt/smarteragent/procdata/APR';

and I created the following insert statement

INSERT INTO apr_agent
(NAME, email, officeid, website, photo, phone_direct, phone_cell, mod_date)
WITH T AS (SELECT XMLTYPE(BFILENAME('APR_STG','agents.xml'), NLS_CHARSET_ID('AL32UTF8')) xmlcol FROM dual)
SELECT EXTRACTVALUE(VALUE(x),'/ROW/name') NAME,
EXTRACTVALUE(VALUE(x),'/ROW/email') email,
EXTRACTVALUE(VALUE(x),'ROW/officeid') officeid,
EXTRACTVALUE(VALUE(x),'ROW/website') website,
EXTRACTVALUE(VALUE(x),'ROW/photo') photo,
EXTRACTVALUE(VALUE(x),'ROW/phone_direct') phone_direct,
EXTRACTVALUE(VALUE(x),'ROW/phone_cell') phone_cell,
EXTRACTVALUE(VALUE(x),'ROW/mod_time') mod_date
FROM T,TABLE(XMLSEQUENCE(EXTRACT(T.xmlcol,'/ROWSET/ROW'))) x;


I am currently getting now rows inserted. Not sure why?

Any help would be greatly appreciated.
  • 1. Re: Inporting XML data into a table
    Jason_(A_Non) Expert
    Currently Being Moderated
    Well, if you were to run the SELECT statement alone (from WITH on down), you would see that it is returning 0 rows. After some head-scratching and searching you would come to realize it is because your XPaths are not finding any data in the XML. In your XPaths, you are looking for a root node of ROWSET with a child of ROW. In the XML, the root node is agents and the child is agent. Assuming you are on 10.2 or later, your SELECT statement itself could be written as
    SELECT NAME, email,
           officeid, website,
           photo, phone_direct,
           phone_cell, mod_date
      FROM XMLTable('/agents/agent'
                    PASSING XMLType(BFILENAME('APR_STG','agents.xml'), NLS_CHARSET_ID('AL32UTF8'))
                    COLUMNS
                    NAME         VARCHAR2(100) PATH 'name',
                    email        VARCHAR2(100) PATH 'email',
                    officeid     VARCHAR2(50) PATH 'officeid',
                    website      VARCHAR2(1000) PATH 'website',
                    photo        VARCHAR2(1000) PATH 'photo',
                    phone_direct VARCHAR2(100) PATH 'phone_direct',
                    phone_cell   VARCHAR2(100) PATH 'phone_cell',
                    mod_date     VARCHAR2(100) PATH 'mod_time');
    and then you could just append that to your INSERT so you have something like
    INSERT INTO apr_agent
    (NAME, email, officeid, website, photo, phone_direct, phone_cell, mod_date)
    SELECT NAME, email,....
    If the count of 1382 is to be believed, then you may start wondering why your INSERT is taking so long to process. Performance will depend upon many factors.

    If you are on 11g (any of them), then you have an option to improve performance over 10.2 and earlier versions. You simply create a table, it could even be a Global Temporary Table that looks something like
    create table TEMP_XML (XML_DATA xmltype)
    xmltype column XML_DATA store as securefile binary xml;
    (if on 11.2.0.2 or later, you only need)
    create table TEMP_XML (XML_DATA xmltype);
    Then you insert the XML data into it via
    INSERT INTO TEMP_XML VALUES XMLType(BFILENAME('APR_STG','agents.xml'), NLS_CHARSET_ID('AL32UTF8')));
    and change the above SELECT statement to read from that table instead, such as
    SELECT NAME, email,
           officeid, website,
           photo, phone_direct,
           phone_cell, mod_date
      FROM TEMP_XML tx
           XMLTable('/agents/agent'
                    PASSING tx.xml_data
    ...
    And then you'll be done with that task.

    Note: Only the first SQL statement was verified, the rest should be valid but have not been executed/verified.
  • 2. Re: Inporting XML data into a table
    984142 Newbie
    Currently Being Moderated
    thanks so much !!! that did it for me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points