SQL XML Question

From: Booth.Steve <Steve.Booth_at_we-energies.com>
Date: Fri, 22 Sep 2006 13:37:52 -0500
Message-ID: <D8B6ACB8EA9BBE4AB9A7EFBEA6986D62900ABE@mlxdpm1w3.we.dirsrv.com>

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...

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 22 2006 - 13:37:52 CDT