Unanswered: DBMS_XMLGEN and DBMS_XMLSTORE Filter Condition program
I am using dbms_xmlgen and dbms_xmlstore package to extract xml datafile tag value from file server to oracle database table.
I have used the below pl/sql program to extract the xml tag values to oracle relational table.Its working fine for me.
But I would like to extract the values based on particular filter condition.
The following xml program I have 3 rows I would like extract the xml data based on the following condition .
the tag <STATE_ABBREVIATION> value shuold be 'CA' and the <CITY> tag value should be Palo_Alto then I will store the ZIPCODE
ZIP_CODE_EXTN,STATE_ABBREVIATION tag values in the Oracle Relational table.
The below pl/sql program storing all the three rows but I required the values based on the filter condition that I mentioned earlier.
PL/SQL Program for XML extract.
finalStr varchar2(4000) := null;
v_FileHandle := utl_file.fopen('XMLTEST','XML_NEW_CITIES.XML','r') ;
when no_data_found then
if finalStr is not null then
finalStr := finalStr || charString;
finalStr := charString;
insCtx := DBMS_XMLStore.newContext('SYS.ZIPCODES');
rowsp := dbms_xmlstore.insertXML(insCtx,finalstr);
You need to create a external table if the external table supports a XMLtype column
Based on that create a view using xmltable function to break the xml into relational format
use that view to populate the relational source
Yes you right. So I have changed my architecture logic like use the dbms_xmlstore package and store all the tags that I required from xml files in the relational staging table then based the my filter requirement I will delete the records in the staging then processing records in real tables.
Thanks for your timely suggesstions.
I have one another query , If you can kindly advice me how can i proceed further.
NULL tag throwing excpetion using DBMS_XMLSTORE in 10g r1
to store the XML tag value from xml file in file server to our database relational table.
I have used the following code to store the XML tag value from xml file in file server to our database relational table.
The DBMS_XMLStore.clearUpdateColumnList(insCtx); is there in the program . But I didn't add the setkeycolumn since in my table I don't add the primary constraint . All the columns in the table are allowing null values.
This is not a problem on TRAN_ID colum, If any of the tag value is null then I am getting xml parsing error.