Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Introduction to Oracle XML, 16 of 27

### SQL Example: Retrieving the First Line Item Numbers

Thus if the user has stored purchase_order as an XML document, to retrieve the first line item numbers of a particular document, you can execute a query such as:

    SELECT extractNode(e.po_xml_column,
        `//line_item_list[1]/itemno')
    FROM    purchase_order_tab e
    WHERE e.pono = 100;

This example shows how SQL and XML querying can coexist. XPath behaves as a sublanguage within a SQL operator.

### extractNode and existsNode Operators

Two operators are provided, extractNode and existsNode:

- extractNode() operator extracts the given nodes from the XML document and returns an XML fragment.

- existsNode() operator on the other hand returns a boolean value indicating if the Xpath traversal on the document yielded any nodes.

The boolean operator is useful in predicates.

### SQL Example: Retrieving All Documents With Line Item = 100

For example, the following query lists all documents which contain a line item with itemno equal to 100.

    SELECT e.po_xml_column
    FROM purchase_order_tab e
    WHERE
     existsNode(e.po_xml_column,
                   `//line_item_list[itemno="100"]/') != 0;

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index