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;