SQL/XML

Was sagt die Norm?

History• SQL-86

the first version of the Structured Query Language Standard:- ANSI 1986- ISO 1987

provides:- the notion of named tables, rows, columns- DDL constructs to create tables- DML constructs to insert, update and delete rows from tables- query expressions to retrieve selected rows- set of built-in types- privileges to control access to tables- facilities to specify declarative integrity constraints

• SQL-89 enhances SQL-86 with the notion of referential integrity

• SQL-92 enhancements:

- additional data types- outer joins- catalogues- domains- assertions- temporary tables- referential actions- schema manipulation language- dynamic SQL- information schema tables- greater orthogonality

incremental parts 1995 - SQL/CLI: functions as a callable interface to an SQL

database system, providing a highly dynamic capability 1996 – SQL/PSM:

- stored procedures: ability to execute block-structured compound statements, flow-of-control statements, local variables, condition handlers

- SQL-invoked routines: functions – allowed to be overloaded, procedures

• SQL:1999 a 5-part standard consisting of:

1. SQL/Framework2. SQL/Foundation3. SQL/CLI (Call Level Interface)4. SQL/PSM (Persistent Stored Modules)5. SQL/Bindings

enhancements:- object-relational facilities- additional predefined data types: BOOLEAN, CLOB, BLOB- type constructors: ROW, REF, ARRAY- the ability to create user-defined distinct and structured data types- 3 new predicates: SIMILAR, DISTINCT, type predicate- ability to formulate recursive queries- the notion of savepoints, roles- triggers

incremental parts 9. SQL/MED (Management of External Data): extensions to the SQL language

to query and manipulate external data, to join it with local SQL-data 10. SQL/OLB (Object Language Bindings) 13. SQL/JRT (Routines and Types Using the Java Programming Language):

in combination with SQL/OLB, establishes a tight union between the Java application programming language and SQL

Addendum: complex statistical and data analysis functions executed as part of ordinary SQL-statements

• SQL:2003new functionality:

- the MERGE statement

- identity, respectively generated columns

- sequence generators

- OLAP

- CREATE TABLE … AS query

- tablesample

- multiple assignment

- MULTISET types

- the BIGINT type

- improved savepoint handling

- enhanced diagnostics

new part: 14. SQL/XML (XML-Related Specifications)

Introduction• Motivation

only XML-data => XML-query language (XQuery) only object-relational data => usual SQL integrated use of XML- and traditional SQL-data, accessed through SQL =>

SQL/XML

• SQL/XML allows applications

to store XML-documents within SQL-databases- new data type: XML

to produce XML-documents from traditional SQL-data- mapping from SQL-tables to XML-documents- to generate XML from SQL-data

under discussion to produce SQL-data from XML-documents to update XML-documents to search for texts in XML-documents

operations parse operation: XML(string) -> SQL(XML_value) serialize operation: SQL(XML_value) -> XML(string)

The XML Data Type• Values:

NULL XML-document individual XML-elements sequences of XML-elements

• Use: to define

columns variables parameters

• Comparability: XML-values are not comparable the user must explicitly define order, if XML-values are to be

compared

• Examples (XML-values):<!—-XML-element with textual content--><Salary>4000</Salary>

<!—-XML-element with sub element--><Salary><Euro>4000</Euro></Salary>

<!—-XML-element with mixed content--><JobApplication>Dieter Grau<Salary>4000</Salary></JobApplication>

<!—-XML-element with attribute--><JobApplication Date=”19.06.1997”>Dieter Grau</JobApplication>

<!—-XML-element forest--><Salary>4000</Salary><LastName>Black</LastName>

<!—-XML-document--><?xml version=’1.0?’><JobApplication>Kim Black</JobApplication>

• Example (use of XML data type):CREATE TABLE Employees( ID Integer,

Salary Decimal(12,2), JobApplication XML

);

ID Salary JobApplication

501148 7000 <JobApplication Date=“27.07.1999”>

<FirstName>Gisela</FirstName>

<LastName>Blum</LastName>

<Address>Alexanderstrasse 39</Address>

<Skills>

<Skill>Datenbanken</Skill>

<Skill>SQL</Skill>

<Skill>Oracle</Skill>

<Skill>Postgres</Skill>

<Skill>DB2</Skill>

</Skills>

</JobApplication>

220443 4000 <JobApplication Date=“03.05.1994”>

<FirstName>Dieter</FirstName>

<LastName>Grau</LastName>

