This discussion is archived
4 Replies Latest reply: Jul 28, 2013 1:40 AM by odie_63 RSS

INSERT INTO ... SELECT FROM XMLTABLE()

KnightOfBlueArmor Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

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