Introduction

Last week, we were talking about how to refer to parts of an XML document using XPath, and on different techniques to map semi-structured XML data into a traditional RDMBS.
So far we said, how to handle XML data directly if it comes in a tabular format. Such formats occur in the wild as the format of popular spreadsheets. For example, the Excel file format .xlsx is essentially a compressed folder with such tabular XML data.
We now look at how to map more complicated XML data to a data base, first without the assumption that we know the DTD or XML Schema, and then with the assumption that we do.

Mappings Between XML Documents and Relational Data - Schema Oblivious Mapping

This approach can be used if we need to store XML in an RDBMS, but don't have access to DTD or XML Schema for the data.

The idea is that we use tables to model nodes in the XML document:

CREATE TABLE NODE(
ID CHAR(6) NOT NULL PRIMARY KEY,
PARENT_ID CHAR(6),
TYPE VARCHAR(9),
LABEL VARCHAR(20),
VALUE CLOB,
FOREIGN KEY (PARENT_ID) REFERENCES NODE (ID) 
CONSTRAINT CC1 CHECK(TYPE IN ("element", "attribute")) )

Here each NODE has
- A unique ID for its position in the whole XML document
- A PARENT_ID to indicate it is a child element of or attribute of what node.
- A TYPE to indicate if it is an element (i.e., tag) or an attribute
- A LABEL - the name of the tag or attribute in the XML document
- A VALUE - the text contents of tag or the value of an attribute

Schema Oblivious Mapping Example

Using the NODE table, the XML document:

<?xml version="1.0" encoding="UTF-8"?> <winecellar>
<wine winekey="1">
<name>Jacques Selosse Brut Initial</name> 
<year>2012</year> 
<type>Champagne</type> <price>150</price>
</wine>
<wine winekey="2">
<name>Meneghetti White</name> <year>2010</year>
<type>white wine</type> <price>18</price>
</wine> </winecellar>

would be mapped to the table rows:
Example XML to node mapping

The XPath query:

doc("winecellar.xml")/winecellar/wine[price > 20]/name

would map to the SQL:

SELECT N2.VALUE
FROM NODE N1, NODE N2 WHERE
N2.LABEL="name" AND N1.LABEL="price" 
AND CAST(N1.VALUE AS INT) > 20 AND 
N1.PARENT_ID=N2.PARENT_ID

As we can see, this schema unaware mapping cause yield queries that involve joins for even simple XPath queries and so can be resource intensive.

Mappings Between XML Documents and Relational Data - Schema Aware Mapping

A schema-aware mapping transforms an XML document making use of DTD or XML Schema.
The following steps can then be used to generate a database schema from a DTD or XSD:
1. Simplify the DTD or XSD as much as possible.
2. Map every complex element type (consisting of other element types or mixed content) to a relational table, or user-defined type in the case of an ORDBMS, with corresponding primary key.
3. Map every element type with mixed content to a separate table where the (P)CDATA is stored. Connect this table to the parent table using a primary-foreign key relationship.
4. Map single-valued attribute types, or child elements that occur only once, with (P)CDATA content to a column in the corresponding relational table. When starting from an XSD, choose the SQL data type that most closely resembles the XML Schema data type (e.g., xs:short to SMALLINT). Null values are allowed if the attribute type or child elements are optional.
5. Map multi-valued attribute types, or child elements that can occur multiple times, with (P)CDATA content to a separate table. Use a primary-foreign key relationship to connect this table to the parent table. In an object-relational DBMS, collection types can be used as an alternative.
6. For each complex child element type, connect the tables corresponding to the child and parent element types using a primary-foreign key relationship.

In-Class Exercise

Apply the procedure of the previous slide to the Planes For Sale DTD of a couple weeks ago.
Show how the resulting tables would be populated after inserting the Example Plane Document from that Lecture.
Post your solution to the Sep 9 In-Class Exercise Thread.

SQL/XML

Different RDBMS database vendors support XML in different ways.
SQLite, perhaps the mmost ubiquitous database, does not support XML natively, so you would have to use one of the mapping approaches we hhhave just discusssed.

MySQL and MariaDB typically store XML in TEXT or LONGTEXT types and then use two functions ExtractXML(xml,xpath) and UpdateValue(xml, xpath, replacement) to manipulate the XML. For example, consider the following modifications of the MySQL docs

