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. ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access