Xml To Oracle Db Table : Encountering Problems

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='

Solution 1:

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><rowUserId="3714"Name=

SQL> SET LONG 4000
SQL> /

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?><badges><rowUserId="3714"Name="Teacher"Date="2008-09-15T08:55:03.923"/><rowUserId="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
  2FROM (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/0808:55:03,923000000994 Teacher   15/09/0808: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"
  4FROMTABLE(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.923994     Teacher   2008-09-15T08:55:03.957

Solution 2:

I had the exact same problem, I was wondering why:

encoding="UTF-8"

changed to

encoding="WINDOWS-1250"

in my case (after loading).

Then I realised what Oracle does here: it converts the utf-8 encoded xml to the default character set of your database, in order to be able to store it. That's why it changes the value of 'encoding'. If the default character set of your databse is utf-8, then 'encodig' will not change.

If your xml actually has utf-8 encoded characters, then trying to load it in the database with nls_charset_id('WINDOWS-1252') will throw an error.

So to put it short: you should not worry about encoding="UTF-8" changing to encoding="WINDOWS-1252", just ignore it - the database is doing its job.