<Address>Editharing 31</Address>

<Skills>

<Skill>Datenbanken</Skill>

<Skill>Informix</Skill>

<Skill>DB2</Skill>

</Skills>

</JobApplication>

XML-Functions• XMLELEMENT

produces an XML-element syntax:

XMLELEMENT (Name <ElementName> [, XMLATTRIBUTES( <XML-

AttributeList> )] [, < ValueExpressionList>])

<XML-Attribute>:=<ValueExpression>[AS <AttributeName>]

where the ValueExpressionList defines the content of the XML-element.

• XMLATTRIBUTES used to create XML-attributes example:SELECT e.id ID, XMLELEMENT(Name “Emp”,

XMLATTRIBUTES(e.id), e.lname) AS Employee

FROM employees e;

ID Employee

1001 <Emp ID = “1001”>Smith</Emp>

1206 <Emp ID = “1206”>Martin</Emp>

• XMLAGG produces a single XML-value from a group of XML-values syntax:

XMLAGG(< XML-ValueExpression >

[ORDER BY <SortExpressionList> ])

example:

SELECT XMLELEMENT(Name “Dept”,

XMLATTRIBUTES(e.dept AS name”),

XMLAGG(XMLELEMENT(NAME “emp”, e.lname)

ORDER BY e.lname)

) AS Dept_List

FROM employees e;

Dept_List<Dept name=“Accounting”>

<emp>Smith</emp>

<emp>Yates</emp>

</Dept>

<Dept name= “Shipping”>

<emp>Martin</emp>

<emp>Dylan</emp>

</Dept>

• XMLCONCAT concatenates two or more XML-values syntax:

XMLCONCAT(< XML-ValueExpressionList >) example:

SELECT XMLCONCAT(

XMLELEMENT(NAME “Name”, Name),

XMLELEMENT(NAME “Euro”, 12*Salary)

) AS Emp_Salary

FROM employees;Emp_Salary<Name>Smith</Name>

<Euro>24000</Euro>

<Name>Moore</Name>

<Euro>32000</Euro>

• XMLFOREST creates a sequence of XML-elements syntax:

XMLFOREST(< XML-ElementList >)<XML-Element>>:=<ValueExpression>

[AS <ElementName>] example:

SELECT XMLFOREST(ID,XMLELEMENT(NAME “Euro”,

12*Salary)AS Salary) AS Emp_Salary

FROM employees;

Emp_Salary<ID>501148</ID><Salary><Euro>36000</Euro></Salary>

<ID>220443</ID><Salary><Euro>48000</Euro></Salary>

<ID>170470</ID><Salary><Euro>84000</Euro></Salary>

<ID>160139</ID><Salary><Euro>48000</Euro></Salary>

Mapping between SQL and XML

• The SQL/XML Norm specifies mapping between the following concepts: SQL-value → XML-value SQL-character set ↔ XML-Unicode SQL-identifier ↔ XML-name SQL-data type ↔ XML-schema name SQL-table ↔ XML-document + XML-schema document SQL-schemes ↔ XML-document + XML-schema document SQL-catalog ↔ XML-document + XML-schema document

• SQL/XML makes available an XML-Namespace that serves as a base for mapping between SQL and XML. This defines in XML the SQL-data type forms (predefined data type, domain, row type, array

type, multiset type and distinct type) the predefined SQL-data types the SQL-schema objects (catalogue, schema, base table, view, character set,

collation)

Mapping from SQL-Values to XML-Values

• set by mapping of corresponding data types

• exceptions: numerical data types: decimal point missing when no values after

following it

strings: special symbols mapped to their counterpart in XML

• examples:

SQL-data type SQL-value XML-value

NUMERIC(12,2) 1003.44 1003.44

NUMERIC(12,2) 1003.0 1003

CHAR(20) ‘SQL:2003’ SQL:2003

VARCHAR(20) ‘< SQL:2003>’ &lt;SQL:2003&gt;

Mapping from SQL-Identifiers to XML-Names

• Regular identifiers: 1:1 mapping capital letters, as internally SQL-identifiers are displayed in upper case letters example:

Employee → <EMPLOYEE></EMPLOYEE>

• Identifiers containing special symbols: special symbols are not allowed in qualified XML-names mapping: _xUnicodeValue_ mapping is reversible examples:

