This discussion is archived
8 Replies Latest reply: Mar 27, 2013 3:28 PM by MarcoGralike RSS

XQuery and Relational Queries with RAW data

KnightOfBlueArmor Newbie
Currently Being Moderated
So, I have a table setup like this:
CREATE TABLE book_frag
(
  book_id RAW(16),
  xpath_id INT,
  last_modified_ts TIMESTAMP,
  created_date TIMESTAMP,
  book_data XmlType,
  PRIMARY KEY(book_id, xpath_id),
  FOREIGN KEY (book_id) REFERENCES book(book_id),
  FOREIGN KEY (xpath_id) REFERENCES book_xpath(xpath_id)
);

CREATE TABLE book_xpath
(
  xpath_id INT,
  book_element_name VARCHAR2(32),
  PRIMARY KEY(xpath_id)
);
I'm trying to put together a query that will combine the different book_frag XMLTypes into one. If I use this query, it works, but it's slow, and sometimes times out:
SELECT 
    e.book_id,
    
    e.title_txt,
    e.subj_txt,
    
    e.target_mkt,
    e.target_lcl_mkt,
    
    e.catg,
    e.lang_code,
    e.out_of_stock_reas,
    
    e.sucsr_title,
    e.sucsr_sub_txt,
    
    e.orig_copy_ref,
    e.orig_subj_txt,
    
    XMLQuery(
    'declare namespace invtdata="http://www.mrbook.com/InventoryData";
    
     copy $invtData := $orig/invtdata:inventory
     
     modify
     (
       for $bkRow in fn:collection("oradb:/BOOKSCHEMA/BOOK_FRAG")
       let $bk := $mpfRow/ROW/BOOK_DATA/node()
       where xs:string($bkRow/ROW/ID)=$bookId
       return insert node $bk into $invtData
     ) return $invtData'
     PASSING XmlType('<inventory xmlns="http://www.mrbook.com/InventoryData" />') AS "orig",
     CAST(e.book_id AS VARCHAR2(64)) AS "bookId" RETURNING CONTENT
     ) AS invt_data
FROM
  entity e
WHERE e.title_txt = 'Finite Variable Analysis' OR e.subj_txt = 'Finite Mathematics';
However, if I try this, it doesn't work at all.
SELECT 
    e.book_id,
    
    e.title_txt,
    e.subj_txt,
    
    e.target_mkt,
    e.target_lcl_mkt,
    
    e.catg,
    e.lang_code,
    e.out_of_stock_reas,
    
    e.sucsr_title,
    e.sucsr_sub_txt,
    
    e.orig_copy_ref,
    e.orig_subj_txt,
    
    XMLQuery(
    'declare namespace invtdata="http://www.mrbook.com/InventoryData";
    
     copy $invtData := $orig/invtdata:inventory
     
     modify
     (
       for $bkRow in fn:collection("oradb:/BOOKSCHEMA/BOOK_FRAG")
       let $bk := $mpfRow/ROW/BOOK_DATA/node()
       where $bkRow/ROW/ID=$bookId
       return insert node $bk into $invtData
     ) return $invtData'
     PASSING XmlType('<inventory xmlns="http://www.mrbook.com/InventoryData" />') AS "orig",
     e.id AS "bookId" RETURNING CONTENT
     ) AS invt_data
FROM
  entity e
WHERE e.title_txt = 'Finite Variable Analysis' OR e.subj_txt = 'Finite Mathematics';
The error that I receive with this query is:
ORA-00932: inconsistent datatypes: expected - got BINARY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 52 Column: 5
Is there a way to let XQuery use the RAW(16) data type without casting?

Legend

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