Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Red face Unanswered: upload xml file to database

    Hi All

    I am getting XML file as input in my interface ,i have read this file and then upload some data from it to oralce data base..

    example <TxInfAndSts>
    <OrgnlPmtInfId>1234</OrgnlPmtInfId>
    <OrgnlInstrId>CC2011-05-12-Test</OrgnlInstrId>
    <OrgnlEndToEndId>TWOPOWITHINVANDCREDITNOTE1</OrgnlEndToEndId>
    RfrdDocTp>
    <Cd>CINV</Cd>
    </RfrdDocTp>
    <RfrdDocNb>FDC01</RfrdDocNb>
    ....
    ----


    have to fetch OrgnlEndToEndId value,
    Cd and RfrdDocNb


    I am very new to this have never done this before
    How i can do this .Will be great if some samle code is given for reference
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example.

    First, I'll create a table that is capable of storing XML type data:
    Code:
    SQL> create table test_xml
      2    (col xmltype);
    
    Table created.
    Now, based on your example, I'm inserting a record into a table. Note the syntax (XMLTYPE, as well as the way I formatted (indented) XML so that it is easier to read. It is VERY important to visually see what you are dealing with. Otherwise, if you get lost in the XML tree, you'll never get the result):
    Code:
    SQL> insert into test_xml (col)
      2    values (xmltype('<TxInfAndSts>
      3                       <OrgnlPmtInfId>1234</OrgnlPmtInfId>
      4                       <OrgnlInstrId>CC2011-05-12-Test</OrgnlInstrId>
      5                       <OrgnlEndToEndId>TWOPOWITHINVANDCREDITNOTE1</OrgnlEndToEndId>
      6                       <RfrdDocTp>
      7                         <Cd>CINV</Cd>
      8                       </RfrdDocTp>
      9                       <RfrdDocNb>FDC01</RfrdDocNb>
     10                     </TxInfAndSts>'));
    
    1 row created.
    Finally, let's extract information you're interested in. Basically, you just have to follow the tree and select branches (OK, there might be some modifications, but it is all described in the documentation):
    Code:
    SQL> select extractvalue(col, 'TxInfAndSts/OrgnlEndToEndId') val_1,
      2         extractvalue(col, 'TxInfAndSts/RfrdDocTp/Cd') val_2,
      3         extractvalue(col, 'TxInfAndSts/RfrdDocNb') val_3
      4  from test_xml;
    
    VAL_1                          VAL_2      VAL_3
    ------------------------------ ---------- ----------
    TWOPOWITHINVANDCREDITNOTE1     CINV       FDC01
    That's it; quite simple, eh?

  3. #3
    Join Date
    Aug 2011
    Posts
    4

    uploa xml file to oracle db

    Hii
    thanks for writing.

    In my file <TxInfAndSt> will be repeating .....n times and in each <TxInfAndSts> tag there is only one <OrgnlEndToEndId> ,followed by tags like <OrgnlTxRef> ,this will have repeated <Strd> tag ---having inv deatils . now i requrie my output in below format

    VAL_1 VAL_2 VAL_3
    ------------------------------ ---------- ----------
    TWOPOWITHINVANDCREDITNOTE1 CINV FDC01
    TWOPOWITHINVANDCREDITNOTE1 CINV FDC02
    TWOPOWITHINVANDCREDITNOTE1 CREN FDC03
    ..............................................
    ..............................................
    ..............................................
    TWOPOWITHINVANDCREDITNOTE2 CINV NDC01
    TWOPOWITHINVANDCREDITNOTE2 CINV NDC02
    TWOPOWITHINVANDCREDITNOTE2 CINV NDC03


    How to fetch this,please suggest .It will be a great help

    Also trie to upload the format as

    insert into test_xml (col)
    values (xmltype('<TxInfAndSts>
    <OrgnlPmtInfId>1234</OrgnlPmtInfId>
    <OrgnlInstrId>CC2011-05-12-Test</OrgnlInstrId>
    <OrgnlEndToEndId>TWOPOWITHINVANDCREDITNOTE1</OrgnlEndToEndId>
    <RfrdDocTp>
    <Cd>CINV</Cd>
    </RfrdDocTp>
    <RfrdDocNb>FDC01</RfrdDocNb>
    </TxInfAndSts>
    <TxInfAndSts>
    <OrgnlPmtInfId>1234</OrgnlPmtInfId>
    <OrgnlInstrId>CC2011-05-12-Test</OrgnlInstrId>
    <OrgnlEndToEndId>TWOPOWITHINVANDCREDITNOTE1</OrgnlEndToEndId>
    <RfrdDocTp>
    <Cd>CINV</Cd>
    </RfrdDocTp>
    <RfrdDocNb>FDC01</RfrdDocNb>
    </TxInfAndSts>'
    ));

    getting below error ,

    ORA-19202: Error occurred in XML processing
    LPX-00245: extra data after end of document
    Error at line 10
    ORA-06512: at "SYS.XMLTYPE", line 310
    ORA-06512: at line 1

    kindly help.
    Last edited by najeebalavi; 08-02-11 at 07:59.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's no top level in your XML, so it is not a valid XML data. You can't have "multiple TxInfAndSts" nodes without a parent one.

    I modified your data a little bit and added the "<head>" and - in order to distinguish different data - added the "no. 2" parts:
    Code:
    SQL> insert into test_xml (col)
      2    values (xmltype(
      3                 '<head>
      4                    <TxInfAndSts>
      5                       <OrgnlPmtInfId>1234</OrgnlPmtInfId>
      6                       <OrgnlInstrId>CC2011-05-12-Test</OrgnlInstrId>
      7                       <OrgnlEndToEndId>TWOPOWITHINVANDCREDITNOTE1</OrgnlEndToEndId>
      8                       <RfrdDocTp>
      9                         <Cd>CINV</Cd>
     10                       </RfrdDocTp>
     11                       <RfrdDocNb>FDC01</RfrdDocNb>
     12                     </TxInfAndSts>
     13                     <TxInfAndSts>
     14                       <OrgnlPmtInfId>1234</OrgnlPmtInfId>
     15                       <OrgnlInstrId>CC2011-05-12-Test</OrgnlInstrId>
     16                       <OrgnlEndToEndId>TWOPOWITHINVANDCREDITNOTE1 no. 2</OrgnlEndToEndId>
     17                       <RfrdDocTp>
     18                         <Cd>CINV no. 2</Cd>
     19                       </RfrdDocTp>
     20                       <RfrdDocNb>FDC01 no. 2</RfrdDocNb>
     21                     </TxInfAndSts>
     22                   </head>'));
    
    1 row created.
    Please, PLEASE, note the way I formatted my code and see the difference between your code and my code. Mine is a lot easier to read. Learn how to use CODE tags and, in the future posts, enclose your code into those tags.

    Now, selecting required information: you reference a node with [1] (the first one), [2] (the second one) etc. Here's how you'd do that:
    Code:
    SQL> select 'first' what,
      2         extractvalue(col, 'head/TxInfAndSts[1]/OrgnlEndToEndId') val_1,
      3         extractvalue(col, 'head/TxInfAndSts[1]/RfrdDocTp/Cd') val_2,
      4         extractvalue(col, 'head/TxInfAndSts[1]/RfrdDocNb') val_3
      5  from test_xml
      6  union all
      7  select 'second' what,
      8         extractvalue(col, 'head/TxInfAndSts[2]/OrgnlEndToEndId') val_1,
      9         extractvalue(col, 'head/TxInfAndSts[2]/RfrdDocTp/Cd') val_2,
     10         extractvalue(col, 'head/TxInfAndSts[2]/RfrdDocNb') val_3
     11  from test_xml
     12  ;
    
    WHAT   VAL_1                             VAL_2           VAL_3
    ------ --------------------------------- --------------- ---------------
    first  TWOPOWITHINVANDCREDITNOTE1        CINV            FDC01
    second TWOPOWITHINVANDCREDITNOTE1 no. 2  CINV no. 2      FDC01 no. 2
    
    SQL>

  5. #5
    Join Date
    Aug 2011
    Posts
    4

    XMl to oracle

    Hii

    Thanks for the replay, I am delighted

    But here in my case i will be getting a xml file in pain 002 format, so name of tags will reamin as it is
    ( Please see the attachemnt TRANSACTION DETAILS.txt --save it at ur end and change it to TRANSACTION DETAILS.xml it is my sample xml file that i have to process) at unix server now this file has 3 blocks
    1) GrpHdr ---header
    2) OrgnlGrpInfAndSts----staus accepted/rejected count
    3) TxInfAndSts.....deatils of transactions

    and each TxInfAndSts bolck will have
    only one <OrgnlEndToEndId> tag and multiple <Strd> tag
    ....which in turns hold the <RfrdDocNb> tag i.e invoices details in it

    so it will be like this
    - <Document xmlns="urn:iso:std:iso:20022:tech:xsdain.002.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    - <pain.002.001.02>

    + <GrpHdr>

    + <OrgnlGrpInfAndSts>

    <TxInfAndSts> 1-----have given just for reference but actually it will be only TxInfAndSts only

    --------<OrgnlEndToEndId> only once

    ----------------------inv 1
    -----------------------inv 2
    ........................inv n
    </TxInfAndSts>


    <TxInfAndSts> 2
    --------<OrgnlEndToEndId> only once

    ----------------------inv 1
    -----------------------inv 2
    ........................inv n
    </TxInfAndSts>


    </pain.002.001.02>
    </Document>

    now as it is unknown to me that how many records are coming ...it will be dynamic
    i cannot use union here in my code ....i guess first i have to find the count for <TxInfAndSts> tag and then inside each of this tag have to find count for <Strd> and from inside each <strd> tag have to fetach invoices details ....hope that this would have thrown light on my requriment


    Please correct me if am wrong , Please suggest me approch for this

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What will you do with these values, once you extract them?

    Anyway: as the number is unknown (moreover, my UNION was here just to make things simpler to understand), you could extract them in a loop (if using PL/SQL, where [1], [2], ... would be taken from the loop counter). Or, in SQL, use a row generator (for example, a LEVEL pseudocolumn). Here's an example based on my previous data:
    Code:
    SQL> select extractvalue(col, 'head/TxInfAndSts[' || level || ']/RfrdDocNb') val
      2  from test_xml
      3  connect by level <= (select count(*)
      4                       from test_xml,
      5                            table(xmlsequence(extract(test_xml.col, '//RfrdDocNb')))
      6                      );
    
    VAL
    ------------------------------
    FDC01
    FDC01 no. 2
    
    SQL>
    When do you plan to start using the CODE tags?

  7. #7
    Join Date
    Aug 2011
    Posts
    4

    XML to oracle....near to solution

    Hi Buddy
    I have now changed my approch i am using below code for getting xml file (as attached ) into db...and got partial success ,the only problem is i am not getting out put in properly ....please have a look


    code is....

    code ---as e2eid followed by inv


    CREATE OR REPLACE PROCEDURE APPS.XXNR_AP_XML_LOAD_TEST(dir VARCHAR2, inpfile VARCHAR2)
    is
    p xmlparser.parser;
    doc xmldom.DOMDocument;
    DOCELEM DBMS_XMLDOM.DOMELEMENT;

    ------ prints elements in a document
    PROCEDURE printElements_Test(doc xmldom.DOMDocument)
    IS
    -----All Elements---- prints the attributes of each element in a document
    nl1 xmldom.domnodelist;
    nl2 xmldom.domnodelist;
    nl3 xmldom.domnodelist;
    nl4 xmldom.domnodelist;

    ---Tag Count---
    len1 NUMBER;
    len2 NUMBER;
    len3 NUMBER;
    len4 NUMBER;
    len5 NUMBER;
    n1 xmldom.domnode;
    n2 xmldom.domnode;
    n3 xmldom.domnode;
    n4 xmldom.domnode;
    nnm xmldom.domnamednodemap;
    attrname varchar2(1000);
    attrval varchar2(1000);
    v_e2e_id varchar2(1000);
    v_inv_num varchar2(1000);
    v_inv_type varchar2(1000);
    v_status varchar2(1000);
    begin
    ---- get all elements
    nl1 := xmldom.getElementsByTagName(doc,'OrgnlEndToEndId') ;
    nl2 := xmldom.getelementsbytagname(doc,'TxSts');
    --- nl3 := XMLDOM.getElementsByTagName(doc,'Cd');
    nl4 := XMLDOM.getElementsByTagName(doc,'RfrdDocNb');
    ---- Count the number of each Tag
    len1 := xmldom.getLength(nl1);
    len2 := xmldom.getLength(nl2);
    -- len3 := xmldom.getLength(nl3); cd
    len4 := xmldom.getLength(nl4);

    dbms_output.put_line('OrgnlEndToEndId Tag Count : '||len1);
    dbms_output.put_line('TxSts Tag Count : '||len2);
    dbms_output.put_line('Cd Tag Count : '||len3);
    dbms_output.put_line('RfrdDocNb Tag Count : '||len4);
    dbms_output.put_line('------------------');

    ----------------------------------------------------------------------
    ---- Loop through elements
    ----------------------------------------------------------------------
    FOR i IN 0..len1-1 LOOP
    n1 := xmldom.item(nl1,i);
    n2 := xmldom.item(nl2,i);

    ----- dbms_output.put(xmldom.getNodeName(n) || ' ');
    n1 := xmldom.getFirstChild(n1);---element tag name
    n2 := xmldom.getFirstChild(n2);

    v_e2e_id := XMLDOM.GETNODEVALUE(N1);
    v_status := XMLDOM.GETNODEVALUE(N2);
    dbms_output.put_line('End2End ID : ' || v_e2e_id);--actual values
    dbms_output.put_line('Status : ' || v_status);--accp/reject
    dbms_output.put_line('--------second loop starts---');



    FOR j IN 0..len4-1 LOOP
    -- n3 := xmldom.item(nl3,h); cd
    n4 := xmldom.item(nl4,j);
    n4 := xmldom.getFirstChild(n4);

    v_inv_num := xmldom.getNodeValue(n4);

    dbms_output.put_line('Invoice Number : ' || v_inv_num);
    dbms_output.put_line('second loop ends ');

    END LOOP;


    dbms_output.put_line('first loop ends');
    End Loop;------first loop ends
    END printElements_Test;

    BEGIN
    -- new parser
    p := xmlparser.newParser;
    -- set some characteristics
    xmlparser.setValidationMode(p, FALSE);
    --xmlparser.setErrorLog(p, dir || '/' || errfile);
    xmlparser.setBaseDir(p, dir);
    -- parse input file
    xmlparser.parse(p, dir || '/' || inpfile);
    -- get document
    doc := xmlparser.getDocument(p);
    -- Print document elements
    dbms_output.put_line('The elements are: ');
    printelements_test(doc);
    END XXNR_AP_XML_LOAD_TEST;
    /
    and out put is as


    The elements are:
    OrgnlEndToEndId Tag Count : 2
    TxSts Tag Count : 2
    Cd Tag Count :
    RfrdDocNb Tag Count : 4

    ------------------
    End2End ID : TWOPOWITHINVANDCREDITNOTE1
    Status : ACSP
    --------second loop starts---
    Invoice Number : FDC01
    second loop ends
    Invoice Number : 1234512345
    second loop ends
    Invoice Number : FDC02
    second loop ends
    Invoice Number : 1234512346
    second loop ends
    first loop ends
    End2End ID : TWOPOWITHONEINVOICEANDCNOTE2
    Status : ACSP
    --------second loop starts---
    Invoice Number : FDC01
    second loop ends
    Invoice Number : 1234512345
    second loop ends
    Invoice Number : FDC02
    second loop ends
    Invoice Number : 1234512346
    second loop ends
    first loop ends

    here the problem is that for OrgnlEndToEndId should have displayed only its associating invoices i.e

    End2End ID : TWOPOWITHINVANDCREDITNOTE1
    Status : ACSP
    Invoice Number : FDC01
    Invoice Number : 1234512345


    End2End ID : TWOPOWITHINVANDCREDITNOTE2
    Status : ACSP
    Invoice Number : FDC02
    Invoice Number : 1234512346

    ..please help me out here

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by me, several days ago
    When do you plan to start using the CODE tags?
    Meaning: I'm not going to debug your unformatted code. Someone might, though. Be patient.

Posting Permissions

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