“Movies We Own” XML

“Movies We Own” Schema

Data A-2 is one possible XML Schema for the data in Data A-1.

There are a number of decisions to be made when creating an XML Schema. Some have an effect on the variations in data that can be “caught” by schema validation, others are largely a question of style. For example, in Example 1-6, we defined an element for each of givenName and familyName and referenced those elements in producer, director, writer, and cast. In Data A-2, on the other hand, we chose to create a type for person and then to define producer, director, writer, and cast to be of type person (sometimes with extensions).

We have included a liberal sprinkling of comments in the schema. There are two possible styles of comment in a schema – either you can use the schema style (add an annotation element with a documentation child) or, since the schema is an XML document, you can use XML comments (<!-- like this -->). The annotation element lends itself to easier querying (if you want to do XQueries against comments in your schema), and its contents show up in the schema design view in XMLSpy. But the XML comments are simpler and less verbose, so we chose the XML comment style. Note that comments are very necessary in a schema – the schema supplies lots of information about syntax, but the schema author still needs to document syntax and style choices, and the semantics underlying the data.

When writing and testing the schema, we were reminded just how useful a schema is – in the original movies data, there was a near-even split between “Male” and “male,” “Female” and “female,” “Story” and “story,” “Screenplay” and “screenplay.” The enumeration in the schema caught that inconsistency. If there were no schema and the inconsistency had not been caught, queries would not fail but they would silently give “wrong” answers.

Of course, there are a number of ways that the same data can be represented in a schema. We have already mentioned that we abstracted out persons, for example. Similarly, there are a number of ways to represent the same data in an XML document. As one example, in movies-we-own storyOrScreenplay is a child of writer. We could have decided to make story and screenplay children of movie, each with a writer child.

Finally, take a look at the schema definition of plotSummary. Here is an example of presentation markup – a plotSummary contains text with an arbitrary number of emph tags bracketing parts of the text.

“Movies We Own” DTD

The DTD in Data A-3 is based on the DTD produced from Data A-2 by XMLSpy. Note how much richness is lost when going from XML Schema to DTD.

“Movies We Own” SQL Tables for XML Data

When considering a storage/representation strategy for XML data, you need to consider what constitutes a document. There are two obvious strategies for the movies data – a document could be all the movies wrapped in a single <movies> tag, or it could be a set of documents, each representing a single movie. These two options are illustrated by the ALL_MOVIES_XML table (Data A-4) and the MOVIES_XML table (Data A-5) respectively. In both examples, we create a SQL table and store the data natively in a column of type XMLTYPE. Again, the syntax throughout this appendix is vendor-specific (the SQL standard calls the special XML type “XML,” for example).

“Movies We Own” SQL Tables for Relational Data

The data in Data A-1 could be stored as relational data (as well as, or instead of, XML data) in SQL tables. In this section we present one possible representation. For each table we show a CREATE TABLE statement followed by a listing of the data in that table. For the largest table (MOVIES) we have split the data into three separate listings. Note that the CREATE TABLE statements use Oracle, rather than standard SQL, syntax. For example, some columns are of type VARCHAR2 rather than CHARACTER VARYING. There is some other proprietary syntax (such as CREATE DIRECTORY and CREATE SEQUENCE) – if you want to run these samples on a database from some vendor other than Oracle, you’ll need to work around these differences.

The main table, MOVIES, has a column for each property of a movie, minus repeating properties. Data A-6 shows how to create MOVIES and populate it from the data in the MOVIES_XML table. Note that MOVIES, and the other SQL tables we present, could be created as a view instead of a table.

Of course, you could split the MOVIES table into any number of tables – for example, it might be useful to split the sound fields into their own table.

In the body of the book, we created tables for producers and directors. In this appendix, we decided to abstract out all the persons into a single table, and then to create tracking tables to link producers with movies, directors with movies, writers with movies, and cast with movies. SQL readers will be familiar with this notion of normalizing data so that common facts (such as a person’s names) are stored only once, no matter how many roles and movies he is associated with. Data A-7 through Data A-11 create and populate these tables from the data in the MOVIES_XML table.

Check on Relational Tables

We now have a couple of tables that store the movies-we-own.xml data in XML columns in relational tables, and we have a set of purely relational tables (with no XML columns) built from the XML data in the MOVIES_XML table. Now let’s turn this relational data back into XML, and check the result against the original XML file.

We assume that the order of repeating elements is not significant. The movies were inserted into the relational tables in title order, though we could have done that in document order, in which case we could have used ORDER BY id to get them back in the same order. If we’d gone with a movies_producer table instead of normalizing to persons, we could have done the same with producers (and directors and writers and cast). Note that if the order of any of these fields were important, you could add an “order” column to the table to preserve the original document order.

The output from Data A-12 does indeed match movies-we-own.xml, modulo ordering of repeating properties (see http://xqzone.marklogic.com/queryingxmlbook/ for a listing of the output). As we explained earlier, in our example the ordering is not significant, but the ordering could be captured if it were important.

“Movie Reviews” XML

In this section, we present movie-reviews.xml and its associated metadata and SQL tables. The movie reviews data are a small sample of ratings and reviews of some of the movies. They are presented here so that we can illustrate queries across more than one data source (XML file or table).

“Movie Reviews” Schema

Just for fun, we use a slightly different style for defining elements in Data A-14. Instead of defining a set of complex types, as in Data A-2, we define a set of elements up front and reference them in the definition of the reviews element. Neither style is particularly appropriate for our rather simple data set; we just used them to show some variations in schema styles.

“Movie Reviews” DTD

“Movie Reviews” XML Table

Instead of using SQL/XML to populate the REVIEWS table from reviews.xml (as we did with movies), we created an XSLT stylesheet that, when run over reviews.xml, outputs a SQL script to create and populate the REVIEWS table. (Our first pass at creating the movies tables was to write XQueries that output SQL scripts – see the examples website).

Publishing Functions – Representing SQL Data as XML

In Example A-24 and some following examples, we introduced a spurious condition to keep the results size down (to save some paper).

Example A-24 and Example A-25 give similar results, but Example A-25 uses XMLFOREST to skip null values (for otherNames).

XMLQUERY and XMLTABLE—Manipulating XML in SQL

Of course, you could use XMLTABLE to create MOVIES_XML from ALL_MOVIES_XML; see Example A-29.

Note that in Example A-31, there are 10 rows in the result, since we are iterating over all 10 rows in the table.

In Example A-39, we use a sequence to yield an ID. Any unique, increasing number would serve. Note that Example A-39 uses the SQL DISTINCT keyword to ensure that producer details are distinct (each producer is represented only once in the table, no matter how many movies he produced).

To end this section, we present two analytic functions, rollup and cube, in Example A-41 and Example A-42, respectively. These examples illustrate the power of SQL/XML, providing the ability to perform sophisticated functions on XML data.