XML Support in Commercial Relational Database Management Systems

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.

Microsoft SQL Server 2000

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.

Using SELECT FOR XML to Expose Relational Data as 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

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.

The AUTO Mode

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

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.

  • Tag must be specified as the first column in the Universal Table and is used to indicate the tag number of the current element. Tag is an integer and is numbered starting at 1.
  • The second column in the Universal Table must be called Parent. It's used to specify the tag number of the parent element. Parent is also an integer. To specify that an element has no parent (that is, it's at the top level of the tree), use a value of 0 or NULL.

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] 
  • ElementName represents the element name that the column belongs to.
  • TagNumber specifies the tag number of the element.
  • AttributeName specifies the attribute or element name within the XML.
  • Directive is optional and is used to specify how a column should be rendered in the XML. For example, a column is generated as an attribute in the XML by default. By specifying the "element" Directive, the column will be rendered as a contained element instead. Directive supports many other options, and we will see some of them later in this section.

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.

XML Templates

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.

XML View

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> 

Persisting XML Data Using OPENXML

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 
  • Handle is the handle value returned by the sp_xml_prepareDocument stored procedure.
  • XPath is an XPath expression used to identify the nodes in the XML document that should be included in the resultset. Each node identified by the XPath expression corresponds to a single row in the resultset generated by OPENXML.
  • Attributes are optional and are used to specify the type of mapping (attribute-centric or element-centric) between the resultset columns and the XML nodes identified by the XPath expression. Attributes are integer values and the default value of 0 uses an attribute-centric mapping. A value of 2 identifies element-centric mapping.
  • SchemaDeclaration identifies the database schema declaration that OPENXML uses to generate the resultset. SchemaDeclaration uses a syntax similar to that used by the CREATE TABLE statement, namely ColumnName1 ColumnType1, ColumName2, ColumnType2, and so forth. Alternatively, you can specify the name of a table for SchemaDeclarations if this table already exists with the desired schema.

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.

Oracle 9i

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 ) / 

The XML SQL Utility

XSU is supplied as part of the Oracle XDK and is designed to enable the user to perform the following three tasks:

  • Generate an XML document from any SQL queries or Java JDBC Resultset objects.
  • Extract the data from an XML document and insert the data into the appropriate columns/attributes of a table or view.
  • Extract the data from an XML document and use this data to update or delete values of the appropriate columns/attributes or both.

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.

Generating XML Documents

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

  1. Get a connection to the required Oracle database by using the Oracle JDBC Driver. You can use any Oracle-supported JDBC Driver such as the OCI8, Thin, or the server-side internal JDBC Driver.
  2. Create an instance of the OracleXMLQuery class and passing the constructor the SQL query in the form of a java.lang.String, a java.sql.ResultSet, or an oracle.xml.sql.dataset.OracleXMLDataSet.
  3. Specify optional features such as a different resultset, row name or both, defining the format for dates, or even specifying a style sheet header.
  4. Retrieve the generated XML document. You can get a string or DOM representation of this XML document by calling the getDOM ( ) or getXML( ) method, respectively.
  5. Release resources held by the OracleXMLQuery by calling its close( ) method.

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>  

Storing XML Data

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:

  • Get a connection to the required Oracle database by using the Oracle JDBC Driver. You can use any Oracle-supported JDBC Driver, such as the OCI8, Thin, or the server-side internal JDBC Driver.
  • Create an instance of the OracleXMLSave class and passing the constructor the name of the table or view on which insertions, updating, or deletions need to be done.
  • For insertion you have the option to specify a list of column names that you want the insert to work on. To do this you construct an array of java.lang.String containing the column names and then pass it to the setUpdateColumnList( ) method.
  • For updating you need to specify the list of key column names that will be used to select the rows for updating. You do this by building an array of java.lang.String containing the key column names and passing it to the setKeyColumnList( ) method. You can also specify the specific columns that you want to update instead of updating all the columns. To do this you construct an array of java.lang.String containing the column names and then pass it to the setUpdateColumnList( ) method.
  • For deletion you can specify the list of key column names that will be used to select the rows for deletion. You do this by building an array of java.lang.String containing the key column names and passing it to the setKeyColumnList( ) method.
  • Construct an XML document as a java.lang.String, java.io.InputStream, java.io.Reader, or as a DOM object.
  • For insertion invoke the insertXML( ) method and pass it the XML document constructed in the previous step.
  • For updating invoke the updateXML( ) method and pass it the XML document constructed in the previous step.
  • For deletion invoke the deleteXML( ) method and pass it the XML document constructed in the previous step.
  • Release resources held by the OracleXMLSave by calling its close( ) method.

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 

XMLType

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.

Creating XMLType Columns

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.

Inserting and Updating 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

Using XMLType Columns in SELECT Statements

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>

Using XPath to Query XML Data

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. 


XML Programming
XML Programming Bible
ISBN: 0764538292
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net