In the race to provide the "best" XML support, RDBMS vendors have developed tools to XML-enable their products. The level of support can vary greatly, though. Most RDBMSs support XML as an output format by using dynamic resultset-based mapping. Only a few RDBMS products, however, have built-in support for updating databases using XML. In this section we will explore the XML capabilities of two of the more popular RDBMSs in the market: Microsoft SQL Server 2000 and Oracle 9i.
SQL Server 2000 provides a host of built-in features for delivering XML support. Most of these concentrate on enabling the user to translate relational data into XML documents. In addition, SQL Server 2000 has a mechanism for updating databases using XML.
SQL Server 2000 features a new clause of the SELECT statement that can be used to expose existing relational data as XML instead of as standard resultsets. This capability makes it trivial to expose resultsets generated by any SQL queries as XML documents. It also eliminates the need for you to write any custom code to manually do the transformation from resultsets to XML.
The new FOR XML clause supports three modes—RAW, AUTO, and EXPLICIT—for controlling the shape of the resulting XML.
The RAW mode is the simplest of the three modes. Unfortunately, it is also the most limited. The RAW mode provides a simple one-to-one row mapping to XML.
Let's start with a simple example. The following query will return all rows in the Employees table:
SELECT EmployeeID, FirstName, LastName FROM Employees
The Employees table, all other tables, and their data mentioned in this section can be found in the Northwind sample database that is installed by default with SQL Server 2000.
The resultant resultset is shown in Figure 4-5.
Figure 4-5 The Employees resultset.
To return an XML you can add the FOR XML RAW clause to the SELECT query in Listing 4-13.
Listing 4-13 raw.sql: An XML-generating simple query.
SELECT EmployeeID, FirstName, LastName FROM Employees FOR XML RAW
With this addition, Listing 4-14 will be returned.
Listing 4-14 raw.xml: XML generated by the query in Listing 4-13.
<row Employee FirstName="Nancy" LastName="Davolio"/> <row Employee FirstName="Andrew" LastName="Fuller"/> <row Employee FirstName="Janet" LastName="Leverling"/> <row Employee FirstName="Margaret" LastName="Peacock"/> <row Employee FirstName="Steven" LastName="Buchanan"/> <row Employee FirstName="Michael" LastName="Suyama"/> <row Employee FirstName="Robert" LastName="King"/> <row Employee FirstName="Laura" LastName="Callanan"/> <row Employee FirstName="Annee" LastName="Dodsworth"/>
As shown in Listing 4-14, any XML document returned by FOR XML does not automatically include any prolog, such as <?xml version="1"?>, and a root element. We will show you one way to deal with this issue in a later section where we discuss the XML Templates feature of SQL Server 2000.
The RAW mode of querying transforms each row in the resultset generated by the query into an XML <row> element. Each column is rendered as an attribute of the same name. One of the biggest limitations of the RAW mode is that it cannot be used to generate a hierarchical XML document from a multitable resultset. To illustrate this limitation, let's use the multitable query shown in Listing 4-15 to generate a resultset.
Listing 4-15 multitable.sql: A multitable query.
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice FROM Customers, Orders, [Order Details] WHERE Customers.CustomerID = Orders.CustomerID AND Orders.OrderID = [Order Details].OrderID AND Customers.CustomerID = 'BOLID' ORDER BY Customers.CustomerID, Orders.OrderID
The resultant resultset of this query is shown in Figure 4-6.
Figure 4-6 The resultset generated by Listing 4-15.
Again, to generate XML, we add the FOR XML RAW to our query. The result is shown in Listing 4-16.
Listing 4-16 raw2. xml: XML generated by the RAW mode query in Listing 4-15.
<row Customer CompanyName="Bolido Comidas preparadas"
Order OrderDate="1996-10-10T00:00:00" Product Quantity="24" UnitPrice="17.6"/> <row Customer CompanyName="Bolido Comidas preparadas"
Order OrderDate="1996-10-10T00:00:00" Product Quantity="16" UnitPrice="15.6"/> <row Customer CompanyName="Bolido Comidas preparadas"
Order OrderDate="1996-10-10T00:00:00" Product Quantity="50" UnitPrice="6.2"/> <row Customer CompanyName="Bolido Comidas preparadas"
Order OrderDate="1997-12-26T12:00:00" Product Quantity="40" UnitPrice="39"/> <row Customer CompanyName="Bolido Comidas preparadas"
Order OrderDate="1997-12-26T12:00:00" Product Quantity="20" UnitPrice="123.79"/> <row Customer CompanyName="Bolido Comidas preparadas"
Order OrderDate="1998-03-24T00:00:00" Product Quantity="40" UnitPrice="7"/>
As you can see, this does not reflect the hierarchy of our resultset Order Details under Orders under Customer. Luckily, the next mode we're going to discuss, the AUTO mode, is just as convenient to use but can also handle hierarchical data automatically.
For a more configurable and powerful way to expose a resultset that is just as simple to use as the RAW mode, SQL Server 2000 provides the AUTO mode. The biggest improvement over the RAW mode is that the AUTO mode can be used to expose a multitable resultset as a hierarchical XML document. The query in Listing 4-17 is the same query shown in Listing 4-15 except that it now uses the AUTO mode.
Listing 4-17 auto.sql: SQL query to illustrate the AUTO mode.
SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice FROM Customers, Orders, [Order Details] WHERE Customers.CustomerID = Orders.CustomerID AND Orders.OrderID = [Order Details].OrderID AND Customers.CustomerID = 'BOLID' ORDER BY Customers.CustomerID, Orders.OrderID FOR XML AUTO
The resultant XML is shown in Listing 4-18. As you will see, the AUTO mode has correctly rendered the hierarchical structure of our data in this XML.
Listing 4-18 auto.xml: XML generated using the query in AUTO.sql.
<Customers Customer CompanyName="Bolido Comidas preparadas"> <Orders Order OrderDate="1996-10-10T00:00"> <Order_Details Product Quantity="24" UnitPrice="17.6"/> <Order_Details Product Quantity="16" UnitPrice="15.6"/> <Order_Details Product Quantity="50" UnitPrice="6.2"/> </Orders> <Orders Order OrderDate="1997-12-26T00:00"> <Order_Details Product Quantity="40" UnitPrice="39"/> <Order_Details Product Quantity="20" UnitPrice="123.79"/> </Orders> <Orders Order OrderDate="1997-12-26T00:00"> <Order_Details Product Quantity="40" UnitPrice="7"/> </Orders> </Customers>
In AUTO mode each table in the FROM clause is represented by a corresponding XML element. The columns listed in the query are mapped to the appropriate attribute of the element. The order in which column names are specified in the SELECT query is significant because it's used to control the hierarchy of the resultant XML. Therefore, in Listing 4-18, <Customers> contains the <Orders> elements which in turn contain the <Order_Details> elements because this is the order in which the corresponding tables are listed in the FROM clause.
The EXPLICIT mode of the FOR XML clause is designed to enable the user to control the shape of the XML explicitly. Using this mode, however, requires a steep learning curve. The query must be written so that the additional information about the expected nesting is explicitly specified as part of the query. SQL Server 2000 directives can be used to specify additional configurations information at the column level. For example, the user can specify whether a particular column should be represented as an element or attribute in the XML.
The EXPLICIT mode expects the resultset resulting from an SQL query to follow a specific format. This resultset, called the Universal Table, should contain not just the data represented within the XML, but also metadata describing how the XML should be structured. This information is obtained by requiring the SQL query author to include two extract columns in the Universal Table and to name the columns according to a special encoding convention.
The two extra columns required by the EXPLICIT mode must be named Tag and Parent.
These two elements determine the parent-child hierarchy in the resultant XML tree. For example, the elements in Listing 4-19 will have the Tag and Parent values in Table 4-1.
Listing 4-19 skeletal1.xml: Skeletal XML used to illustrate the Tag and Parent values used in the EXPLICIT mode.
<Customers ...> ... <Orders ...> ... <Details ...> ... </Details> ... </Orders> ... </Customers>
Table 4-1 Tag and Parents Values of the Elements in Skeletal1.xml
Element | Tag | Parent |
---|---|---|
Customers | 1 | 0 |
Orders | 2 | 1 |
Details | 3 | 2 |
After the Tag and Parent columns you will need to include one column for each of the data items in the XML. For example, the following XML fragment will need four columns because it has four data items: CustomerID, ContactName, OrderID, and OrderDate.
<Customers Customer ContactName="..."> ... <Orders Order OrderDate="..."/> ... </Customers>
These columns in the Universal Table must be named according to a specific convention. This encoding convention is used to specify metadata about the element or attribute that will represent the column. Every column must be named using the following convention:
ElementName!TagNumber!AttributeName![Directive]
For example, the two data items, CustomerID and ContactName, in the following XML fragment can be described by the column names, Customers!1!CustomerID and Customers!1!ContactName.
<Customers Customer ContactName="..."> ...
Each row in the Universal Table identifies an occurrence of an element in the resultant XML. Therefore, there will be as many rows in the Universal Table as the number of elements in the resultant XML. Now that you've learned the theory, it's time for some real actions! Let's build the Universal Table for the XML first shown in Listing 4-18.
We know of 10 element occurrences (one occurrence of <Customers> plus three occurrences of <Orders> plus six occurrences of <Order_Details>), so we will need 10 rows in the Universal Table. Looking at the XML, it should be easy to fill in the Tag and Parent values. These values are shown in Table 4-2.
Table 4-2 Tag and Parent Values
Tag | Parent |
---|---|
1 | 0 |
2 | 1 |
3 | 2 |
3 | 2 |
2 | 1 |
3 | 2 |
2 | 1 |
3 | 2 |
3 | 2 |
3 | 2 |
Next we define the other columns. From the XML we find seven data items (CustomerID, CompanyName, OrderID, OrderDate, ProductID, Quantity, and UnitPrice), so we need seven extra columns in the Universal Table to describe these items. This is shown in Table 4-3.
Table 4-3 Data Columns of the Universal Table for Generating AUTO.xml
Containing Element Name | Containing Element Tag Number | Item Name | Universal Table's Column Name |
---|---|---|---|
<Customers> | 1 | CustomerID | Customers!1!CustomerID |
<Customersk> | 1 | ContactName | Customers!1!CompanyName |
<Orders> | 2 | OrderID | Orders!2!OrderID |
<Orders> | 2 | OrderDate | Orders!2!OrderDate |
<Order_Details> | 3 | ProductID | Order_Details!3!ProductID |
<Order_Details> | 3 | Quantity | Order_Details!3!Quantity |
<Order_Details> | 3 | UnitPrice | Order_Details!3!UnitPrice |
We now have the complete columns definition for our Universal Table. We also know how many rows we will have and their corresponding Tag and Parent values. What's left now is to put in the actual data, which you can see in Figure 4-7.
The first row corresponds to the first occurrence of the <Customers> element, and we need to store the values found in its CustomerID and ContactName attributes in the Customers!1!CustomerID and Customers!1!CompanyName columns, respectively. Note that we leave the rest of the columns blank (with a NULL value) because they don't have any values in the current element.
The second row corresponds to the first occurrence of the <Orders> element in the XML. This time we put the values stored in its OrderID and OrderDate attributes into the corresponding Orders!2!OrderID and Orders!2!OrderDate columns. We can leave the rest of the columns blank. Notice we have repeated the value for the CustomerID column from the previous row but left the CompanyName column blank. The reason for doing this will become clearer when we examine the SQL query needed to generate this Universal Table.
Figure 4-7 The completed Universal Table for generating the XML shown in Listing 4-18.
In the third row, we hit the first occurrence of the <Order_Details> element. Following the same rules described earlier, we fill this row out. Notice that this time we've copied the values for both the CustomerID and OrderID columns from the previous row while leaving the CompanyName and OrderDate blank. As promised, the reasons for this will be explained later in this section.
The fourth row is the same as the third row in terms of which columns it needs to complete; we just need to fill in the different column values. The fifth row is also similar to the third and fourth.
Now we have completed the first occurrence of <Order> and hit the second occurrence. We will use the same rules we employed in building the previous five rows in completing the second occurence here.
The Universal Table we will need for SQL Server 2000 to generate the appropriate XML in Listing 4-18 is now complete. However, given the table's relationship in Listing 4-16, we must use the proper SQL to generate a resultset like that shown in Figure 4-7. The UNION ALL statement is a perfect fit for achieving this effect. Listing 4-20 shows the SQL query required to generate the XML shown in Listing 4-18.
If we take a closer look at Listing 4-20, the first detail we notice is the three separate SQL queries joined by two UNION ALL statements. This corresponds to the number of distinct element types we have in AUTO.xml, namely <Customers>, <Orders>, and <Order-Details>.
Listing 4-20 explicit.sql: The EXPLICIT mode SQL query that can be used to generate the Universal Table shown in Figure 4-7.
SELECT 1 AS Tag, 0 AS Parent, CustomerID AS [Customers!1!CustomerID], CompanyName AS [Customers!1!ContactName], NULL AS [Orders!2!OrderID], NULL AS [Orders!2!OrderDate], NULL AS [Order_Details!3!ProductID], NULL AS [Order_Details!3!Quantity], NULL AS [Order_Details!3!UnitPrice] FROM Customers WHERE CustomerID = 'BOLID' UNION ALL SELECT 2, 1, CustomerID, NULL, Orders.OrderID, Orders.OrderDate, NULL, NULL, NULL FROM Orders, Customers WHERE Orders.CustomerID = Customers.CustomerID AND Customers.CustomerID = 'BOLID' UNION ALL SELECT 3, 2, CustomerID, NULL, OrderID, NULL, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice, FROM Orders, Customers, [Order Details] WHERE Orders.CustomerID = Customers.CustomerID AND [Order Details].OrderID = Orders.OrderID AND Customers.CustomerID = 'BOLID' ORDER BY Customers.CustomerID, Orders.OrderID FOR XML EXPLICIT
In the first query all the attribute values for the <Customers> element are obtained. We have given aliases to the columns according to the naming conventions required by the Universal Table. Notice that values for all the columns not related to the <Customers> element are set to NULL. Finally the values for the Tag and Parent columns are set to required values, in this case 1 and 0.
In the second query all the attribute values for the <Orders> element are retrieved. Notice that we've joined the Orders table to the Customers table, and we used the same criteria for selection of the Customers row as we used in the first query. This step ensures that the Customers we found in this query match those found in the first query. Also note that, except for CustomerID, all the columns not related to the <Orders> element are set to NULL. We've also assigned the values of 2 and 1 to the Tag and Parent columns.
In the final query we obtain all the attribute values for the <Order_Details> element. Notice that this time we have retained both the CustomerID and OrderID columns and left the other columns not related to the <Order_Details> element as NULL. We've also set up the same relationship between the Customers and Orders tables as the previous query to ensure that the rows returned in this query are consistent with those returned in the previous two queries.
In the EXPLICIT mode the resultset representing the Universal Table is scanned one row at a time in a forward-only manner, producing the resultant XML tree. To yield the proper XML hierarchy, use an ORDER BY clause in the query to ensure the correct order of the rows in the resultset.
The need for this ORDER BY clause also explains why we've repeated certain column values in the Universal Table but left others NULL. The proper order required by the EXPLICIT mode is ensured by having the values of the columns specified in the ORDER BY clause present in all the rows in the resultset.
It would be pointless to work through the complexity of using the EXPLICIT mode just so you can create XML documents that can be easily generated using the AUTO mode. The EXPLICIT mode is also designed to do much more. As an example, let's make a few modifications to the XML in Listing 4-18. This new XML is shown in Listing 4-21.
We've made some significant modifications in Listing 4-21. First we renamed all the elements; <Customers> became <Customer>, <Orders> became <Order>, and <Order_Details> became <Item>. Second we changed some of the data items into elements: CompanyName, OrderDate, UnitPrice, and Quantity. Generating an XML like this is impossible to achieve using the AUTO mode. With the EXPLICIT mode, however, it's almost trivial. Listing 4-22 shows the required query.
Listing 4-21 explicit.xml: An XML document based on AUTO.xml and customized with mixed attribute and element data values.
<Customer Customer> <CompanyName>Bolido Comidas preparadas</CompanyName> <Order Order> <OrderDate>1996-10-10T00:00</OrderDate> <Item Product> <Quantity>24</Quantity> <UnitPrice>17.6</UnitPrice> </Item> <Item Product> <Quantity>16</Quantity> <UnitPrice>15.6</UnitPrice> </Item> <Item Product> <Quantity>50</Quantity> <UnitPrice>6.2</UnitPrice> </Item> </Order> <Order Order> <OrderDate>1997-12-26T00:00</OrderDate> <Item Product> <Quantity>40</Quantity> <UnitPrice>39</UnitPrice> </Item> <Item Product> <Quantity>20</Quantity> <UnitPrice>123.79</UnitPrice> </Item> </Order> <Order Order> <OrderDate>1998-03-24T00:00</OrderDate> <Item Product> <Quantity>40</Quantity> <UnitPrice>7</UnitPrice> </Item> </Order> </Customer>
Listing 4-22 explicit2.sql: An SQL query that can be used to generate the XML in EXPLICIT.xml.
SELECT 1 AS Tag, 0 AS Parent, CustomerID AS [Customer!1!CustomerID], ContactName AS [Customer!1!CompanyName!element], NULL AS [Order!2!OrderID], NULL AS [Order!2!OrderDate!element], NULL AS [Item!3!ProductID], NULL AS [Item!3!Quantity!element], NULL AS [Item!3!UnitPrice!element] FROM Customers WHERE CustomerID = 'BOLID' UNION ALL SELECT 2, 1, CustomerID, NULL, Orders.OrderID, Orders.OrderDate, NULL, NULL, NULL FROM Orders, Customers WHERE Orders.CustomerID = Customers.CustomerID AND Customers.CustomerID = 'BOLID' UNION ALL SELECT 3, 2, CustomerID, NULL, OrderID, NULL, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice, FROM Orders, Customers, [Order Details] WHERE Orders.CustomerID = Customers.CustomerID AND [Order Details].OrderID = Orders.OrderID AND Customers.CustomerID = 'BOLID' ORDER BY Customers.CustomerID, Orders.OrderID FOR XML EXPLICIT
Notice that all the changes required are isolated to the first query, and all we had to do was specify different aliases for the columns affected. Specifically, all the elements were renamed accordingly (Customer, Order, and Item). In addition, we included the element directive for those columns that we wanted to render as elements instead of attributes.
Many other options are available to tweak the behavior of the EXPLICIT mode. We have shown you the basics in this section, and if you're interested we encourage you to consult your SQL Server 2000 documentation for further details.
SQL Server 2000 provides a new feature, called XML Templates, that can be used to wrap multiple FOR XML queries or stored procedures into a valid XML document. XML Templates also support advanced features such as runtime parameter substitution and support for XSLT of the resulting XML document.
An XML Template is an XML document that contains the query strings or stored procedures to execute. Listing 4-23 shows how you can wrap a RAW mode query that returns all rows from the Employees table within an XML Template.
Listing 4-23 xml_template.xml: A simple XML Template.
<?xml version="1.0"?> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:query> SELECT EmployeeID, FirstName, LastName FROM Employees FOR XML RAW </sql:query> </ROOT>
In Listing 4-23 the <ROOT> element specifies the root element of the resulting XML document. You can, of course, call it anything you want, such as <Customers>. This root element serves to make the resulting XML document valid. Notice that we've also included the XML Template namespace definition in the <ROOT> element. The SQL Server 2000 XML Template XSD is identified by a URI of urn:schemas-microsoft-com:xml-sql.
As shown in Listing 4-23, you use the <query> element to specify a query or stored procedure to execute. You can have as many <query> elements as you like, and the results of all the queries are included together in the final XML document.
Now we need to actually execute our XML Template to retrieve the resulting XML document. One of the methods SQL Server 2000 provides for achieving this is using an HTTP URL. This feature requires you to set up a virtual directory and a virtual name of template type in Microsoft Internet Information Server (IIS) by using the IIS utility, Virtual Directory Management for SQL Server 2000. For instructions on configuring IIS this way, please consult your SQL Server 2000 documentation. Once IIS is configured properly, you can execute our XML Template using a URL similar to the following:
http://localhost/nwind/templates/XML_Template1.xml
In the above URL nwind and templates are examples of the virtual directory and virtual name, respectively. Listing 4-24 shows the resulting XML document when this URL is opened in a browser.
Listing 4-24 xml_template_result.xml: XML document generated from an XML Template.
<?xml version="1.0"?> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <row Employee FirstName="Nancy" LastName="Davolio"/> <row Employee FirstName="Andrew" LastName="Fuller"/> <row Employee FirstName="Janet" LastName="Leverling"/> <row Employee FirstName="Margaret" LastName="Peacock"/> <row Employee FirstName="Steven" LastName="Buchanan"/> <row Employee FirstName="Michael" LastName="Suyama"/> <row Employee FirstName="Robert" LastName="King"/> <row Employee FirstName="Laura" LastName="Callanan"/> <row Employee FirstName="Annee" LastName="Dodsworth"/> </ROOT>
As shown, SQL Server 2000 executed our RAW mode query and inserted the result within the <ROOT> element.
For users who need to be able to shape the XML extensively but find using the EXPLICIT mode too difficult, SQL Server 2000 provides a new feature referred to as XML View. XML View is basically an XML-to-relational database mapping mechanism that allows the user to fine-tune the shape of the XML. In addition, queries against XML Views are made by using a subset of the XPath language. This approach is similar to creating SQL views using CREATE VIEW statements and then querying against these views using SELECT statements.
In XML, View XML-to-relational database mapping is specified through a number of Microsoft-defined annotations to the XSD schema language. These annotations allow the user to specify the mapping between elements and attributes in the XSD schema to tables and columns in the databases. They are also used to specify the hierarchical relationships in the XML. For example, suppose we want to use the same tables and relationships as those used in generating the XML shown in Listing 4-18, but want a resultant XML that looks like that shown in Listing 4-25.
Listing 4-25 xml_view.xml: Another customized version of Listing 4-18 used to illustrate the capability of the XML View feature.
<Customer Customer> <Name>Bolido Comidas preparadas</Name> <Order Order> <Date>1996-10-10T00:00</Date> <Item> <ProductID>4</ProductID> <Quantity>24</Quantity> <Price>17.6</Price> </Item> <Item> <ProductID>57</ProductID> <Quantity>16</Quantity> <Price>15.6</Price> </Item> <Item> <ProductID>75</ProductID> <Quantity>50</Quantity> <Price>6.2</Price> </Item> </Order> <Order Order> <Date>1997-12-26T00:00</Date> <Item> <ProductID>17</ProductID> <Quantity>40</Quantity> <Price>39</Price> </Item> <Item> <ProductID>29</ProductID> <Quantity>20</Quantity> <Price>123.79</Price> </Item> </Order> <Order Order> <Date>1998-03-24T00:00</Date> <Item> <ProductID>52</ProductID> <Quantity>40</Quantity> <Price>7</Price> </Item> </Order> </Customer>
To be able to generate an XML document like this, SQL Server 2000 requires that we specify an XSD with the appropriate mapping annotations. This special XSD is known as a mapping schema. The annotations are used to specify which XML elements correspond to which columns in a database table. They also specify the parent-child relationships between the tables.
The XSD schema for our example is shown here in Listing 4-26. Notice that all annotations defined in a mapping schema belong to the SQL Server 2000 XML View namespace, namely urn:schemas-microsoft-com:mapping-schema. In Listing 4-24 this namespace is mapped to the sql prefix.
Listing 4-26 xml_view.xsd: Mapping schema for the XML in Listing 4-25.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Customer" sql:relation="Customers"> <xsd:complexType> <xsd:sequence> <xsd:element name="Name" sql:field="CompanyName" type="xsd:string" /> <xsd:element ref="Orders" sql:relationship="relOrders"/> </xsd:sequence> <xsd:attribute name="CustomerID" sql:field="CustomerID" type="xsd:string"/> </xsd:complexType> </xsd:element> <xsd:element name="Order" sql:relation="Orders"> <xsd:complexType> <xsd:sequence> <xsd:element name="Date" sql:field="OrderDate" type="xsd:dateTime"/> <xsd:element ref="Item" sql:relationship="relItems"/> </xsd:sequence> <xsd:attribute name="OrderID" type="xsd:int"/> </xsd:complexType> </xsd:element> <xsd:element name="Item" sql:relation="Order Details"> <xsd:complexType> <xsd:element name="ProductID" sql:field="ProductID" type="xsd:string"/> <xsd:element name="Price" sql:field="UnitPrice" type="xsd:int"/> <xsd:element name="Quantity" sql:field="Quantity" type="xsd:int"/> </xsd:complexType> </xsd:element> <xsd:annotation> <xsd:appinfo> <sql:relationship name="relOrders" parent="Customers" parent-key="CustomerID" child="Orders" child-key="CustomerID"/> <sql:relationship name="relDetails" parent="Orders" parent-key="OrderID" child="Order Details" child-key="OrderID"/> </xsd:appinfo> </xsd:annotation> </xsd:schema>
To specify the mapping between elements in the XML and columns in a database table, you use the sql:field attribute. The table where these columns can be found is specified using the sql:relation attribute. For example, the following fragment from Listing 4-26 defines the complex element <Item> as having subelements <ProductID>, <Price>, and <Quantity> mapped to the ProductID, UnitPrice, and the Quantity fields of the Details table respectively:
<xsd:element name="Item" sql:relation="Order Details"> <xsd:complexType> <xsd:element name="ProductID" sql:field="ProductID" type="xsd:string"/> <xsd:element name="Price" sql:field="UnitPrice" type="xsd:int"/> <xsd:element name="Quantity" sql:field="Quantity" type="xsd:int"/> </xsd:complexType> </xsd:element>
In addition to the column and table mapping specifications, we said earlier that XML View also allows you to specify the hierarchical shape of the XML. This is specified using the <sql:relationship> element. You need to use this annotation in two places.
First you need to specify how tables/views are to be joined. These join relationships are defined inside an annotation section using the first form of <sql:relationship>. The following fragment shows one of the join relationships we have defined in Listing 4-26:
<sql:relationship name="relOrders" parent="Customers" parent-key="CustomerID" child="Orders" child-key="CustomerID"/>
Each join relationship, or table join, is defined by an <sql:relationship> element. A name attribute is used to uniquely identify this relationship so that it can be referenced elsewhere in the XSD. Which tables and columns are involved in the join are defined by the four self-explanatory attributes parent, parent-key, child, and child-key. The previous fragment means that we are joining the Orders table to the Customers table using the CustomerID column of each of these tables as the keys. This is functionally equivalent to the following WHERE clause:
WHERE Customers.CustomerID = Orders.CustomerID
Now that you have the join relationships defined, you can use them to specify corresponding elements to be included in the resultant XML by defining an element that references one of these join relationships. Here the sql:relationship attribute is used to specify the name of the join relationship defined in the annotation section:
<xsd:element ref="Item" sql:relationship="relItems"/>
Now that we have completed the design of our schema, how do we ask SQL Server 2000 to create an XML document using this schema? The answer is that SQL Server 2000 features a subset of the XPath language for querying against XML Views. For example, to perform the equivalent of the SELECT WHERE Customers.CustomerID = `BOLID' that we've used in our earlier examples, we can use the following XPath, which will return an XML like that shown in XML_view.xml:
/Customer[@CustomerID='BOLID']
You don't have to stop there. You can restrict the XML document to return only the list of the <Item> elements without the <Customer> and <Order> elements. Here is the XPath to achieve this:
/Customer[@CustomerID='BOLID'/Order/Item]
Using this string will return an XML fragment similar to the following:
<Item> <ProductID>918234</ProductID> <Price>12.00</Price> <Quantity>4</Quantity> </Item> <Item> <ProductID>170018</ProductID> <Price>20.00</Price> <Quantity>2</Quantity> </Item> <Item> <ProductID>100144</ProductID> <UnitPrice>49.95</UnitPrice> <Quantity>10</Quantity> </Item> <Item> <ProductID>710050</ProductID> <UnitPrice>199.95</UnitPrice> <Quantity>1</Quantity> </Item> <Item> <ProductID>918234</ProductID> <UnitPrice>12.00</UnitPrice> <Quantity>10</Quantity> </Item> <Item> <ProductID>001460</ProductID> <UnitPrice>299.95</UnitPrice> <Quantity>1</Quantity> </Item>
SQL Server 2000 also features a resultset provider, called OPENXML, that provides a resultset view over an XML document. This feature can be useful when you want to send an XML document as a parameter to an SQL statement or stored procedure and then insert the data found in the document into the appropriate relational tables.
Functionally, OPENXML is similar to other resultset providers such as OPENQUERY and OPENROWSET. The major difference is that, with OPENXML, you need to use a special stored procedure to prepare your XML document and clear it from memory with another stored procedure after you're done.
To prepare your XML document, you call the sp_xml_prepareDocument stored procedure. This loads the XML document, passed in through the string parameter, into memory, preparses it for optimal query performance, and turns it into a resultset. The procedure, sp_xml_prepareDocument, returns an INT value, which represents a handle to the XML resultset in memory. You use this handle when you want to use the associated XML resultset. When you're done using this resultset, pass the handle to the sp_xml_removeDocument stored procedure to clear up the used memory.
The SQL script in Listing 4-27 shows how you can use OPENXML to insert data defined in a specific XML document into the Employees table.
You can see that the sp_xml_prepareDocument stored procedure takes two parameters. The first is an output parameter and is the returned handle value. The second is the XML document represented as a string. Depending on the size of the XML string, this input parameter can be CHAR, TEXT, or VARCHAR. You can also use the Unicode-ready version of these data types (NCHAR, NTEXT, and NVARCHAR).
Listing 4-27 openxml.sql: Inserting records using OPENXML.
DECLARE @xmlHnd INT DECLARE @xmlDoc VARCHAR( 1000 ) -- prepare the XML document SET @xmlDoc = '<ROOT> <Employees Employee FirstName="Albert" LastName="Adams"/> <Employees Employee FirstName="Ed" LastName="Davis"/> <Employees Employee FirstName="Joe" LastName="Young"/> </ROOT>' -- Load and parse the XML document EXEC sp_xml_prepareDocument @xmlHnd OUTPUT, @xmlDoc -- Insert the three Employees record specified in the XML INSERT Employees SELECT * FROM OPENXML( @xmlHnd, '/ROOT/Employees', 0 ) WITH ( EmployeeID INT, FirstName VARCHAR( 20 ), LastName VARCHAR( 20 ) ) -- release reference to the XML document EXEC sp_xml_removeDocument @hXmlDoc
To turn the XML document identified by the handle that is returned by sp_xml_prepareDocument into a resultset, you use the OPENXML resultset function. OPENXML has the following syntax:
OPENXML( Handle, XPath[, Attributes] ) WITH SchemaDeclaration
In our previous openxml.sql example our OPENXML call looks like the following:
OPENXML( @xmlHnd, '/ROOT/Employees', 0 ) WITH Employees
This code specifies that our XML document, identified by the xmlHnd handle, is attribute-centric by passing a value of 0 to the Attributes parameter (the third parameter). Next the XPath identifies that we intend to use the nodes found under the /ROOT/Employees element to generate the rows in the OPENXML resultset. Finally the WITH clause specifies the schema that will be used to construct the resultset.
SQL Server 2000 is not the only database that supports a wealth of XML integration. Oracle 9i also provides a number of powerful tools for building XML applications. However, compared to SQL Server 2000, these features are more complicated and usually require more programming.
Most of the XML features provided by Oracle 9i can be found in the Oracle XML Development Kits (XDK). Oracle makes XDKs for Java, C/C++, and PL/SQL. Components supplied in these XDKs include XML parsers, XLST processors, and other tools. One of these tools is the XML SQL Utility (XSU), which is used to generate XML documents from SQL queries.
Oracle 9i also provides a new data type, called XMLType, that can be used to natively persist both data-centric and document-centric XML documents in the database. This feature simplifies the task of using the database as an XML repository. Oracle 9i supports indexing the XML documents stored using XMLType through the Oracle Text package. Once indexed, these XML documents can be searched as text or as document sections.
In the code examples in the rest of this section we will use the tables and objects created and populated using the oracle_script.sql SQL script and displayed in Listing 4-28. If you are running these examples, use this script to create and populate your tables.
Listing 4-28 oracle_script.sql: A creation script for sample tables.
-- create and populate the Dept table CREATE TABLE Dept ( Dept_No NUMBER PRIMARY KEY Dept_Name VARCHAR2( 20 ) NOT NULL ); / INSERT INTO Dept VALUES ( 1, `IT' ); / INSERT INTO Dept VALUES ( 2, `Marketing' ); / INSERT INTO Dept VALUES ( 3, `HR' ); / INSERT INTO Dept VALUES ( 4, `Accounting' ); / INSERT INTO Dept VALUES ( 5, `Operations' ); / -- create and populate the Employee table CREATE TABLE Employee ( Employee_No NUMBER PRIMARY KEY, First_Name VARCHAR2( 20 ), Last_Name VARCHAR2( 20 ) NOT NULL, Dept_No NUMBER REFERENCES Dept( Dept_No ) ); / INSERT INTO Employee VALUES ( 1, 'Sean', 'Chai', 1 ) / INSERT INTO Employee VALUES ( 2, 'Aaron', 'Con', 1 ) / INSERT INTO Employee VALUES ( 3, 'Andrew', 'Dixon', 2 ) / INSERT INTO Employee VALUES ( 4, 'Ted', 'Bremer', 3 ) / INSERT INTO Employee VALUES ( 5, 'Randall', 'Boseman', 4 ) / INSERT INTO Employee VALUES ( 6, 'Jane', 'Clayton', 1 ) / INSERT INTO Employee VALUES ( 7, 'Peter', 'Connelly', 2 ) / INSERT INTO Employee VALUES ( 8, 'Eva', 'Corets', 3 ) / INSERT INTO Employee VALUES ( 9, 'Nate', 'Sun', 5 ) /
XSU is supplied as part of the Oracle XDK and is designed to enable the user to perform the following three tasks:
The various XSU functionalities are accessible through a Java API, a PL/SQL API, or a command-line utility. For the examples in this section we will be using the Java API to illustrate using XSU. XSU uses a set of customizable resultset-based mapping rules, called SQL-XML mapping, to go from SQL to XML and vice versa. Let's take a look at what the default mapping does. Submit the following SQL query to XSU:
SELECT Employee_ID, First_Name, Last_Name FROM Employee ORDER BY Employee_ID
This simple SQL query generates Listing 4-29.
Listing 4-29 xsu_generated_xml1.xml: XML generated by XSU using default mapping.
<?xml version="1.0"?> <ROWSET> <ROW num="1"> <EMPLOYEE_ID>1</EMPLOYEE_ID> <FIRST_NAME>Sean</FIRST_NAME> <LAST_NAME>Chai</LAST_NAME> </ROW> <ROW num="2"> <EMPLOYEE_ID>2</EMPLOYEE_ID> <FIRST_NAME>Aaron</FIRST_NAME> <LAST_NAME>Con</LAST_NAME> </ROW> <ROW num="3"> <EMPLOYEE_ID>3</EMPLOYEE_ID> <FIRST_NAME>Andrew</FIRST_NAME> <LAST_NAME>Dixon</LAST_NAME> </ROW> <ROW num="4"> <EMPLOYEE_ID>4</EMPLOYEE_ID> <FIRST_NAME>Ted</FIRST_NAME> <LAST_NAME>Bremer</LAST_NAME> </ROW> <ROW num="5"> <EMPLOYEE_ID>5</EMPLOYEE_ID> <FIRST_NAME>Randall</FIRST_NAME> <LAST_NAME>Boseman</LAST_NAME> </ROW> <ROW num="6"> <EMPLOYEE_ID>6</EMPLOYEE_ID> <FIRST_NAME>Jane</FIRST_NAME> <LAST_NAME>Clayton</LAST_NAME> </ROW> <ROW num="7"> <EMPLOYEE_ID>7</EMPLOYEE_ID> <FIRST_NAME>Peter</FIRST_NAME> <LAST_NAME>Connelly</LAST_NAME> </ROW> <ROW num="8"> <EMPLOYEE_ID>8</EMPLOYEE_ID> <FIRST_NAME>Eva</FIRST_NAME> <LAST_NAME>Cosets</LAST_NAME> </ROW> <ROW num="9"> <EMPLOYEE_ID>9</EMPLOYEE_ID> <FIRST_NAME>Nate</FIRST_NAME> <LAST_NAME>Sun</LAST_NAME> </ROW> </ROWSET>
With the default mapping, the root element is called <ROWSET>. <ROWSET> contains one or more <ROW> child elements, each of which is used to represent a row in the resultset. <ROW> has a single attribute, called num and is used to indicated the logical row number for the associated row. Finally each <ROW> element contains one or more child elements whose names and contents represent the columns in the resultset.
To generate XML documents from resultsets you will need to write some Java code. All the functionality related to generating XML documents is provided by the oracle.xml.sql.query.OracleXMLQuery Java class. The general steps required to generate an XML document using XSU are
Listing 4-30 shows a simple example of a Java console application that uses XSU to generate an XML document from a query.
Listing 4-30 xsu_select.java: Using XSU to generate an XML document from an SQL query.
import java.sql.*; import oracle.jdbc.driver.*; import oracle.xml.sql.query.*; /* class to test the String generation! */ public class XSU_Select { public static void main( String argv[] ) throws Exception { /* connect to Oracle */ DriverManager.registerDriver( new oracle.jdbc.driver. OracleDriver() ); Connection conn = DriverManager.getConnection( "jdbc:oracle:oci8:@", "scott", "tiger" ); /* setup the SQL string */ String sqlStr = "SELECT Last_Name || ', ' || First_Name Employee_Name, Dept_Name" + " FROM Employee, Dept" + " WHERE Employee.Dept_No = Dept.Dept_No" + " ORDER BY Employee_ID"; /* initiate an OracleXMLQuery with our SQL string */ OracleXMLQuery qry = new OracleXMLQuery( conn, sqlStr ); /* get the generated XML string */ String xmlStr = qry.getXMLString(); /* output the XML string to the console */ System.out.println( xmlStr ); /* always remember to close the OracleXMLQuery object after use */ qry.close(); } }
Listing 4-30 first sets up a JDBC Connection to Oracle using the Oracle OCI8 JDBC driver (oracle.jdbc.driver.OracleDriver).
Listing 4-30 connects to the default database (identified by the ORA_SID environment variable) as user scott. To test this program in your environment, be certain that you make the necessary modifications to properly log in first.
Next an instance of the OracleXMLQuery class is created, passing it the JDBC Connection object and the SQL query string. Then xsu_select.java then calls the getXMLString( ) method of OracleXMLQuery to generate and return the XML document as a java.lang.String. This XML document is then displayed on the console. Finally Listing 4-30 releases the memory occupied by the instance of OracleXMLQuery.
To compile, first make sure you have the necessary CLASSPATH specified. Execute the command javac xsu_select.java. Once it is successfully compiled, execute XSU_select by entering java xsu_select.
Listing 4-31 is an example of the output that will be generated.
Listing 4-31 xsu_select.xml: XML generated by Listing 4-30.
<?xml version="1.0"?> <ROWSET> <ROW num="1"> <EMPLOYEE_ID>1</EMPLOYEE_ID> <EMPLOYEE_NAME>Chai, Sean</EMPLOYEE_NAME> <DEPT_NAME>IT</DEPT_NAME> </ROW> <ROW num="2"> <EMPLOYEE_ID>2</EMPLOYEE_ID> <EMPLOYEE_NAME>Con, Aaron</EMPLOYEE_NAME> <DEPT_NAME>IT</DEPT_NAME> </ROW> <ROW num="3"> <EMPLOYEE_ID>3</EMPLOYEE_ID> <EMPLOYEE_NAME>Dixon, Andrew</EMPLOYEE_NAME> <DEPT_NAME>Marketing</DEPT_NAME> </ROW> <ROW num="4"> <EMPLOYEE_ID>4</EMPLOYEE_ID> <EMPLOYEE_NAME>Bremer, Ted</EMPLOYEE_NAME> <DEPT_NAME>HR</DEPT_NAME> </ROW> <ROW num="5"> <EMPLOYEE_ID>5</EMPLOYEE_ID> <EMPLOYEE_NAME>Boseman, Randall</EMPLOYEE_NAME> <DEPT_NAME>Accounting</DEPT_NAME> </ROW> <ROW num="6"> <EMPLOYEE_ID>6</EMPLOYEE_ID> <EMPLOYEE_NAME>Clayton, Jane</EMPLOYEE_NAME> <DEPT_NAME>IT</DEPT_NAME> </ROW> <ROW num="7"> <EMPLOYEE_ID>7</EMPLOYEE_ID> <EMPLOYEE_NAME>Connelly, Peter</EMPLOYEE_NAME> <DEPT_NAME>Marketing</DEPT_NAME> </ROW> <ROW num="8"> <EMPLOYEE_ID>8</EMPLOYEE_ID> <EMPLOYEE_NAME>Corets, Eva</EMPLOYEE_NAME> <DEPT_NAME>HR</DEPT_NAME> </ROW> <ROW num="9"> <EMPLOYEE_ID>9</EMPLOYEE_ID> <EMPLOYEE_NAME>Sun, Nate</EMPLOYEE_NAME> <DEPT_NAME>Operations</DEPT_NAME> </ROW> </ROWSET>
In addition to transforming a resultset into XML, XSU can also be used to insert XML data into tables, update existing tables with the data extracted from XML documents, and delete rows from tables based on element values stored in the XML documents. These are all provided by methods found in the oracle.xml.sql.dml.OracleXMLSave class. Using OracleXMLSave generally involves following these basic steps:
Listing 4-32 is an example Java program that illustrates the insertion process.
Listing 4-32 xsu_insert.java: Inserting XML data into a table.
import java.sql.*; import oracle.jdbc.driver.*; import oracle.xml.sql.dml.*; public class XSU_Insert { public static void main( String argv[] ) throws Exception { /* connect to Oracle */ DriverManager.registerDriver( new oracle.jdbc.driver. OracleDriver() ); Connection conn = DriverManager.getConnection( "jdbc: oracle:oci8:@", "scott", "tiger" ); /* instantiate the OracleXMLSave object and specify that we will be inserting into the Employee table */ OracleXMLSave sav = new OracleXMLSave( conn, "Employee" ); /* setup the XML string */ String xmlStr = "<?xml version=\"1.0\"?>" + "<ROWSET>" + " <ROW num=\"1\">" + " <EMPLOYEE_NO>10</EMPLOYEE_NO>" + " <FIRST_NAME>Joshua</FIRST_NAME>" + " <LAST_NAME>Lehman</LAST_NAME>" + " <DEPT_NO>5</DEPT_NO>" + " </ROW>" + "</ROWSET>"; /* insert the row */ sav.insertXML( xmlStr ); /* always remember to close the OracleXMLSave object after use */ sav.close(); } }
Like the last example, Listing 4-32 sets up a JDBC Connection to Oracle using the Oracle OCI8 JDBC driver. Doing so creates an instance of the OracleXMLSave class, passing the JDBC Connection object and also the name of the table (in this case Employee). Listing 4-32 then sets up the string used to hold the XML document containing the specification for the row to be inserted into the Employee table.
Listing 4-32 then calls the insertXML( ) method of the OracleXMLSave( ) class to insert the row represented by the XML document specified in xmlStr into the Employee table. Finally Listing 4-32 releases the memory occupied by the instance of OracleXMLSave.
Compiling and running this example has the same requirements as the previous example.
Once it has finished executing you might want to verify for yourself that the row has been inserted into the table. To do this fire up SQL*PLUS and execute the following query:
SQL>SELECT * FROM Employee ORDER BY Employee_ID;
Here is the response you can expect back if Listing 4-32 executed correctly:
EMPLOYEE_NO FIRST_NAME LAST_NAME DEPT_NO ----------- ---------- --------- ------- 1 Sean Chai 1 2 Aaron Con 1 3 Andrew Dixon 2 4 Ted Bremer 3 5 Randall Boseman 4 6 Jane Clayton 1 7 Peter Connelly 2 8 Eva Corets 3 9 Nate Sun 5
Oracle 9i features a new system-defined object type, XMLType, for storing XML documents. The method, exposed by XMLType, provides mechanisms for creating, extracting, and indexing XML data. XMLType can be used as columns in tables and views. It can also be used in PL/SQL and Java code as parameters, return values, and variables. XMLType has a number of uses, including the four we will discuss in the following sections.
As mentioned previously, XMLType can be used as table/view columns. The following example creates a product information table that, among other normal columns, has an XMLType column used to store a product description.
CREATE TABLE Product_Info ( Product_Number INTEGER PRIMARY KEY, Product_Name VARCHAR2( 20 ) NOT NULL, Comments SYS.XMLType NOT NULL )
Notice that, just like a normal column, you can use the NOT NULL constraint on an XMLType column. However, you cannot currently use default values and check constraints with XMLType columns.
You can use an INSERT statement to insert an XML document into an XMLType column. If your XML data is stored as a string or CLOB, you first need to convert it to the XMLType before you can insert into an XMLType column. In both cases you use the built-in createXML( ) method that creates and returns an instance of XMLType. You can pass createXML( ) in its argument the XML document as either a string or CLOB argument.
At the time of writing, Oracle only supports insertion of well-formed and complete XML documents. You cannot store fragments or other non-well-formed XML into XMLType columns.
The following example inserts two rows into the Product_Info table using SQL*Plus:
SQL>INSERT INTO Product_Info VALUES ( 10, `VideoMan LPV-240', SYS.XMLType.createXML( `<?xml
version="1.0"?><Comments><Comment user=\"tjane\">Nice value but not
enough features</Comment><Comment user=\"rjlehman\">User interface too
complicated</Comment></Comments>' ) ); 1 row created. SQL>INSERT INTO Product_Info VALUES ( 12, `Ultra DVD210', SYS.XMLType.createXML( `<?xml
version="1.0"?><Comments><Comment user=\"rhackman\">Positively
Amazing! Best output quality I have seen!</Comment><Comment
user=\"qiwong\">Great video quality. Bad VCD support</Comment>
</Comments>' ) ); 1 row created. SQL>
Here the two XML documents, represented as strings, are first converted to an XMLType by using the createXML( ) method before they are inserted into the Product_Info table. To replace the XML stored in an XMLType column with another, you can simply use the UPDATE statement. As in the case of insertion, unless your new XML is an XMLType, you will have to use the createXML( ) method to convert your XML into an XMLType first.
Here is a simple example of using the UPDATE statement to update one of the rows inserted in the preceding INSERT example:
UPDATE Product_Info SET Comments = SYS.XMLType.createXML( `<?xml version="1.0"?>
<Comments><Comment user=\"tjane\">Nice value but not enough features
</Comment><Comment user=\"rjlehman\">User interface too complicated
</Comments><Comment user=\"mgoodman\"> For an off brand this one seems
to be one of the better ones</Comment></Comments>' ) WHERE Product_No = 10
XMLType can also be used in SELECT statements. In PL/SQL or Java you can use SELECT to transfer XMLType column values to and from XMLType-typed variables. You can also use the built-in getClobValue( ), getStringVal( ), or getNumberVal( ) methods to get the value of an XMLType as a CLOB, varchar, or a number respectively.
For instance, the following SELECT statement displays the XML document stored in the Comments field of Product_Info table:
SQL>SELECT Product_No, Product_Name, Comments.getStringVal() AS Users_Comments FROM Product_Info; PRODUCT_NO PRODUCT_NAME USERS_COMMENTS 10 VideoMan LPV-240 <?xml version="1.0"?><Comments><Comment
user=\"tjane\">Nice value but not enough features</Comment><Comment
user=\"rjlehman\">User interface too complicated</Comments><Comment
user=\"mgoodman\"> For an off brand this one seems to be one of
the better ones</Comment></Comments> 20 Ultra DVD210 <?xml version="1.0"?><Comments><Comment
user=\"rhackman\">Positively Amazing! Best output quality I have
seen!</Comment><Comment user=\"qiwong\">Great video quality. Bad VCD
support</Comment></Comments> SQL>
One of the most powerful features of XMLType is the built-in support for advanced XPath XML querying capabilities. XPath support is provided through the ExistsNode( ) and Extract( ) built-in methods. The ExistsNode( ) method is used to check if an XPath expression passed to it as an argument evaluates to at least a single XML element or text node. If the check is true, ExistsNode( ) returns 1. Otherwise, it returns 0. You can use this method anywhere functions can be used.
In the following example we use ExistsNode( ) in the WHERE clause of a SELECT statement to look for rows in the Product_Info table that match a specific XPath expression.
SQL>SELECT Product_No, Product_Name, Comments.getStringVal() AS Users_Comments FROM Product_Info WHERE Comments.ExistsNode( `/Comments/Comment[@user= mgoodman]' ) = 1 PRODUCT_NO PRODUCT_NAME USERS_COMMENTS 10 VideoMan LPV-240 <?xml version="1.0"?><Comments><Comment
user=\"tjane\">Nice value but not enough features</Comment><Comment
user=\"rjlehman\">User interface too complicated</Comments><Comment
user=\"mgoodman\"> For an off brand this one seems to be one of the
better ones</Comment></Comments> SQL>
The second method supported by XMLType for XPath querying, Extract( ), is used to extract nodes from the XML stored in an XMLType. The result returned from applying an XPath is another XMLType. However, this XMLType does not need to be well-formed; it can consist of a set of nodes or simple scalar types. The following example extends the SELECT statement in the previous example to extract contents from the node that matches the XPath expression.
SQL>SELECT Product_No, Product_Name, Comments.Extract( `/Comments/ Comment[@user=mgoodman]' ).getStringVal() AS USER_COMMENT FROM Product_Info WHERE Comments.ExistsNode( `/Comments/Comment[@user= mgoodman]' ) = 1 PRODUCT_NO PRODUCT_NAME USERS_COMMENTS 10 VideoMan LPV-240 For an off brand this one seems to be one of the better ones SQL> Note: We had to convert the result into a string value by using the getStringVal() method so that it can be displayed in SQL*Plus.