xml to oracle DB table : encountering problems

Question

Hi,

I have a sample xml file created using Editplus( in windows).

  < ?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>

My goal here is to get this information into Oracle DB table. As suggested here http://stackoverflow.com/questions/998055?sort=newest#sort-top, I tried to execute the sql commands. But couldn't succeed,

========================= sql query 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

In the output, I see half of the xml file got truncated. And the encoding type in output is seen as WINDOWS-1252. Could someone explain why is it happening so?

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

=============================== sql query 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

===================================================================== The same query was working here http://stackoverflow.com/questions/998055?sort=newest#sort-top. But for me it doesn't. I have oracle 10g installed on my machine. Could someone suggest the corrections to make the queries work.

Thanks.

Answer 1

Hi Shafi,

Considering your first point, your output is only truncated on display. You can change how many bytes are displayed in SQL*Plus with 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>

As you have noticed, your character set will be modified per your NLS session parameters (i-e: the file will be translated to the character set of your client).

For the second point:

• What version of SQL*Plus are you using ? It might be older than the database and not recognizing the synthax
• could you post the exact query as you typed it in SQL*Plus (Please use the CODE feature of SO)

because I can not reproduce with 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

Update: This XMLTable synthax must be a new feature of the 10gR2 (10.2.*) (needs confirmation)

You can however use another method of accessing XML Data(described in another SO):

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

Answer 2

Hi Vincent,

Thanks for the help. 'set Long 4000' fixed the truncation issue.

But I am still struggling to get the second query running. My sqlplus version is "SQL*Plus: Release 10.1.0.2.0". Do you think that version is the issue.

Here is the code i have tried.

SQL> select xmltype(bfilename('D','tmp.xml'),nls_charset_id('WINDOWS-1252')) xml_data from dual;

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


SQL> set LONG 4000
SQL> /

XML_DATA
--------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<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>


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 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');
       XMLTable('for $i in /badges/row
               *
ERROR at line 4:
ORA-00933: SQL command not properly ended