xml到oracle DB表：遇到问题

Question

我有一个使用Editplus（在Windows中）创建的示例xml文件。

  < ?xml version="1.0" encoding="UTF-8" ?>
  < badges >
    < row UserId="3714" Name="Teacher" Date="2008-09-15T08:55:03.923"/>
    < row UserId="994" Name="Teacher" Date="2008-09-15T08:55:03.957"/>
  < / badges>

我的目标是将这些信息放入OracleDB表中。如这里的建议，我试图执行sql命令。但是不能成功

========================= SQL查询1 ======================= ======

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

  XML_DATA
  ------------------------------------------------------------
  <?xml version="1.0" encoding="WINDOWS-1252"?>
  <badges>
     <row UserId="3714" Name

在输出中，我看到xml文件的一半被截断了。并且输出中的编码类型被视为WINDOWS-1252。有人可以解释为什么会这样吗？

================================================== ========================

============================== SQL查询2 ================ ===============

SQL> SELECT UserId, Name, to_timestamp(dt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') dt

  2        FROM (SELECT XMLTYPE(bfilename('D', 'tmp.xml'), 
  3                             nls_charset_id('WINDOWS-1252')) xml_data
  4                FROM dual),
  5             XMLTable('for $i in /badges/row
  6                                  return $i'
  7                      passing xml_data
  8                      columns UserId NUMBER path '@UserId',
  9                              Name VARCHAR2(50) path '@Name',
 10                              dt VARCHAR2(25) path '@Date');


       XMLTable('for $i in /badges/row
               *
       ERROR at line 5:
       ORA-00933: SQL command not properly ended

==================================================
===================同样的查询在这里工作。但是对我来说却不是。我的机器上安装了oracle 10g。有人可以提出更正以使查询工作。

谢谢。

Answer 1

考虑到您的第一点，您的输出只会在显示时被截断。您可以使用以下命令更改SQL * Plus中显示的字节数SET LONG：

SQL> SELECT XMLTYPE(bfilename('D', 'test.xml'), 
  2         nls_charset_id('WINDOWS-1252')) xml_data FROM dual;

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<badges>
  <row UserId="3714" Name=

SQL> SET LONG 4000
SQL> /

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<badges>
  <row UserId="3714" Name="Teacher" Date="2008-09-15T08:55:03.923"/>
  <row UserId="994" Name="Teacher" Date="2008-09-15T08:55:03.957"/>
</badges>

如您所知，您的字符集将根据您的NLS会话参数进行修改（即：文件将转换为客户端的字符集）。

对于第二点：

• 您正在使用什么版本的SQL * Plus？它可能比数据库旧，并且无法识别合成句法
• 您能否发布在SQL * Plus中键入的确切查询（请使用SO的CODE功能）

因为我无法使用Oracle 10.2.0.3复制：

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

    USERID NAME      DT
---------- --------- ----------------------------
      3714 Teacher   15/09/08 08:55:03,923000000
       994 Teacher   15/09/08 08:55:03,957000000

更新： 此XMLTable synthax必须是10gR2（10.2。*）的新功能（需要确认）

但是，您可以使用另一种访问XML数据的方法：

SQL> SELECT extractvalue(column_value, '/row/@UserId') "userID",
  2         extractvalue(column_value, '/row/@Name') "Name",
  3         extractvalue(column_value, '/row/@Date') "Date"
  4    FROM TABLE(XMLSequence(XMLTYPE(bfilename('D', 'tmp.xml'),
  5                     nls_charset_id('WINDOWS-1252')).extract('/badges/row'))) t;

userID  Name      Date
------- --------- ------------------------
3718    Teacher   2008-09-15T08:55:03.923
994     Teacher   2008-09-15T08:55:03.957