甲骨文:加载一个大的xml文件? [英] Oracle: loading a large xml file?
问题描述
现在,我对大量的XML数据感兴趣:
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
我想将其加载到Oracle中以供使用.
I'd like to load this into Oracle to play with.
如何直接将大型XML文件直接加载到Oracle中?欢迎使用服务器端解决方案(可以在服务器上打开数据文件)和客户端解决方案.
How can I directly load a large XML file directly into Oracle? Server-side solutions (where the data file can be opened on the server) and client-side solutions welcomed.
下面是一个具体示例的badges.xml.
Here's a bit of badges.xml for a concrete 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"/>
...
推荐答案
您可以通过SQL访问服务器上的XML文件.将数据放在/tmp/tmp.xml中,您首先要声明目录:
You can access the XML files on the server via SQL. With your data in the /tmp/tmp.xml, you would first declare the directory:
SQL> create directory d as '/tmp';
Directory created
然后您可以直接查询XML文件:
You could then query your XML File directly:
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>
[...]
To access the fields in your file, you could use the method described in another SO for example:
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
这篇关于甲骨文:加载一个大的xml文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!