This discussion is archived
14 Replies Latest reply: Oct 3, 2013 1:05 PM by odie_63 RSS

how to read data from XML  variable and insert into table variable

8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
Currently Being Moderated


Hi Everyone,

 

I am new to oracle.Now I have to Create Store Procedure with input Parameter as XML data type. I have to read data from XML variable and insert into table variable.

 

Process

I will get XML data from TIBCO. TIBCO will call my Store Procedure it will my pass input as XML data.

 

I have to read it and insert into table variable.

 

Appriciate your help,

Thanks,

Srikar

  • 1. Re: how to read data from XML  variable and insert into table variable
    Jason_(A_Non) Expert
    Currently Being Moderated

    What version of Oracle as seen by select * from v$version

     

    What does some sample XML look like and your table?

     

    There are lots of examples of using XMLTable (10.2 and greater) or XQuery to parse XML, be it stored in a table or as a PL/SQL variable, around on the forum.  Approach the problem as how do I parse XML (something new to you) and how do I insert data into a table (a familiar concept).  After you get the two pieces, then you work on the approach to merge them, such as INSERT INTO ... SELECT  ... FROM ... XMLTable(.....

     

    Of course, none of this may apply given my lack of knowledge of your environment.

  • 2. Re: how to read data from XML  variable and insert into table variable
    8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
    Currently Being Moderated

    Hi Jason,

     

    Thank you very much for your reply.

     

    Kindly Consider any sample XML file.

     

    We are using Oracle SQL Developer(Software) version 3.0.

     

    Can you please provide any links:  Store Procedure having XML Type input parameter.

     

    And i have to read data from XML variable and load it into a table variable.

     

    Waiting for your reply.

     

    Appriciate Your help

     

    Thanks,

    Srikar

  • 3. Re: how to read data from XML  variable and insert into table variable
    Jason_(A_Non) Expert
    Currently Being Moderated

    Here are a couple of links I quickly pulled from recent forum posts

    How To : Load XML data into multiple tables | Odie's Oracle Blog

    In that one, you would want to change

    FROM tmp_xml t
      , XMLTable('/Locations/Location'
      passing t.object_value

    to simply

    FROM XMLTable('/Locations/Location'
      passing <pl/sql variable>

     

    Another would be

    XMLTABLE TAKING TIME.

     

    None of those are stored procedures, but a parameter is a parameter regardless of datatype when calling a procedure.  As long as you can access the variable name, you can accomplish what you need.

     

    It does not matter what software editing tool you are using, such as SQL Developer, as you could be using SQL*Plus and notepad.  What matters is what database you have and that is shown by running the following SQL statement.

    select * from v$version

     

    Without knowing anything about what you are doing, any sample I provide would be completely wrong and provide you no more help than reading examples on the forums and web in general.

  • 4. Re: how to read data from XML  variable and insert into table variable
    8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
    Currently Being Moderated

    Hi Josan,

     

    We are using oracle 11g.

     

    With the help of following link i am able to load the XMl data to a table.

     

    I Will get Million of records for each time. Can you Please verify the following code from the below link.

     

    How i can improve the performance of the Store Procedure.

     

    http://www.codeproject.com/Articles/54784/ADO-NET-DataTable-as-XML-parameter-to-an-Oracle-SQ.

     

    And sample XML data also avalible in the above link.

     

    Waiting for your reply.

     

    Thanks,

    Srikar V

  • 5. Re: how to read data from XML  variable and insert into table variable
    Jason_(A_Non) Expert
    Currently Being Moderated

    I would rewrite it to look something like

    CREATE GLOBAL TEMPORARY TABLE temp_xml_storage OF XMLTYPE XMLTYPE 
    -- store as only needed if 11.1.0.6 thru 11.2.0.1.  Becomes default in 11.2.0.2
    STORE AS SECUREFILE BINARY XML
    ON COMMIT DELETE ROWS;
    create or replace procedure InsertEmpDetail(p_EmpDetails XMLTYPE) is
    -- If cannot pass an XMLType, then pass a CLOB
    begin
      INSERT INTO temp_xml_storage values (p_EmpDetails);
      --INSERT INTO temp_xml_storage values (XMLType(p_EmpDetails));
      INSERT INTO your_table
      (col1, col2)
      SELECT col1, col2
        FROM temp_xml_storage txs,
             XMLTable('/Xpath/to/parent'
                      PASSING txs.xml_data
                      COLUMNS
                      col1   VARCHAR2(10) PATH 'node1',
                      col2   INTEGER      PATH 'node2');
                      
      COMMIT;
      
    end InsertEmpDetail;

    Reasons:

    In 11.2, Extract is deprecated.  XMLTable is one of the alternatives.

    There is no need to write a loop when a single SQL statement will accomplish the same task.

    I could not tell whether millions meant millions of calls a day or a single XML with millions of records.  The assumption is that your XML will be large in size.  By storing the XML into the database in a SECUREFILE BINARY XML format, Oracle pre-processes the XML and stores it in a format that speeds up access, compared to using a PL/SQL variable.

     

    Obviously this is just pseudo code and based off all the links I've given you, you should be able to finish the example.  If you get stuck post what you have so far and the question(s) you have.

  • 6. Re: how to read data from XML  variable and insert into table variable
    8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
    Currently Being Moderated

    Hi Jason,

     

    I Changed data type from XMLType to Clob.

     

    I am getting the following error. When i try to Create Store Procedure.

     

    Error : ora-00932 inconsistent datatypes expected-got clob.

     

    kindly help me to resolve the above error.

     

    Thanks,

    Srikar V

  • 7. Re: how to read data from XML  variable and insert into table variable
    Jason_(A_Non) Expert
    Currently Being Moderated

    That would be a run-time error, not a compile time error.

     

    Are you using the following line?

    INSERT INTO temp_xml_storage values (XMLType(p_EmpDetails));

    as that converts the CLOB into an XMLType before doing the insert.


    Of course without seeing what code you actually compiled or any other details, I'm completely guessing.  Please read
    Re: 2. How do I ask a question on the forums?

  • 8. Re: how to read data from XML  variable and insert into table variable
    8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
    Currently Being Moderated

    Hi Jason,

     

    Sorry for the late response.

     

    I Changed datatype from XMLTYPE to CLOB from the below line.

     

    Create or replace Procedure InsertEMPDetail(p_EMPDetails XMLTYPE)-----Here only one Place i changed datatype from XMLTYPE to CLOB

     

    Error report :

    SQL Error : ORA-00932 : inconsistent datatypes : excepted -got clob.

    00932. 00000 - "Inconsistent datatypes : excepted %s got %s"

     

     

    And below is my Oracle version details

    Oracle database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    PL/SQL Release 11.2.0.3.0 - Production.

    CORE 11.2.0.3.0  Production.

    TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

    NLSRTL Version - 11.2.0.3.0 - Production.

     

     

    Thanks,

    Srikar V

     


  • 9. Re: how to read data from XML  variable and insert into table variable
    odie_63 Guru
    Currently Being Moderated

     

    I Changed datatype from XMLTYPE to CLOB from the below line.

     

    Create or replace Procedure InsertEMPDetail(p_EMPDetails XMLTYPE)-----Here only one Place i changed datatype from XMLTYPE to CLOB

     

    Then you have to convert the CLOB to XMLType inside the procedure, as Jason already showed :

    INSERT INTO temp_xml_storage values (XMLType(p_EmpDetails));



  • 10. Re: how to read data from XML  variable and insert into table variable
    8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
    Currently Being Moderated

    Hi Odie,

     

    Still i am faceing the same Issue.

     

    Kindly find the below error.

     

    Error Report:

    SQL Error :ORA-00932 : inconsistent datatypes: excepted -got clob.

    00932.00000-"Inconsistent data types : excepted %s got %s.

     

    I changed the datatypes in the below places.

    Create or replace Procedure InsertEMPDetail(p_EMPDetails XMLTYPE)---------Here i changed to Clob

    and

    INSERT into temp_xml_storage_values(XMLTYPE(p_EmpDetails));------As suggested you i added XMLTYPE here.

     

    Waiting for your response.

     

    Thanks,

    Srikar V

  • 11. Re: how to read data from XML  variable and insert into table variable
    Jason_(A_Non) Expert
    Currently Being Moderated

    Show us the code you are using and the full error message.  The full error message would have line numbers associated to it that references lines in the procedure.  How is this being called for your testing?  I've never used TIBCO so I wonder if it is hiding the full error message based on what little you showed.  Have you tried calling this from SQL*Plus or via some other tool, such as SQLDeveloper / TOAD / PL/SQL Developer?

  • 12. Re: how to read data from XML  variable and insert into table variable
    8f6b7db2-0c32-4ece-8359-efaaa62ce080 Newbie
    Currently Being Moderated

    Hi Jason,

     

    Thanks for your response.

     

    I am Using SQLDeveloper.

     

    When iam Creating store Procedure i got the below error. Still i am not started testing the  Store Procedure.

     

    Kindly find the below Code which i used.

     

     

    -------Creation of Sample table--------

    Create table empdetails(
    EMPName varchar2(15),
    EMPAddress varchar2(50))

     

    This is the Sample XML Data. I want to insert into empdetails table.

    declare p_empdetails Clob:='<DocumentElement>
      <EmpDetails>
       <EmpName>Emp-1</EmpName>
       <EmpAddress>Emp-Addr-1</EmpAddress>
         </EmpDetails>
    <EmpDetails>
       <EmpName>Emp-2</EmpName>
       <EmpAddress>Emp-Addr-2</EmpAddress>
         </EmpDetails>
    </DocumentElement>';

     

                    ---------------------------------------Creating the Store Procedure-------------

     

    CREATE GLOBAL TEMPORARY TABLE temp_xml_storage OF XMLTYPE XMLTYPE       
    STORE AS SECUREFILE BINARY XML ON COMMIT DELETE ROWS
    create or replace procedure JasonInsertEmpDetail(p_EmpDetails Clob) is   
    begin  
    INSERT INTO temp_xml_storage values (XMLTYPE(p_EmpDetails));     
    INSERT INTO empdetails  (EMPName, EMPAddress)   
    SELECT EmpName, EmpAddress   
    FROM temp_xml_storage txs,  
             XMLTable('/Xpath/to/parent' PASSING txs.xml_data   
                     COLUMNS   
                      EmpName   VARCHAR2(15) PATH 'node1',  
                      EmpAddress   varchar2(50)      PATH 'node2');  
                         
      COMMIT;  
         
    end InsertEmpDetail;   
     ----------------------- ----Error Details---------------------------------------------------- 
      Error starting at line 6 in command:
    INSERT INTO empdetails  (EMPName, EMPAddress)   
    SELECT EmpName, EmpAddress   
    FROM temp_xml_storage txs,  
             XMLTable('/Xpath/to/parent' PASSING txs.xml_data   
                     COLUMNS   
                      EmpName   VARCHAR2(15) PATH 'node1',  
                      EmpAddress   varchar2(50)      PATH 'node2')
    Error report:
    SQL Error: ORA-00904: "TXS"."XML_DATA": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    committed.
    Error starting at line 17 in command:
    end InsertEmpDetail
    Error report:
    Unknown Command
     
     
     
     
     Thanks,
     Srikar V
  • 13. Re: how to read data from XML  variable and insert into table variable
    Jason_(A_Non) Expert
    Currently Being Moderated

    A few things.

    1) My original example included 2 DDL statements, one to CREATE a temporary table and one to CREATE a procedure.  In the example you provided, you broke the CREATE TABLE DDL by removing the last line, including the ;  The CREATE TABLE should be ran one time and one time only.

     

    2) For the Procedure, I missed one alteration when rewriting the code.  Replace

    PASSING txs.xml_data

    with

    PASSING txs.object_value

    Why object_value you ask?

    OBJECT_VALUE Pseudocolumn

    The Temporary table that is created has no defined column, so we need to use a pseudocolumn name instead.  You could create the Temp table with a XMLType column instead, so that other columns can be included as well and then you would simply use the column name from the DDL.

     

    Also, the error associated with that code is nothing like what you posted above, hence our requests to post the code and full error message when you have an error.  It avoids the guessing on our part.

  • 14. Re: how to read data from XML  variable and insert into table variable
    odie_63 Guru
    Currently Being Moderated

    And of course, you also have to use the real path expressions to target your XML data :

    INSERT INTO empdetails (EMPName, EMPAddress)

    SELECT EmpName, EmpAddress

    FROM temp_xml_storage txs

       , XMLTable(

           '/DocumentElement/EmpDetails'

           PASSING txs.object_value

           COLUMNS

             EmpName     VARCHAR2(15) PATH 'EmpName'

           , EmpAddress  VARCHAR2(50) PATH 'EmpAddress'

         )

    ;  

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points