Tom,
It may be something obvious I'm missing so do tell me to sod off, but I'm stucker than someone that's stood on a tube of superglue.
I have a problem. I'm trying to show it's possible to load an XML file, parse it, and put the documents fields into a table.
I can load the file into memory, but when I parse it I get a java erro that the root element cannot be found by 'DBMS_XMLSave.insertXML', i.e. the java based XML SQL Utility (XSU) cannot read the file.
The best I can come up with is that the file I'm loading is coming via a BFILE and is not being translated properly before it's put into the CLOB. I.e. it's still binary and not being converted into char.
If this is the problem, can you see (without coding something in C using OCI) how I can convert so it can be read.
Additional info - I'm using Oracle 9i, and NLS_CHARACTERSET is set to UTF8 (cause we have to cope with welsh).
==============set up==============
This is the file I want to put in a table
-------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW num="1">
<SURNAME>Rushton</SURNAME>
<FORENAMES>Simon</FORENAMES>
<DOB>30/01/1975</DOB>
<CHARGE>Eating too much cake</CHARGE>
</ROW>
</ROWSET>
-------------------------
This is the directory it lives in
(GRANT ANY DIRECTORY to <user>)
CREATE DIRECTORY testdir as '<location of XML file>'
This creates the table I want to populate
-------------------------
CREATE TABLE rushtons.defendant
(surname VARCHAR2(20) NOT NULL
,forenames VARCHAR2(20)
,dob DATE
,charge VARCHAR2(40))
/
-------------------------
This creates the SP to insert the xml into the table
-------------------------
CREATE OR REPLACE PROCEDURE testInsert( xmlDoc IN CLOB) IS
insCtx DBMS_XMLSave.ctxType;
doc CLOB;
rows NUMBER;
BEGIN
insCtx := DBMS_XMLSave.newContext('rushtons.defendant'); -- get the save context
--FALLS OVER HERE
rows := DBMS_XMLSave.insertXML(insCtx, xmlDoc);
DBMS_XMLSave.closeContext(insCtx);
END;
/-------------------------
And this is the fella that loads a file into a CLOB, from the file system via a BFILE
-------------------------
CREATE OR REPLACE PROCEDURE run AS
xml_file CLOB;
fils BFILE := BFILENAME ('TESTDIR', 'acase.xml');
BEGIN
DBMS_LOB.CREATETEMPORARY(xml_file, TRUE);
IF (DBMS_LOB.FILEEXISTS(fils) != 0)
THEN
DBMS_OUTPUT.PUT_LINE('BFILE exists');
ELSE
DBMS_OUTPUT.PUT_LINE('BFILE does not exist');
END IF;
IF (DBMS_LOB.FILEISOPEN(fils) = 1)
THEN
DBMS_OUTPUT.PUT_LINE('BFILE is open');
ELSE
DBMS_OUTPUT.PUT_LINE('BFILE is not open');
END IF;
DBMS_LOB.FILEOPEN(fils, DBMS_LOB.FILE_READONLY);
IF (DBMS_LOB.FILEISOPEN(fils) = 1)
THEN
DBMS_OUTPUT.PUT_LINE('BFILE is open');
ELSE
DBMS_OUTPUT.PUT_LINE('BFILE is not open');
END IF;
-- CHARACTER SET CONVERSION REQUIRED HERE?:
-- from </code>
http://download-uk.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a888 <code>
79/adl02bs3.htm
DBMS_LOB.LOADFROMFILE(xml_file, fils, DBMS_LOB.GETLENGTH( fils ) );
testInsert(xml_file);
DBMS_LOB.FILECLOSE(fils);
END;
-------------------------
And this is the error message I get
-------------------------
SQL> exec run
BEGIN run; END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.xml.sql.OracleXMLSQLException: Start of root element expected.
ORA-06512: at "SYS.DBMS_XMLSAVE", line 27
ORA-06512: at "RUSHTONS.TESTINSERT", line 19
ORA-06512: at "RUSHTONS.RUN", line 37
ORA-06512: at line 1
-------------------------
I asked Sean Dillon, our local XML guy to take a look at this. Here's what he had to say:
---------------------------------------------------------
It's failing because you're trying to load a BFILE into a UTF-8 database. Oracle is reading the file and the file's not UTF-8, therefore Oracle doesn't recognize the data as being XML. I tried your code above and it works fine in a standard CHARSET 9i database. (one problem with your XML, DOB had to be "30/01/1975 0:0:0").
If you want to pursue the BFILE approach, I'd say that's a support issue. Call Oracle support and send them your test case.
I worked around this in my UTF-8 database by using Java to load the XML into a CLOB in the database, then using a stored procedure in the database to read the CLOB and use DBMS_XMLSAVE to parse it into a table. Here's the scripts I used to accomplish this:
=============================================================
create table emp
as select * from scott.emp where 1=0
/
REM ************************************************
REM *
REM * Setup a PL/SQL function to insert XML data
REM * into the EMP table. This function assumes ALL
REM * columns are present in the table.
REM *
REM ************************************************
create table tmp_clob(
id number,
theclob clob)
/
create sequence tmp_clob_seq
/
create trigger bi_tmp_clob
before insert
on tmp_clob
for each row
begin
select tmp_clob_seq.nextval
into :new.id
from dual;
end;
/
create or replace function insert_xml_emps(
p_tablename in varchar2,
p_in_tmpclob in number)
return number
is
l_ctx dbms_xmlsave.ctxType;
l_rows number;
begin
for c1 in (select theclob
from tmp_clob
where id = p_in_tmpclob) loop
l_ctx := dbms_xmlsave.newContext(p_tableName);
l_rows := dbms_xmlsave.insertxml(l_ctx,c1.theclob);
dbms_xmlsave.closeContext(l_ctx);
delete from tmp_clob
where id = p_in_tmpclob;
end loop;
return l_rows;
end insert_xml_emps;
/
THEN I used this Java class to insert the file into the TMP_CLOB table, get the ID, and call the stored procedure to process the CLOB into the EMP table:
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.*;
import oracle.sql.*;
/**
*
* This class responsible for accepting a username, password, and filename
* and inserting the file into a temporary clob table in the database.
* Once the clob is inserted, we call a PL/SQL stored procedure based on
* the XML SQL Utility to parse the CLOB as XML, and insert it into the
* EMP table.
*
*/
public class InsertEmps {
/* YOU'LL have to change these connection properties */
static String conStr = "jdbc:oracle:thin:@138.1.122.165:1521:9i_utf8";
public static void main(String args[])
throws SQLException, FileNotFoundException, IOException {
if (args.length != 3) {
System.out.println("\nUsage:\n java InsertEmps <username> <password> <filename>\n");
System.exit(0);
}
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(conStr, args[0], args[1]);
conn.setAutoCommit(false);
String sql =
"begin insert into tmp_clob(theclob) " +
"values (empty_clob()) return theclob,id into ?,?; end;";
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall(sql);
ocs.registerOutParameter(1, OracleTypes.CLOB);
ocs.registerOutParameter(2, OracleTypes.NUMBER);
ocs.executeUpdate();
oracle.sql.NUMBER clobId = ocs.getNUMBER(2);
InputStream is = new FileInputStream(args[2]);
OutputStream os = (ocs.getCLOB(1)).getAsciiOutputStream();
byte[] buf = new byte[1024];
int length;
while ((length = is.read(buf, 0, 1024)) != -1) {
os.write(buf, 0, length);
}
is.close();
os.close();
ocs.close();
OracleCallableStatement ocs2 =
(OracleCallableStatement)conn.prepareCall("begin ? := insert_xml_emps(?, ?); end;");
ocs2.registerOutParameter(1, OracleTypes.NUMBER);
ocs2.setString(2,"EMP");
ocs2.setNUMBER(3, clobId);
try {
ocs2.execute();
System.out.println("Inserted " + (ocs2.getNUMBER(1)).longValue() + " records.");
} catch (Exception e) {
e.printStackTrace();
}
ocs2.close();
conn.commit();
conn.close();
}
}
Given an XML document that looks like this:
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="2">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/20/1981 0:0:0</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="3">
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/22/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="4">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>4/2/1981 0:0:0</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="5">
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>9/28/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="6">
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>5/1/1981 0:0:0</HIREDATE>
<SAL>2850</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="7">
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>6/9/1981 0:0:0</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW num="8">
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>4/19/1987 0:0:0</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="9">
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>11/17/1981 0:0:0</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW num="10">
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>9/8/1981 0:0:0</HIREDATE>
<SAL>1500</SAL>
<COMM>0</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="11">
<EMPNO>7876</EMPNO>
<ENAME>ADAMS</ENAME>
<JOB>CLERK</JOB>
<MGR>7788</MGR>
<HIREDATE>5/23/1987 0:0:0</HIREDATE>
<SAL>1100</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="12">
<EMPNO>7900</EMPNO>
<ENAME>JAMES</ENAME>
<JOB>CLERK</JOB>
<MGR>7698</MGR>
<HIREDATE>12/3/1981 0:0:0</HIREDATE>
<SAL>950</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="13">
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>12/3/1981 0:0:0</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="14">
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>1/23/1982 0:0:0</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
Using the Java class looks something like this:
SQL> select * from emp;
no rows selected
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.2.1 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
C:\Documents and Settings\sdillon\My Documents\Oracle\Training\9i_Class\oldscripts>java InsertEmps sdillon sdillon emp.xml
Inserted 14 records.
C:\Documents and Settings\sdillon\My Documents\Oracle\Training\9i_Class\oldscripts>sqlplus sdillon/sdillon@9i_utf8
SQL*Plus: Release 9.0.1.0.1 - Production on Wed Feb 27 15:07:23 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.1 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Hope that helps!