RE: SQL XML Question

Steve,


Do you think it matters that your insert statement looks incorrect?  You
have:

I could be wrong - but it looks unbalanced.

 

  <price type="US">10.00</price> 



 

Tom

 


--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged 
information which is intended solely for use by the individual or entity to 
whom it is addressed.  If you are not the intended recipient, you are hereby 
notified that any disclosure, dissemination, copying or distribution of this 
transmission or its attachments is strictly prohibited.  In addition, 
unauthorized access to this transmission may violate federal or State law, 
including the Electronic Communications Privacy Act of 1985.  If you have 
received this transmission in error, please notify the sender immediately by 
return e-mail and delete the transmission and its attachments.


________________________________


From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Booth.Steve
Sent: Friday, September 22, 2006 2:38 PM
To: Oracle-L@xxxxxxxxxxxxx
Subject: SQL XML Question

 

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: