SQL XML Question

  • From: "Booth.Steve" <Steve.Booth@xxxxxxxxxxxxxxx>
  • To: <Oracle-L@xxxxxxxxxxxxx>
  • Date: Fri, 22 Sep 2006 13:37:52 -0500

I've been having problems dealing with retrieving data and appropriate
records based upon XPATH searching criteria in my PL/SQL. I'm running
9iR2. Here's the Table creation and sample XML Records:

CREATE TABLE T_XML_BOOKS 
( 
  ID_NBR    NUMBER, 
  DATE_XML  NUMBER, 
  TIME_XML  NUMBER, 
  XML_DATA  SYS.XMLTYPE 
); 
INSERT INTO T_XML_BOOKS ( ID_NBR, DATE_XML, TIME_XML, XML_DATA ) VALUES
( 
1, 20040713, 103203, XMLTYPE('<bookcatalog> 
 <book> 
  <title>History of Interviews</title> 
  <author> 
   <firstname>Juan</firstname> 
   <lastname>Smith</lastname> 
  </author> 
  <ISBN>99999-99999</ISBN> 
  <publisher>Oracle Press</publisher> 
  <publishyear>2003</publishyear> 
  <price type="US">10.00</price> 
 </book> 
</bookcatalog>')); 
INSERT INTO T_XML_BOOKS ( ID_NBR, DATE_XML, TIME_XML, XML_DATA ) VALUES
( 
2, 20040713, 103203, XMLTYPE('<bookcatalog> 
 <book> 
  <title>Dragonbone Chair</title> 
  <author> 
   <firstname>Tad</firstname> 
   <lastname>Williams</lastname> 
  </author> 
  <ISBN>1234-56789</ISBN> 
  <publisher>DAW</publisher> 
  <publishyear>1991</publishyear> 
  <price type="US">6.95</price> 
 </book> 
</bookcatalog>')); 
COMMIT; 

First Example (Which Works -- demonstrates data exists): 
   SELECT ID_Nbr, Date_XML, Time_XML,
extract(XML_DATA,'/').getStringVal() as XML_Data 
     FROM T_XML_BOOKS; 

Second (Which Works -- demonstrates use of extractValue): 
  SELECT  extractValue(xml_data, '/bookcatalog/book/title') as Title_Val

    FROM  t_XML_BOOKS; 

Third (doesn't return author, firstname or ISBN)? 

SELECT  extractValue(xml_data, '/bookcatalog/book/title') as Title_Val, 
        extractValue(xml_data, '/bookcatalog/book/title/author') as
auth, 
        extractValue(xml_data, '/bookcatalog/book/title/firstname') as
fname,    
        extractValue(xml_data, '/bookcatalog/book/title/ISBN') as
ISBN_Val 
  FROM  t_XML_BOOKS 

Fourth (Returns incorrect value from the existsNode function)? 

SELECT  extractValue(xml_data, '/bookcatalog/book/title') as Title_Val, 
        existsNode(xml_data, '/bookcatalog/book/title[QUERY="History of
Interviews"]') as Response 
  FROM  t_XML_BOOKS; 

I would have expected "Response" to be a "1" on the one record and "0"
on the 
other. 

What am I missing? 

 TIA 

Steve... 

Other related posts: