XQuery

Combining SQL and XQuery

Most major relational database vendors now allow you to store XML in your databases. The examples in this section use Microsoft SQL Server 2005 syntax, but there is similar functionality available in Oracle and IBM DB2.

Combining Structured and Semistructured Data

One use case is to combine narrative text with more highly structured data. An example is when each of the products has a textual description that can span multiple paragraphs and can be marked up with HTML-like tags to indicate sections of text that need to be in bold or italic. This is shown in Table 24-7.

Table 24-7. The prod_desc table

number	desc
557	<p>This pullover is made from recycled polyester.</p>
563	<p>Enjoy the sun in this <i>gorgeous</i> hat!</p>
443	<p>You'll <b>never</b> be disorganized with this bag.</p>
784	<p>Our <i>favorite</i> shirt!</p> <p>Can be monogrammed upon request.</p>

When you create the table, you declare the desc column to be of type XML, as in:

CREATE TABLE prod_desc (
  number INTEGER NOT NULL,
  desc XML
  );

If desired, you can specify the location of a schema for the desc column, which will ensure that any values inserted into desc conform to that schema. It will also provide all the other benefits of using a schema, such as query debugging.

Flexible Data Structures

Another use case for storing XML in a relational table is to take advantage of the flexibility of XML. Suppose each product has a set of custom properties that needs to change flexibly over time. ...