Hi ,

Topic : NULL tag throwing excpetion using DBMS_XMLSTORE in 10g r1

I am trying to store the XML tag value from xml file in file server to our database relational table.

I have used the following code

insCtx := DBMS_XMLStore.newContext('tran_data');
dbms_xmlstore.setRowTag(insCtx,'subtran');

DBMS_XMLStore.clearUpdateColumnList(insCtx);
DBMS_XMLStore.setUpdateColumn(insCtx,'TRAN_ID');
DBMS_XMLStore.setUpdateColumn(insCtx,'RCODE');
DBMS_XMLStore.setUpdateColumn(insCtx,'STATION');
rowsp := dbms_xmlstore.insertXML(insCtx,finalstr);
DBMS_XMLStore.closeContext(insCtx);

tran_data is my table name.
in the finalstr I have store the xml file data using utl_file package.

following is my xml

<?xml version = '1.0'?>
<TRAN>
<subtran>
<TRAN_ID>2</TRAN_ID>
<RCODE>x</RCODE>
<TRAN_DT>01-JUN-2005</TRAN_DT>
<STATION>1234567</STATION>
</subtran>
<subtran>
<TRAN_ID>3</TRAN_ID>
<RCODE>y</RCODE>
<TRAN_DT>01-JUN-2005</TRAN_DT>
<STATION>1234567</STATION>
</subtran>
</TRAN>

All are working fine unless the tag value don't have null value

I am having a problem If I have null value any of the tag. I am getting error message

for eg.in the following xml if TRAN_ID is tag is null , then I am getting error message

<?xml version = '1.0'?>
<TRAN>
<subtran>
<TRAN_ID></TRAN_ID>
<RCODE>x</RCODE>
<TRAN_DT>01-JUN-2005</TRAN_DT>
<STATION>1234567</STATION>
</subtran>
<subtran>
<TRAN_ID>3</TRAN_ID>
<RCODE></RCODE>
<TRAN_DT>01-JUN-2005</TRAN_DT>
<STATION>1234567</STATION>
</subtran>
</TRAN>

What is the work around for this in 10g r1. I am using Oracle 10g relase 1.10.1.0.2.0.

Please advice me how to proceed further.

Thanks,
Nat