Page 279
<Product PROD_ID=''20" name="Home Theatre Package with DVD-Audio/Video Play">
<category>Electronics</category>
<list_price>599.99</list_price>
</Product>
<Product PROD_ID="29" name="8.3 Minitower Speaker">
<category>Electronics</category>
<list_price>499.99</list_price>
</Product>
. . .
. . .
This listing is a snippet of the full results, giving you a flavor of what the code looks like. Now let's look at xmlagg().
xmlagg()
The previous query produces a separate XML document for each product. In many cases, there is a requirement such as to collect all the products for a product category together and transmit them in one document. You can do that by using the xmlagg() function in conjunction with a group by construct. xmlagg() is an aggregate function,
|
Line |
Important Points |
|
1 |
As stated earlier, the first xmlelement() function call creates the root element. In this case, the name of the root element is called "Product". |
|
2 |
xmlattibutes() may be used only as an argument of xmlelement(). In this query, there are two attributes defined: prod_id and name. The first attribute name defaults with the column name p.prod_id (when the column name is used as the default, it will be defined in uppercase). The second attribute includes an as clause, which is used to name the attribute "NAME". |
|
3 |
xmlforest() produces a forest of XML elements from the given list of arguments. The arguments may be string expressions with optional aliases. Again if the as clause is omitted, the column name is used to name the XML element. The element names defined here are category and price_list. |
|
4 |
The selection of data is being made against the PRODUCTS table. |
|
5 |
We are constraining the query to obtain only the PRODUCTS rows whose prod_category_id = 201 (Electronics). |
|
TABLE 8-1. Important Points about the SQLX Query |
