Oracle:加载一个大的 xml 文件?

2021-12-03 00:00:00 xml sql oracle

现在我有大量感兴趣的 XML 数据:

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

https://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>
  [...]

要访问文件中的字段，您可以使用 另一个 SO 例如:

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

相关文章