Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    9

    Unanswered: DBMS_XMLGEN and DBMS_XMLSTORE Filter Condition program

    Hi,

    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 .

    Filter 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.

    Kindly assist me how can I change my program.?


    XML Program file content.
    ======================
    <ZIPCODES>
    <mappings>
    <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
    <ZIPCODE>94301</ZIPCODE>
    <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
    <CITY>Palo_Alto</CITY>
    </mappings>
    <mappings>
    <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
    <ZIPCODE>95302</ZIPCODE>
    <ZIP_CODE_EXTN>9279</ZIP_CODE_EXTN>
    <CITY>LA</CITY>
    </mappings>
    <mappings>
    <STATE_ABBREVIATION>TX</STATE_ABBREVIATION>
    <ZIPCODE>75038</ZIPCODE>
    <ZIP_CODE_EXTN>7837</ZIP_CODE_EXTN>
    <CITY>DALLAS</CITY>
    </mappings>
    </ZIPCODES>

    PL/SQL Program for XML extract.
    ==========
    declare
    charString varchar2(80);
    finalStr varchar2(4000) := null;
    rowsp number;
    insCtx DBMS_XMLStore.ctxType;
    ctx dbms_xmlgen.ctxHandle;
    v_FileHandle UTL_FILE.FILE_TYPE;
    begin
    v_FileHandle := utl_file.fopen('XMLTEST','XML_NEW_CITIES.XML','r') ;
    loop
    BEGIN
    utl_file.get_line(v_FileHandle, charString);
    exception
    when no_data_found then
    utl_file.fclose(v_FileHandle);
    exit;
    END;
    dbms_output.put_line(charString);
    if finalStr is not null then
    finalStr := finalStr || charString;
    else
    finalStr := charString;

    end if;
    end loop;
    insCtx := DBMS_XMLStore.newContext('SYS.ZIPCODES');
    dbms_xmlgen.setRowsetTag(insCtx,'ZIPCODES');
    dbms_xmlgen.setRowsetTag(insCtx,'mappings');
    DBMS_XMLStore.clearUpdateColumnList(insCtx);
    DBMS_XMLStore.setUpdateColumn(insCtx,'ZIPCODE');
    DBMS_XMLStore.setUpdateColumn(insCtx,'ZIP_CODE_EXT N');
    DBMS_XMLStore.setUpdateColumn(insCtx,'STATE_ABBREV IATION');
    rowsp := dbms_xmlstore.insertXML(insCtx,finalstr);
    end;

  2. #2
    Join Date
    Jun 2004
    Posts
    115
    Hi

    You cannot do this through dbms_xmlstore.

    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

    http://download-uk.oracle.com/docs/c...y.htm#CBAJBBDD

    Another mehtod of shredding xml and storing it in relation data using xml table is in case the XMLType column is not supported by external table

    http://www.dbazine.com/olc/olc-articles/scardina1



    regards
    Hrishy
    Last edited by db2hrishy; 11-18-07 at 06:25.

  3. #3
    Join Date
    Nov 2007
    Posts
    9
    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.


    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.



    Thanks,
    Nat

  4. #4
    Join Date
    Jun 2004
    Posts
    115
    Hi

    I dont see a call to
    DBMS_XMLStore.clearUpdateColumnList(updCtx);
    and
    DBMS_XMLStore.setKeyColumn(updCtx,'TRAN_ID');

    Can you please add that and then try since you are trying update some relation data based on xml fragment you need to have the line DBMS_XMLStore.setKeyColumn(updCtx,'TRAN_ID') in your code somewhere.


    regards
    Hrishy
    Last edited by db2hrishy; 11-22-07 at 09:27.

  5. #5
    Join Date
    Nov 2007
    Posts
    9
    Hi,

    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.

    Thanks for spending a time for me.

    Nat

  6. #6
    Join Date
    Jun 2004
    Posts
    115
    Hi

    Hmm i dont think so you can use dbms_xmlstore to do what you are trying to do.

    Maybe you should have a look at oracle xmldb and extractValue function to get what you want it takes much less code to do what you are trying to do.

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •