4 Replies Latest reply: Jul 28, 2013 3:40 AM by odie_63 RSS

    INSERT INTO ... SELECT FROM XMLTABLE()

    KnightOfBlueArmor

      I am attempting to insert using a query in 11.2.0.3.  The table is defined like this:

       

      CREATE TABLE book_master AS TABLE OF XMLTYPE XMLTYPE STORE AS SECUREFILE BINARY;
      

       

      The insert statement is structured this way:

       

      INSERT INTO book_master
      SELECT
          t.invt_data
      FROM
          XMLTable(
              XmlNamespaces('http://www.mrbook.com/InventoryData' AS "invtdata", 
                        'http://www.mrbook.com/book' AS "book"),  
              '/book:BOOKS'
          PASSING ?
          COLUMNS
               invt_data XMLTYPE PATH 'invtdata:INVT_DATA'
          ) t;
      

       

      The parameter '?' is passed in through JDBC.

       

      When I run this, I get the error:

      Error report:
      SQL Error: ORA-19010: Cannot insert XML fragments
      19010. 00000 -  "Cannot insert XML fragments" 
      *Cause:    XML fragments got from extractNode cannot be inserted into the database.
      *Action:   Convert the fragment into a proper XML document before insertion.
      

       

      Is there any way to cast the XMLTYPE returned by XMLTable as a full document, instead of a node?

        • 1. Re: INSERT INTO ... SELECT FROM XMLTABLE()
          odie_63

          Is there any way to cast the XMLTYPE returned by XMLTable as a full document, instead of a node?

          Well, the error message is pretty clear : you're trying to insert fragments, not a document (single-rooted content). It is not allowed.

          I guess the query returns multiple INVT_DATA elements, right?

          SQL> INSERT INTO book_master

            2  SELECT

            3      t.invt_data

            4  FROM

            5      XMLTable(

            6          '/BOOKS'

            7      PASSING xmltype('<BOOKS><INVT_DATA/><INVT_DATA/></BOOKS>')

            8      COLUMNS

            9           invt_data XMLTYPE PATH 'INVT_DATA'

          10      ) t;

          INSERT INTO book_master

          *

          ERROR at line 1:

          ORA-19010: Cannot insert XML fragments

           

           

          You have to build a document by wrapping the returned fragments into a single root element.

          • 2. Re: INSERT INTO ... SELECT FROM XMLTABLE()
            KnightOfBlueArmor

            You are correct that the structure looks like:

             

            <BOOK>

                 <INVT_DATA>

                 <INVT_DATA>

                 ....

            </BOOK>

             

            However, if I run just the SELECT query, without the insert, I do get single-rooted elements, which should be legal.  It is as if the internal datatype is node instead of document, and I'm wondering if I can cast it.

            • 3. Re: INSERT INTO ... SELECT FROM XMLTABLE()
              KnightOfBlueArmor

              OK, I'm wrong and I did something dumb:

               

              INSERT INTO book_master

              SELECT

                   t.invt_data

              FROM

              XMLTable(

                 '/BOOKS/INVT_DATA'

              PASSING xmltype('<BOOKS><INVT_DATA/><INVT_DATA/></BOOKS>')

              COLUMNS

              invt_data XMLTYPE PATH '.'

              ) t;


              This is the correct XQuery.

              • 4. Re: INSERT INTO ... SELECT FROM XMLTABLE()
                odie_63

                OK... you wanted each INVT_DATA as a separate row.

                That possibility didn't even occurred to me. Sometimes, when we're too focus on details, we don't think about zooming out to see the big picture