SELECT ExtractValue('<a><b>X</b><b>Y</b><b>Z</b></a>','//b[2]');

would return Y and

SELECT 
  -> UpdateXML('<a><b>CAT</b><d></d></a>', '/a', '<e>FOX</e>') AS val1, 
  -> UpdateXML('<a><b>CAT</b><d></d></a>', '/b', '<e>FOX</e>') AS val2, 
  -> UpdateXML('<a><b>CAT</b><d></d></a>', '//b', '<e>FOX</e>') AS val3,
  -> UpdateXML('<a><b>CAT</b><d></d></a>', '/a/d', '<e>FOX</e>') AS val4 
  , 
  -> UpdateXML('<a><d></d><b>CAT</b><d></d></a>', '/a/d', '<e>FOX</e>') 
  AS val5 
  ->       \G 
  *************************** 1. row *************************** 
  val1: <e>FOX</e> 
  val2: <a><b>CAT</b><d></d></a> 
  val3: <a><e>FOX</e><d></d></a> 
  val4: <a><b>CAT</b><e>FOX</e></a> 
  val5: <a><e>FOX</e><b>CAT</b><e>FOX</e></a>

Oracle has its own XMLType and XMLIndex for XML columns and indexes. It is uses XQuery for querying XML data.
Postgres, MSSQL, and DB2 support the SQL/XML types and functions we will now describe.
SQL/XML is part 14 of the SQL spec, initially, created in 2003, and last updated in 2011. (The most recent SQL spec is SQL 2016.)
It adds a new XML column type, it provides a set of operators for the XML data type, and it provides functions to map relational data to XML.

SQL/XML DML commands

Let's look at the data manipulation language for SQL/XML

We can create a table with an XML column using the syntax like the below:

CREATE TABLE PRODUCT(
PRODNR CHAR(6) NOT NULL PRIMARY KEY, 
PRODNAME VARCHAR(60) NOT NULL, 
PRODTYPE VARCHAR(15),
AVAILABLE_QUANTITY INTEGER,
REVIEW XML);

Here we are imagining reviews for a product come in some kind of XML format, but we are not specifying a DTD or Schema.

We can insert rows in this new table with a traditional INSERT STATEMENT:

INSERT INTO PRODUCT VALUES('000120', 'Conundrum', 'white', 12,
XMLPARSE(CONTENT '<review><author>Bart Baesens</author>
<date>27/02/2017</date>
<description>This is an excellent white wine with intriguing aromas 
of green apple, tangerine and honeysuckle blossoms.
</description><rating max-value="100">94</rating>
</review>'));

Notice we didn't give a doctype or schema in our review that we parsed to XML type with XMLPARSE. That is why we used the keyword CONTENT. If we wanted a DTD or schema we could use the keyword DOCUMENT.

SQL/XML Querying

SQL/XML support various functions for outputting XML from database tables.
XMElement has the syntax: xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...]) and can be used to output a tag:
```
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'content');

outputs

<foo bar="2020-09-09">content</foo>
```
XMLForest outputs a forest from a sequence of provided content. It has the syntax: xmlforest(content [AS name] [, ...]). For example,
```
SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 <foo>abc</foo><bar>123</bar>
```

XMLAgg aggregates return XML values into one document. For example,

CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
        xmlagg
----------------------
 <foo>abc</foo><bar/>

This can also work with an ORDER BY clause:

SELECT xmlagg(x ORDER BY y DESC) FROM test;
        xmlagg
----------------------
 <bar/><foo>abc</foo>

Other functions include: XMLPI (for making pi node), XMLComment (for outputting comments), XMLConcat (concatenating XML documents), XMLRoot (for changing root nodes of a document).

SQL/XML Querying with XPath

XPath can be incorporated into XML queries in a variety of ways:
- XMLExists is a predicate which returns true or false depending on whether the particular XML document has nodes satiafying an XQuery (only XPath subset of XQuery works for Postgres) expression:
```
SELECT xmlexists('//town[text() = ''Toronto'']' 
PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');
```
  would return t (for true).
- You can also use the XPATH function to get path a portion of a document:
```
SELECT xpath('//author', REVIEW) FROM PRODUCT;
```

Finish XML Mappings, SQL/XML, Searching XML data

Outline