RE: SQL XML Question

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 22 Sep 2006 15:14:25 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF681662@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Booth.Steve Sent: Friday, September 22, 2006 2:38 PM To: Oracle-L_at_FreeLists.org
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...

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

Received on Fri Sep 22 2006 - 14:14:25 CDT