“SQL:2003*” → SQL_x003A_2003_x003E_“©Türker” → _x00A9_T_xx00FC_rker“Vize@Weltmeister” → Vize_x0040_Weltmeister“<WM_Dritter>” → _x003C_WM_Dritter_x002A_Überführungsstrasse → _x00DC_berf_x00FC_hrungstrasse

Mapping from SQL - Data Types to XML-Schema Names

• Mapping of predefined data types:SQL-data type XML-schema typeBOOLEAN boolean

CHAR, VARCHAR, CLOB string

BIT, VARYING BIT, BLOB base64Binary, hexBinary

SMALLINT, INT, BIGINT integer

NUMERIC, DECIMAL decimal

FLOAT, REAL, DOUBLE PRECISION float, double

DATE date

TIME time

TIMESTAMP dateTime

INTERVAL duration

XML-schema type must be restricted, when its range exceeds its correspondent SQL-data type’s range. Example:

VARCHAR(20) → maxLength(20)

Examples:

CHAR(20)<xsd:simpleType name=“CHAR_20”><xsd:annotation> <xsd:appinfo>

<!—-description of the SQL-data type –><!–- optional -><sqlxml:sqltype kind=“PREDEFINED”

name=“CHAR” length=“20” characterSetName=“LATIN1” collation=“DEUTSCH”/>

</xsd:appinfo></xsd:annotation><!—-description of the corresponding XML-schema type-><xsd:restriction base=“xsd:string”> <xsd:length value=“20”/></xsd:restriction>

</xsd:simpleType>

NUMERIC(12,2)<xsd:simpleType name=“NUMERIC_12_2”>

<xsd:annotation>

<xsd:appinfo>

<sqlxml:sqltype kind=“PREDEFINED”

name=“NUMERIC”

userPrecision=“12”

scale=“2”/>

</xsd:appinfo>

</xsd:annotation>

<xsd:restriction base=“xsd:numeric”>

<xsd:length value=“12”/>

<xsd:scale value=“2”/>

</xsd:restriction>

</xsd:simpleType>

SMALLINT<xsd:simpleType name=“SMALLINT”>

<xsd:annotation>

<xsd:appinfo>

<sqlxml:sqltype kind=“PREDEFINED”

name=“SMALLINT”/>

</xsd:appinfo>

</xsd:annotation>

<xsd:restriction base=“xsd:integer”>

<xsd:minInclusive value=“-32768”/>

<xsd:maxInclusive value=“32767”/>

</xsd:restriction>

</xsd:simpleType>

• Mapping of domains mapped to XML-schema types that describe them as precisely as possible example:

CREATE DOMAIN ETH.DBS.Jobs CHAR(4)DEFAULT ‘Hiwi’CHECK (VALUE IN (‘Prof’, ‘Assi’, ‘Hiwi’))

<xsd:simpleType name=“DOMAIN.ETH.DBS.Jobs”><xsd:annotation> <xsd:appinfo>

<sqlxml:sqltype kind=“DOMAIN” catalogName=“ETH” schemaName=“DBS” typeName=“Jobs” mappedType=“CHAR_4”/>

</xsd:appinfo></xsd:annotation><xsd:restriction base=“CHAR_4”> <xsd:enumeration value=“Prof”/> <xsd:enumeration value=“Assi”/> <xsd:enumeration value=“Hiwi”/></xsd:restriction>

</xsd:simpleType>

• Mapping of the row type mapped to complex type: each SQL-attribute represents an XML-element the types of these elements is the result of mapping each attribute’s data type example:

ROW(FirstName CHAR(20), LastName CHAR(30))

<xsd:complexType name=“ROW.Name”><xsd:annotation> <xsd:appinfo>

<sqlxml:sqltype kind=“ROW”> <sqlxml:field name=“FirstName”

mappedType=“CHAR_20”/>

<sqlxml:field name=“LastName” mappedType=“CHAR_30”/>

</sqlxml:sqltype> </xsd:appinfo></xsd:annotation><xsd:sequence> <xsd:element name=“FirstName” type=“CHAR_20”/> <xsd:element name=“LastName” type=“CHAR_30”/></xsd:sequence>

</xsd:complexType>

• Mapping of collection types mapped to complex type made up of a sequence with a single element the type of this element is the result of mapping the appropriate SQL-data type facets:

minOccurs=0 maxOccurs:

array type: restricted by the maximal cardinality of the type multiset: unrestricted

examples: DECIMAL(12,2) ARRAY[10]

<xsd:complexType name=“ARRAY.DECIMAL_12_2”><xsd:annotation> <xsd:appinfo>

