11 Replies Latest reply: May 18, 2012 1:30 PM by 937454 RSS

    Default insertion for missing elements using DBMS_XMLSave.insertXML

    937454
      Hi,

      I am trying to insert default values for some columns, without having those column names as elements in the xml document. Is it possible to do so?? This is the code I am using for now, but it inserts null values for missing elements in the xml document.

      insCtx := DBMS_XMLSave.newContext ('TABLENAME'); -- get the context
      rows := DBMS_XMLSave.insertXML (insCtx, p_xmlDoc); -- insert the doc
      DBMS_XMLSave.closeContext (insCtx); -- close the handle
        • 1. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
          odie_63
          Hi,
          Is it possible to do so??
          I'm afraid not. Not with this approach.

          You can declare a default value for the column itself, or use something like this :
          SQL> create table my_table ( col1 number, col2 varchar2(30) );
           
          Table created
           
          SQL> 
          SQL> DECLARE
            2  
            3    p_xmldoc   clob :=
            4    '<ROWSET>
            5   <ROW>
            6    <COL1>1</COL1>
            7    <COL2>TEST1</COL2>
            8   </ROW>
            9   <ROW>
           10    <COL1>2</COL1>
           11   </ROW>
           12  </ROWSET>';
           13  
           14  BEGIN
           15  
           16    INSERT INTO my_table (col1, col2)
           17    SELECT col1, col2
           18    FROM XMLTable('/ROWSET/ROW'
           19           passing xmlparse(document p_xmldoc)
           20           columns col1 number       path 'COL1'
           21                 , col2 varchar2(30) path 'COL2' default 'X'
           22         )
           23    ;
           24  
           25  END;
           26  /
           
          PL/SQL procedure successfully completed
           
          SQL> select * from my_table;
           
                COL1 COL2
          ---------- ------------------------------
                   1 TEST1
                   2 X
           
          • 2. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
            937454
            Hi,

            Thank you for the approach, it works perfectly well, my other concern is the performance with this approach.

            Does this approach have any performance impact, if the xml document is making like 10000 row insertions??
            • 3. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
              odie_63
              Does this approach have any performance impact, if the xml document is making like 10000 row insertions??
              Yes, this could have a performance impact on large documents because of the in-memory processing.

              The general solution to this situation is to first store the document in an XMLType table (or column) and run the INSERT from that table.

              Here's a recent thread about it : {message:id=10327556}

              What's your database version btw?
              • 4. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                937454
                My db version is Oracle 11g.
                The approach you explained in the other post works for me as well. Thank you very much.

                I have a question though.
                Yesterday, before I posted the question, I did try creating a table with some columns set to default values and then tried inserting using:

                DBMS_XMLSave.insertXML (insCtx, p_xmlDoc);

                But what I noticed was the insertions happen, but the columns set with default values show as null (those columns elements were not included in the xml document).

                So there was no use creating the table with columns set to default values. I sthere a reason for that, or is it a limitation with using DBMS_XMLSave.insertXML (insCtx, p_xmlDoc)??
                • 5. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                  odie_63
                  But what I noticed was the insertions happen, but the columns set with default values show as null (those columns elements were not included in the xml document).
                  My current available version (11g XE) doesn't have a JVM so I can't test with DBMS_XMLSave (which is Java-based).

                  You should use DBMS_XMLStore instead, it has pretty much the same functionalities but built in the db kernel (C-based), so it's faster.

                  The following works for me :
                  SQL> drop table my_table;
                   
                  Table dropped
                   
                  SQL> create table my_table ( col1 number, col2 varchar2(30) default 'X' );
                   
                  Table created
                   
                  SQL> 
                  SQL> DECLARE
                    2  
                    3    xmldoc   clob :=
                    4    '<ROWSET>
                    5   <ROW>
                    6    <COL1>1</COL1>
                    7    <COL2>TEST1</COL2>
                    8   </ROW>
                    9   <ROW>
                   10    <COL1>2</COL1>
                   11   </ROW>
                   12  </ROWSET>';
                   13  
                   14    ctx      dbms_xmlstore.ctxHandle;
                   15    numrows  number;
                   16  
                   17  BEGIN
                   18  
                   19    ctx := dbms_xmlstore.newContext('MY_TABLE');
                   20    numrows := dbms_xmlstore.insertXML(ctx, xmldoc);
                   21    dbms_xmlstore.closeContext(ctx);
                   22  
                   23  END;
                   24  /
                   
                  PL/SQL procedure successfully completed
                   
                  SQL> select * from my_table;
                   
                        COL1 COL2
                  ---------- ------------------------------
                           1 TEST1
                           2 X
                   
                  • 6. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                    937454
                    Thank you. This worked as well.

                    Only thing I noticed with dbms_xmlstore.insertXML, was when my xml document had "<?xml version = "1.0" ?>" at the start, it gave an error "LPX-00209: PI names starting with XML are reserved"

                    When I removed it from the document, it ran successfully and the default values were also inserted into the table columns.

                    With the dbms_xmlsave.inserXML, it doesnt matter if I had "<?xml version = "1.0" ?>" at the start of the xml document, it would run without errors, though it wont insert the default column values.

                    There seems to be a give n take with the two approaches.
                    • 7. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                      odie_63
                      Only thing I noticed with dbms_xmlstore.insertXML, was when my xml document had "<?xml version = "1.0" ?>" at the start, it gave an error "LPX-00209: PI names starting with XML are reserved"
                      That's probably due to the XML document not starting exactly with "&lt;", like this :
                      SQL> DECLARE
                        2
                        3    xmldoc   clob := '
                        4  <?xml version="1.0"?>
                        5        <ROWSET>
                        6       <ROW>
                        7        <COL1>1</COL1>
                        8        <COL2>TEST1</COL2>
                        9       </ROW>
                       10       <ROW>
                       11       <COL1>2</COL1>
                       12      </ROW>
                       13     </ROWSET>';
                       14
                       15    ctx      dbms_xmlstore.ctxHandle;
                       16    numrows  number;
                       17
                       18  BEGIN
                       19
                       20    ctx := dbms_xmlstore.newContext('MY_TABLE');
                       21    numrows := dbms_xmlstore.insertXML(ctx, xmldoc);
                       22    dbms_xmlstore.closeContext(ctx);
                       23
                       24  END;
                       25  /
                      DECLARE
                      *
                      ERROR at line 1:
                      ORA-31011: XML parsing failed
                      ORA-19202: Error occurred in XML processing
                      LPX-00209: PI names starting with XML are reserved
                      ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
                      ORA-06512: at line 21
                      However, it runs OK if the prolog actually starts the character stream :
                      SQL> DECLARE
                        2
                        3    xmldoc   clob := '<?xml version="1.0"?>
                        4        <ROWSET>
                        5       <ROW>
                        6        <COL1>1</COL1>
                        7        <COL2>TEST1</COL2>
                        8       </ROW>
                        9       <ROW>
                       10       <COL1>2</COL1>
                       11      </ROW>
                       12     </ROWSET>';
                       13
                       14    ctx      dbms_xmlstore.ctxHandle;
                       15    numrows  number;
                       16
                       17  BEGIN
                       18
                       19    ctx := dbms_xmlstore.newContext('MY_TABLE');
                       20    numrows := dbms_xmlstore.insertXML(ctx, xmldoc);
                       21    dbms_xmlstore.closeContext(ctx);
                       22
                       23  END;
                       24  /
                      
                      PL/SQL procedure successfully completed.
                      With the dbms_xmlsave.inserXML, [...] it wont insert the default column values.
                      Now that's strange...
                      I understand there could be differences in the parsing implementation, but in the end both processes must issue an INSERT into the target table using plain SQL, so I really wonder why default values are not applied.

                      I'll try to reproduce when I have access to a Java-enabled db.
                      • 8. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                        937454
                        Cool, thanks for pointing out the error. I would prefer using xmlstore given its performance advantages over xmlsave. But it would be helpful to know why the xmlsave doesnt populate default value columns.
                        Thank you for all your responses. Been very helpful.
                        • 9. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                          odie_63
                          I've been experimenting with SQL Trace to get to the bottom of this.

                          Using DBMS_XMLGEN :
                          ctx := dbms_xmlstore.newContext('MY_TABLE');
                          numrows := dbms_xmlstore.insertXML(ctx, xmldoc);
                          dbms_xmlstore.closeContext(ctx);
                          Oracle generates two separate INSERT statements with hardcoded values :
                          =====================
                          PARSING IN CURSOR #2 len=60 dep=1 uid=61 oct=2 lid=61 tim=3616528890 hv=3243833327 ad='6ad75658'
                          INSERT INTO MY_TABLE ("COL2", "COL1") VALUES ('TEST1', '1') 
                          END OF STMT
                          PARSE #2:c=62500,e=70121,p=0,cr=172,cu=0,mis=1,r=0,dep=1,og=1,tim=3616528885
                          BINDS #2:
                          EXEC #2:c=0,e=155851,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=1,tim=3616684899
                          =====================
                          PARSING IN CURSOR #2 len=43 dep=1 uid=61 oct=2 lid=61 tim=3616686256 hv=3097054149 ad='6ad5eebc'
                          INSERT INTO MY_TABLE ("COL1") VALUES ('2') 
                          END OF STMT
                          PARSE #2:c=0,e=808,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=3616686251
                          BINDS #2:
                          EXEC #2:c=0,e=173,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=1,tim=3616686603
                          EXEC #1:c=171875,e=554810,p=0,cr=517,cu=30,mis=0,r=1,dep=0,og=1,tim=3616686963
                          =====================
                          That explains why the default value is applied on COL2 in this case.

                          However, this behaviour can affect performance badly if the document is large because it'll create a huge number of unshared cursors in the Shared Pool (depending on the CURSOR_SHARING parameter).
                          We can make Oracle use bind variables instead by telling it which column(s) to process :
                          ctx := dbms_xmlstore.newContext('MY_TABLE');
                          dbms_xmlstore.setUpdateColumn(ctx, 'COL1');
                          dbms_xmlstore.setUpdateColumn(ctx, 'COL2');
                          numrows := dbms_xmlstore.insertXML(ctx, xmldoc);
                          dbms_xmlstore.closeContext(ctx);
                          Now it generates a single statement with bind placeholders :
                          =====================
                          PARSING IN CURSOR #2 len=54 dep=1 uid=61 oct=2 lid=61 tim=4457101010 hv=1041360412 ad='6af210d8'
                          INSERT INTO MY_TABLE ("COL2", "COL1") VALUES (:2, :1) 
                          END OF STMT
                          PARSE #2:c=0,e=247,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=4457101006
                          =====================
                          However, since both columns are specified, the default value won't be applied.
                          That's the same behaviour we see using DBMS_XMLSAVE, whether or not we use setUpdateColumn().

                          Conclusion : it all makes sense now :)

                          If you need to specify default values, my suggestion is that you use XMLTable.
                          • 10. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                            937454
                            For your assistance:

                            create table t1 (
                            col1 varchar2(1),
                            col2 varchar2(2) default 'X')

                            declare
                            xml_data clob := '<?xml version = "1.0"?>
                            <ROWSET>
                            <ROW>
                            <COL1>A</COL1>
                            <COL2>Y</COL2>
                            </ROW>
                            <ROW>
                            <COL1>B</COL1>
                            <COL2></COL2>
                            </ROW>
                            <ROW>
                            <COL1>C</COL1>
                            </ROW>
                            </ROWSET>';
                            insctx DBMS_XMLSave.ctxType;
                            rows number;

                            begin
                            insCtx := DBMS_XMLSave.newContext ('t1'); -- get the context
                            rows := DBMS_XMLSave.insertXML (insctx, xml_data); -- insert the doc
                            DBMS_XMLSave.closeContext (insCtx); -- close the handle
                            end;

                            commit;

                            output:

                            COL1     COL2
                            A     Y
                            B     
                            C
                            • 11. Re: Default insertion for missing elements using DBMS_XMLSave.insertXML
                              937454
                              I was overwhelmed with soo much information a trace file could provide (I am pretty new to it).

                              But now I understand your response thorougly. Its preferable to use xmltable over the xmlstore/xmlsave.

                              But in cases where I dont have to specify default values for columns in the xml document, there I can declare columns using "dbms_xmlstore.setUpdateColumn(ctx, 'COL1');" to take advantage of bind variable concept in Oracle.

                              Also, by not using bind variable, Oracle would open a cursor for each insert statement execution. Well I cant imagine what it would have done to performance, if I had 10000 row insertions in xml doc. Its hard to comprehend, does it really open 10000 cursors, to process the document??

                              Thanks, there has been a lot to take off from this discussion.