9 Replies Latest reply: May 13, 2013 2:38 PM by Marco Gralike RSS

    XMLTable with a WHERE clause?

    KnightOfBlueArmor
      I have a table that looks like this running under Oracle 11.2.0.3.4:
      CREATE TABLE book_master OF XMLTYPE XMLTYPE COLUMN STORE AS SECUREFILE BINARY XML;
      ...and I'm trying to run a query like this:
      SELECT
          e.title,
          e.isbn_nbr,
          e.prod_id,
          
          e.ctry_code,
          e.mkt_nbr,
          
          e.invt_data
          
      FROM 
          book_master g,
          XmlTable(
          XmlNamespaces('http://www.mrbook.com/InventoryData/book' AS "bk",
                        'http://www.mrbook.com/InventoryData' AS "invtdata"),
            '/invtdata:INVENT_DATA'
          
          PASSING g.OBJECT_VALUE
          COLUMNS
             title PATH '@title',
             isbn_nbr VARCHAR2(64) PATH 'bk:BOOK_ID/bk:ISBN',
             prod_id VARCHAR2(64) PATH 'bk:BOOK_ID/bk:PROD_ID',
             
             ctry_code NUMBER PATH 'bk:BOOK_ID/bk:CTRY_CODE',
             mkt_nbr NUMBER PATH 'bk:BOOK_ID/bk:MKT_NBR',
             
             invt_data XmlType PATH '.'
      ) e
      
      WHERE
        e.isbn_nbr = ?
      It was performing really badly, and then I realized that it was returning every row in the table for every iteration of the query. Is XMLExists the only possible way to return a unique row, assuming that ISBN_NBR and PROD_ID are unique? Can the WHERE clause not be used as a discriminator at all?

      Edited by: KnightOfBlueArmor on Apr 29, 2013 11:57 AM (added Oracle version)