Perl Cd Bookshelf [Electronic resources] نسخه متنی

Running the xmlupdate Application

You can run xmlupdate from the command line, passing in the specified parameters. You need to have Oracle Database 10g up and running locally. If connecting to a remote database, then you will need to set the service name of your DB instance to the conn variable. The following command line queries the SH schema in the database for all of the products whose list prices are greater than 1000 and returns the result as an XML document. It also queries that XML document for the item whose ProductID is 17 and updates its price to 999.99.

xmlupdate "select XMLELEMENT("ProductList", XMLAGG(XMLELEMENT(
"Product", XMLATTRIBUTES(PROD_ID AS "ProductID"),
XMLELEMENT("Name", PROD_NAME), XMLELEMENT("Price",
PROD_LIST_PRICE)))) AS result from products
where PROD_LIST_PRICE>1000"
"//Product[@ProductID=17]/Price/text()"
"999.99"

The preceding SQL/XML query constructs a single XML document containing multiple products by first specifying the root XML element <ProductList> and then using the XMLAGG() function to aggregate the set of products meeting the query constraint. The following is the resulting XML document retrieved as a XOB before it is updated. Note the price of 1099.99 for the first item.

<ProductList>
<Product ProductID="17">
<Name>Mini DV Camcorder with 3.5&quot; Swivel LCD</Name>
<Price>1099.99</Price>
</Product>
<Product ProductID=18>
<Name>Envoy Ambassador</Name>
<Price>1299.99</Price>
</Product>
</ProductList>

This next XML document is the same as the preceding one except the first item’s price has been updated to be 999.99.

<ProductList>
<Product ProductID="17">
<Name>Mini DV Camcorder with 3.5&quot; Swivel LCD</Name>
<Price>999.99</Price>
</Product>
<Product ProductID=18>
<Name>Envoy Ambassador</Name>
<Price>1299.99</Price>
</Product>
</ProductList>

This application simply prints the results to the screen. The XMLSaveDOM() function performing this can also be used to write the results to a file or stream. Alternatively, the XOB can be saved back to the database using standard OCI statement binding.