Geek Answers Handbook

Oracle: loading a large xml file?

So, now that I have a large bit of XML data, I'm interested in:

http://blog.stackoverflow.com/2009/06/stack-overflow-creative-commons-data-dump

I would like to upload this to Oracle to play with.

How can I directly upload a large XML file directly to Oracle? Server solutions (where the data file can be opened on the server) and client-side solutions are welcome.

Here is some badges.xml for a specific example.

<?xml version="1.0" encoding="UTF-8" ?>
  <badges>
  <row UserId="3718" Name="Teacher" Date="2008-09-15T08:55:03.923"/>
  <row UserId="994" Name="Teacher" Date="2008-09-15T08:55:03.957"/>
  ...
+5
source share
3 answers

You can access the XML files on the server through SQL. With your data in /tmp/tmp.xml you must first declare a directory:

SQL> create directory d as '/tmp';

Directory created

You can directly request your XML file:

SQL> SELECT XMLTYPE(bfilename('D', 'tmp.xml'), nls_charset_id('UTF8')) xml_data
  2    FROM dual;

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<badges>
  [...]

, , SO, :

SQL> SELECT UserId, Name, to_timestamp(dt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') dt
  2    FROM (SELECT XMLTYPE(bfilename('D', 'tmp.xml'), 
                            nls_charset_id('UTF8')) xml_data
  3            FROM dual),
  4         XMLTable('for $i in /badges/row
  5                              return $i'
  6                  passing xml_data
  7                  columns UserId NUMBER path '@UserId',
  8                          Name VARCHAR2(50) path '@Name',
  9                          dt VARCHAR2(25) path '@Date');

    USERID NAME       DT                         
---------- ---------- ---------------------------
      3718 Teacher    2008-09-15 08:55:03.923    
       994 Teacher    2008-09-15 08:55:03.957
+12

, - XML-, Oracle . , , , .

BLOB, CLOB BFILE . , , DIRECTORY , . . BFILE, . (CLOB BLOB , BFILE ).

, , CLOB . , , XML .

, . 1. .

Oracle XML .

. , ( , ), XML- XML. CLOB BFILE XMLTYPE , - , , , SQL XQUERY XML .

, XQUERY, CLOB BFILE, XMLTYPE .

+3

I would make simple:

grep '<row' file.xml |\
gawk -F '"' '{printf("insert into badges(userid,name,date) values (\"%s\",\"%s\",\"%s\");\n",$2,$4,$6); } > request.sql

or you can create a Java program using the SAX analyzer. Each time your handler finds a new line "Element", you get the attributes and insert a new record into your database.

0
source

More articles:


All Articles