![Retrieving XML Data
To get Reference under PurchaseOrder
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') from mytable2
To get all values under LineItem value 1
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]')
FROM mytable2;
To get all Description from the XML Table
SELECT extract(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Description') FROM mytable2;
To get value of Description
SELECT extractValue(value(d),'/Description') FROM mytable2 p, table
(xmlsequence(extract(p.object_value,'/PurchaseOrder/LineItems/LineItem/Descr
iption'))) d WHERE
existsNode(p.object_value,'/PurchaseOrder[Reference="SBELL-
2002100912333601PDT"]') = 1;](https://image.slidesharecdn.com/xmldatausingoracle-150423100830-conversion-gate01/85/XML-Data-Using-Oracle-6-320.jpg)
XML Data Using Oracle
- 1. Storing & Retrieving XML data using Oracle Date: 11-Jul-2009 Place: St. Josephs College of Engineering
- 2. Introduction - XML Oracles XML Support Storing XML Data Retrieving XML Data References Q & A Agenda
- 3. Next generation of storage format for Office products Common platform for developing open standard applications Widely accepted and driven by W3C (www.w3.org) Interchange with External Organizations XML DB Native, flexible, high-performance, scalable storage & processing Introduction - XML
- 5. Creating Directory CREATE DIRECTORY xmldir AS 'c:txt'; Creating Table to store XML content CREATE TABLE mytable2 OF XMLType; Inserting into XML Table from file INSERT INTO mytable2 VALUES (XMLType(bfilename('XMLDIR', 'purchaseorder.xml'), nls_charset_id('AL32UTF8'))); Storing XML Data
- 6. Retrieving XML Data To get Reference under PurchaseOrder SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') from mytable2 To get all values under LineItem value 1 SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]') FROM mytable2; To get all Description from the XML Table SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Description') FROM mytable2; To get value of Description SELECT extractValue(value(d),'/Description') FROM mytable2 p, table (xmlsequence(extract(p.object_value,'/PurchaseOrder/LineItems/LineItem/Descr iption'))) d WHERE existsNode(p.object_value,'/PurchaseOrder[Reference="SBELL- 2002100912333601PDT"]') = 1;
- 8. Q&A Thanks for sharing your time with me Wish you all the best