Selecting XML data
You can select all XML data that is stored in a particular column or only a subset of data from an XML column.
About this task
You can select all XML data that is stored in a particular column by specifying SELECT column name or SELECT *, just as you would for columns of any other data type. Alternatively, you can select only a subset of data from an XML column by using an XPath expression in a SELECT statement. XPath expressions identify specific nodes in an XML document.
To select a subset of data in an XML column, specify the XMLQUERY function in your SELECT statement with the following parameters:
- An XPath expression that is embedded in a character string constant. Specify an XPath expression that identifies which XML data to return.
- Any additional values to pass to the XPath expression, including the XML column name. Specify these values after the PASSING keyword.
Example: Suppose that you store
purchase orders as XML documents in the POrder column in the PurchaseOrders
table. You need to find in each purchase order the items whose product
name is equal to a name in the Product table. You can use the following
statement to find these values:
SELECT XMLQUERY('//item[productName = $n]'
PASSING PO.POrder,
P.name AS "n")
FROM PurchaseOrders PO, Product P;This statement returns the item elements in the POrder column that satisfy the criteria in the XPath expression.