<sqlxml:sqltype kind=“ARRAY” maxElements=“10”

mappedElementType=“NUMERIC_12_2”/> </xsd:appinfo></xsd:annotation><xsd:sequence> <xsd:element name=“Element” type=“NUMERIC_12_2”

minOccurs=“0” maxOccurs=“10”/></xsd:sequence>

</xsd:complexType>

CHAR(20) MULTISET<xsd:complexType name=“MULTISET.CHAR_20”><xsd:annotation> <xsd:appinfo>

<sqlxml:sqltype kind=“MULTISET”

mappedElementType=“CHAR_20”/> </xsd:appinfo></xsd:annotation><xsd:sequence> <xsd:element name=“Element”

type=“CHAR_20” minOccurs=“0” maxOccurs=“unbounded”/>

</xsd:sequence></xsd:complexType>

• Mapping of distinct types directly identifies the appropriate SQL-source type example:

CREATE TYPE ETH.DBS.Franken AS NUMERIC(12,2) FINAL

<xsd:simpleType name=“UDT.ETH.DBS.Franken”>

<xsd:annotation>

<xsd:appinfo>

<sqlxml:sqltype kind=“DISTINCT”

catalogName=“ETH”

schemaName=“DBS”

typeName=“Franken”

mappedType=“Numeric_12_2”

final=“true”/>

</xsd:appinfo>

</xsd:annotation>

<xsd:restriction base=“Numeric_12_2”/>

</xsd:simpleType>

Mapping from SQL-tables to XML-documents

• Data and metadata are mapped separately: XML-schema document: describes the schema of the table (name and type of

columns, row type, type of the table) XML-document: the content of the table

• Example:CREATE TABLE ETH.DBS.Employee

{Name CHAR(20) NOT NULL, Salary NUMERIC(12,2)}

the XML-schema document:<xsd:complexType name=“ROW.ETH.DBS.EMPLOYEE”>

<xsd:sequence> <xsd:element name=“Name”

type=“CHAR_20”/> <xsd:element name=“Salary”

type=“NUMERIC_12_2” nillable=“true”/>

</xsd:sequence></xsd:complexType>

<xsd:complexType name=“TABLE.ETH.DBS.EMPLOYEE”>

<xsd:annotation>

<xsd:appinfo>

<sqlxml:sqlname type=“BASE TABLE”

catalogName=“ETH”

schemaName=“DBS”

localName=“EMPLOYEE”/>

</xsd:appinfo>

</xsd:annotation>

<xsd:sequence>

<xsd:element name=“row”

type=“ROW.ETH.DBS.EMPLOYEE”

minOccurs=“0”

maxOccurs=“unbounded”/>

</xsd:sequence>

</xsd:complexType>

the XML-document:

<EMPLOYEE>

<row>

<NAME>Blum</NAME><Salary>3000</Salary>

</row>

<row>

<NAME>Black</NAME><Salary>7000</Salary>

</row>

</EMPLOYEE>

Mapping from SQL-Schema to XML-documents

• Data and metadata are mapped separately: XML-schema document: definition of the schema and schema objects XML-document: the content of the tables contained by the schema

• Example:CREATE SCHEMA ETH.DBS

CREATE TABLE EMPLOYEE{Name CHAR(20) NOT NULL, Salary NUMERIC(12,2)}

<xsd:complexType name=“SCHEMA.ETH.DBS”><xsd:annotation>

<xsd:appinfo><sqlxml:sqlname type=“SCHEMA”

catalogName=“ETH” schemaName=“DBS”/>

</xsd:appinfo></xsd:annotation><xsd:all> <xsd:element name=“EMPLOYEE”

type=“TABLE.ETH.DBS.EMPLOYEE”/> <!—-enumeration of the other tables->

</xsd:all></xsd:complexType>

Mapping from SQL-Catalogues to XML-documents

• Data and metadata are mapped separately: XML-schema document: the definitions of the schemes contained by the

catalogue XML-document: the content of the tables contained by these schemes

• Example:<xsd:complexType name=“CATALOG.ETH”>

<xsd:annotation><xsd:appinfo><sqlxml:sqlname type=“CATALOG”

catalogName=“ETH”/></xsd:appinfo>

</xsd:annotation><xsd:all> <xsd:element name=“DBS”

type=“SCHEMA.ETH.DBS”/> <!—-enumeration of the other schemes->

</xsd:all></xsd:complexType>