XML Databases 6. SQL/XML

(1)

XML Databases

Silke Eckstein

Andreas Kupfer

Institut für Informationssysteme

Technische Universität Braunschweig

http://www.ifis.cs.tu-bs.de

(2)

6.1Introduction

6.2 Publishing relational data in XML

6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries

6.5 Validation

6.6 SQL/XML standard

6.7 Overview

6.8 References

(3)

•

Creating XML documents from a database

–

Introduced in the last chapter

–

On a more or less conceptual level

•

Not handled so far

6.1 Introduction

•

Not handled so far

–

Creating XML documents inside a database

–

Retrieving data from XML documents

–

Changing XML document content

•

Solution: Integration in database

–

SQL/XML

(4)

•

SQL/XML

–

Storage of XML in all big commercial DBMS available

–

Proprietary solution for embedding in SQL

–

SQL/XML = Part 14 of the SQL-Standard: XML

6.1 Introduction

SQL/XML = Part 14 of the SQL-Standard: XML

functionality

–

Incorporates the corresponding standards for XML

(XML Schema, XQuery)

–

Basic idea:

•

Mapping of SQL concepts to XML (see last chapter)

•

Own datatype to store XML

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig

4

(5)

•

SQL/XML

–

Datatype XML with

6.1 Introduction

<City> <Name> Braunschweig </Name> <Zip>38100</Zip> <Zip>38106</Zip> <State> Niedersachsen </State> </City> <City> <City>

Storing XML

documents inside

the database as

values of type XML

Generating XML

documents using

SQL/XML functions

–

Datatype XML with

belonging functions

–

Mapping between

SQL and XML

–

Embedding XQuery in SQL

5

[Tür08]

SQL database

XML datatype

SQL

XQuery

(6)

•

Mapping SQL database to XML

–

SQL charset to unicode (depends on implementation)

–

SQL identifiers to XML names

–

SQL data types to XML schema data types

6.1 Introduction

SQL data types to XML schema data types

–

SQL values to XML values

–

SQL tables to XML and XML schema documents

–

SQL schemas to XML and XML schema documents

–

SQL catalogues to XML and XML schema documents

6

(7)

Name

Balance

Joe

2000

Jim

3500

Mapping SQL tables

<xsd:complexType name="ROW.ACCOUNT">

<xsd:sequence>

<xsd:element name="NAME"

type="CHAR_20"/>

<xsd:element name="BALANCE"

type="NUMERIC_12_2"/>

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="TABLE.ACCOUNT">

<xsd:annotation><xsd:appinfo>

<xqlxml:sqlname type="BASE TABLE"

CREATE TABLE

Account

(

Name

CHAR(20),

Balance

NUMERIC(12,2),

);

Mapping SQL

table columns to

XML elements

Mapping table

rows to XML

<row>

elements

Jim

3500

7

[Tür08]

<row>

</row>

<row>

</row>

</ACCOUNT>

localName="ACCOUNT"/>

</xsd:appinfo></xsd:annotation>

<xsd:sequence>

<xsd:element name="row"

type="ROW.ACCOUNT"/>

</xsd:sequence>

</xsd:complexType>

<xsd:element name="ACCOUNT"

type="TABLE.ACCOUNT"/>

elements

(8)

•

Relational table: Cities

6.1 Introduction

City

Zip

State

Braunschweig

38100

Niedersachsen

Braunschweig

38106

Niedersachsen

Hannover

30159

Niedersachsen

•

Many possible XML documents

8

...

<City>

<Name>Braunschweig</Name>

<State>Niedersachsen</State>

</City>

...

</City>

</State>

...

(9)

6.1Introduction

6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries

6.5 Validation

6.7 Overview

6.8 References

(10)

•

XMLELEMENT

creates an XML element

–

Example: creating name and content

6.2 Publishing relational data

XMLELEMENT( NAME "City", 'Bad Oeynhausen' )

Creates

<City>Bad Oeynhausen</City>

–

Can contain attributes, comments and other elements

and options

10

[Kud07]

<City>Bad Oeynhausen</City>

XMLELEMENT( NAME "City", XMLCOMMENT ( "Example 2" ),

XMLATTRIBUTES('Bayern' AS "State",

'80469' AS "Zip" ),'München' )

Creates

München</City>

(11)

•

XMLELEMENT

referencing the database

–

Can be used directly from an SQL statement

SELECT XMLELEMENT( NAME "City",

XMLCOMMENT ( "Example 3" ),

XMLATTRIBUTES( "State", "Zip" AS "PLZ" ),

"City" )

•

Creates

11

[Kud07]

"City" )

FROM Cities WHERE …;

<City STATE="Niedersachsen" PLZ="38100">

<!– Example 3 -->

Braunschweig

</City>

(12)

•

XMLELEMENT

nesting

–

Example

SELECT XMLELEMENT( NAME "City",

XMLELEMENT( NAME "Name", "City" ),

XMLELEMENT( NAME "State", "State" ),

XMLELEMENT( NAME "Zip", "Zip" ) )

•

Creates

12

[Kud07]

XMLELEMENT( NAME "Zip", "Zip" ) )

FROM Cities WHERE …;

<City>

<Name>Braunschweig</Name>

<State>Niedersachsen</State>

(13)

•

XMLELEMENT

syntax diagram

13

(14)

•

XMLFOREST

–

Constructs a forest of elements without attributes

•

Creates

SELECT XMLFOREST ( "City", "State" )

FROM Cities;

•

Creates

14

[Kud07]

<City>Braunschweig</City><State>Niedersachsen</State>

<City>Hannover</City><State>Niedersachsen</State>

(15)

•

XMLFOREST

syntax diagram

15

(16)

•

XMLCONCAT

–

Concatenates multiple XML fragments

into a single XML pattern

–

Compare outputs

SELECT XMLELEMENT("city", City) AS "CITY",

16

[Pow07]

SELECT XMLELEMENT("city", City) AS "CITY",

XMLELEMENT("zip", Zip) AS "ZIP",

XMLELEMENT("state", State) AS "STATE"

FROM Cities;

SELECT XMLCONCAT(

XMLELEMENT("city", CITY),

XMLELEMENT("zip", ZIP),

XMLELEMENT("state", STATE) )

FROM Cities;

(17)

•

XMLAGG

–

Aggregates seperate lines of output into a single string

SELECT CITY, XMLAGG(

XMLELEMENT(NAME "Zip", Zip)) AS

"Zipcodes"

•

Creates

17

[Tür08]

City

Zipcodes

Braunschweig

Hannover

FROM Cities

GROUP BY City;

(18)

•

XMLAGG

–

Allows sorting

SELECT XMLAGG(

XMLELEMENT("address", Zip||' '||City)

ORDER BY Zip DESC)

FROM Cities;

•

Creates

–

Disadvantage: Can only aggregate a single element, and

thus fields are concatenated

18

[Pow07]

FROM Cities;

<address>38106 Braunschweig</address>

<address>38100 Braunschweig</address>

<address>30159 Hannover</address>

(19)

6.1Introduction

6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries

6.5 Validation

6.7 Overview

6.8 References

(20)

•

Storing XML in relational databases is possible as

–

Character data (VARCHAR, Character Large OBject)

–

New data type XML

•

A value of the data type XML can contain

–

whole XML document

6.3 XML data type

–

whole XML document

–

XML element

–

a set of XML elements

•

All XML publishing operators from chapter 6.2

create values of the data type XML, not a string

(21)

6.3 XML data type

XML(SEQUENCE

)

XML(CONTENT(ANY))

NULL or document node

Untyped elements &

attributes,

elements not NULL

1 element child

Validated against schema

21

[Tür08]

XML(CONTENT(XMLSCHEMA))

XML(CONTENT(UNTYPED))

XML(DOCUMENT(ANY))

XML(DOCUMENT(UNTYPED))

XML(DOCUMENT(XMLSCHEMA))

1 element child

Validated against schema

1 element child

(22)

•

Specification of XML type

•

Modifiers are optional

•

Primary type modifier

6.3 XML data type

XML [({DOCUMENT|CONTENT|SEQUENCE}

[({ANY|UNTYPED|XMLSCHEMA schema name})])]

•

Primary type modifier

–

DOCUMENT

(XML document)

–

CONTENT

(XML element)

–

SEQUENCE

(sequence of XML elements)

•

Secondary type modifier

–

UNTYPED

–

XMLSCHEMA

(typed)

–

ANY

(may be typed)

22

(23)

•

Create a table that is an XML data type in itself

–

CREATE TABLE XMLDOCUMENT OF XMLTYPE;

•

Create a table containing an XMLType data type

column

6.3 XML data type

column

23

[Pow07]

CREATE TABLE XML

(

ID

NUMBER NOT NULL,

XML

XMLTYPE,

CONSTRAINT XPK PRIMARY KEY (ID)

);

(24)

•

Example: Definition of an XML type column

6.3 XML data type

ID

Name

123

<Groups>Annabelle</Groups>

CREATE TABLE Groups (

ID

INTEGER,

Name

XML );

24

123

<Groups>Annabelle</Groups>

234

<Groups>Magdalena, Marius</Groups>

345

<?xml version 1.0?>

<Person>Patrick</Person>

<Person>Robert</Person>

</Groups>

654

<Groups>Rebecca</Groups>

<Groups>Torben</Groups>

(25)

•

Characteristics

–

Allowed values:

•

XML documents (including prolog)

•

XML content according to XML 1.0 (includes pure text

comments, PI?)

•

NULL

6.3 XML data type

•

NULL

–

No comparison

possible (compare CLOB in SQL)

•

User can define an

order

, if comparison is necessary

–

No corresponding type in programming languages for

embedding in SQL available

–

Standard defines operators to convert to other SQL

data types

25

(26)

•

Parsing & Serialization

–

XMLParse

:

•

Parses a string value using an

XML parser

•

Produces value whose specific type is

6.3 XML data type

<City> <Name> Braunschweig </Name> <Zip>38100</Zip> <Zip>38106</Zip> <State> Niedersachsen </State> </City>

•

Produces value whose specific type is

XML(DOCUMENT(ANY))

, or

…CONTENT…

, or

–

XMLSerialize

•

Transforms an XML value into a string value (

CHAR

,

VARCHAR

,

CLOB

, or

BLOB

)

26

[Mel05] <City> <Name> Braunschweig </Name> <Zip>38100</Zip> <Zip>38106</Zip> <State> Niedersachsen </State> </City>

(27)

6.1Introduction

6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries

6.5 Validation

6.7 Overview

6.8 References

(28)

•

Motivation

–

How can SQL applications

locate and retrieve information

in XML documents stored in an SQL database cell?

–

Invoking XML query language within SQL statements

6.4 Queries

–

Invoking XML query language within SQL statements

•

Retrieve information — in SELECT list

•

Locate information — in WHERE clause

–

Details on XML query language XQuery later

28

(29)

•

XMLQuery

–

A new

SQL expression

, invoked as a

pseudo-function, whose data type can be an XML type

—such as

XML(CONTENT(ANY))

—or an

ordinary SQL type

6.4 Queries

ordinary SQL type

•

XMLExists

–

A new

SQL predicate

, invoked as a pseudo-function,

returning

true

when the contained XQuery

expression returns anything other than the empty

sequence (

false

) or SQL null value (

unknown

)

29

(30)

•

XMLQuery

syntax

•

6.4 Queries

XMLQUERY(<

XQuery expression

>

[PASSING <

argument list

>]

{NULL | EMPTY} ON EMPTY)

argument list :=

<SQL

value

> AS <XQuery

variable

>

–

Example

•

30

[Tür08]

CityList

<State name="Niedersachsen"><City>Braunschweig</City></State>

<State name="Niedersachsen"><City>Hannover</City></State>

SELECT XMLQUERY(

'<State name="{$Name}"><City>{$City}</City></State>'

PASSING State as $Name, City AS $City NULL ON EMPTY)

AS CityList FROM Cities;

(31)

6.4 Queries

ID

Paper

123

<Paper>…<author>Alice</author><title>Perpetual

Motion</title><year>1999</year></Paper>

345

<Paper><year>2005</year><author>Bob</author><author>Charlie

</author><title>Beer</title>…</Paper>

CREATE TABLE Papers (ID INTEGER, Paper XML);

31

[Tür08]

</author><title>Beer</title>…</Paper>

ID

AuthorNames

123

<Authors>Alice</Authors>

345

<Authors>Charlie</Authors>

SELECT ID, XMLQUERY(

'FOR $a IN $p//author RETURN

<Authors>{$a/text()}</Authors>' PASSING Paper AS "p")

AS AuthorNames FROM Papers;

(32)

•

XMLTABLE

–

Provides an

SQL

view

of XML data

•

Output is

not

of the XML type

–

Evaluates an XQuery “row pattern” with

optional arguments (as with XMLQuery)

–

Element/attribute values mapped to columns using XQuery

“column patterns”

6.4 Queries

Element/attribute values mapped to columns using XQuery

“column patterns”

–

Names & types of columns required; default values optional

–

Syntax:

32

[Mel05]

XMLTABLE (<XQuery expression>

PASSING <argument list>

COLUMNS <column list>)

(33)

•

XMLTable

: Example

6.4 Queries

SELECT ID, t.*

FROM Papers p, XMLTABLE(

'for $root in $papers

where $root//author/text() = "Bob"

return $root/Paper'

PASSING p.Paper as "papers"

33

[Tür08]

ID

About

Created

345

Beer

2005

PASSING p.Paper as "papers"

COLUMNS

About VARCHAR(30) PATH '/Paper/title',

Created INTEGER PATH '/Paper/year'

(34)

6.1Introduction

6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries

6.5 Validation

6.7 Overview

6.8 References

(35)

•

Validation of XML

–

Is like integrity constraints in DBs

–

Requires an XML Schema

–

XML Schemas may be

registered

with the SQL-server

6.5 Validation

XML Schemas may be

registered

with the SQL-server

•

Implementation-defined mechanism

•

Known by SQL name & by target namespace URI

–

Schema does need a unique name

•

Used by

XMLValidate()

,

IS VALID

, and to restrict

values of

XML(DOCUMENT

-or-

CONTENT(XMLSCHEMA))

35

(36)

•

Schema registration

6.5 Validation

Register XMLSCHEMA

'http://www.Alfred-Moos.de/GrussSchema.xsd'

FROM 'file://c:/XML_Schemata/GrussSchema.xsd'

AS GrussSchema

COMPLETE

;

36

CREATE TABLE Dokument_XML

(Dokument_XML_Nr CHAR (4)

NOT NULL

PRIMARY KEY,

Dokument XML,

CONSTRAINT validieren

CHECK (Dokument IS VALIDATED

ACCORDING TO XMLSCHEMA

ID GrussSchema

)

(37)

•

Schema definition

–

Syntax

6.5 Validation

XML(CONTENT(XMLSCHEMA) <

schema

> [<

elements

>]))

<

schema

> := URI <

namespace

> [LOCATION <

loc

>]

| NO NAMESPACE [LOCATION <

loc

>]

–

37

[Tür08]

| NO NAMESPACE [LOCATION <

loc

>]

| ID <

registered schema name

>

<

element

> := [NAMESPACE <

namespace

>]

ELEMENT <

element name

>

(38)

•

New functions and predicates:

–

XMLValidate

•

Validates an XML value against an XML Schema (or target

namespace), returning new XML value with type annotations

–

IS VALID

•

Tests an XML value to determine whether or not it is valid

6.5 Validation

•

Tests an XML value to determine whether or not it is valid

according to an XML Schema (or target namespace); return

true/false

without altering the XML value itself

–

IS DOCUMENT

•

determines whether an XML value satisfies the (SQL/XML)

criteria for an XML document

–

IS CONTENT

•

determines whether an XML value satisfies the (SQL/XML)

criteria for XML content

38

(39)

•

Benefits of schema registration

–

Security issues

•

Schemas cannot “disappear” without

SQL-server knowing about it

•

Schemas cannot be “hijacked” (altered in

6.5 Validation

•

Schemas cannot be “hijacked” (altered in

inappropriate ways) without SQL-server

knowing about it

•

Documents cannot be marked “valid”

against schemas unless SQL-server knows

about them

39

(40)

•

Predefined schemas (build-in namespaces)

–

xs:http://www.w3.org/2001/XMLSchema

–

xsi:http://www.w3.org/2001/XMLSchema-instance

–

sqlxml:http://standards.iso.org/iso/9075/2003/sqlxml

–

More depending on the DB implementation

6.5 Validation

More depending on the DB implementation

•

Completely supported per XML+Namespaces:

–

XMLElement, XMLForest, XMLTable

–

Default namespace, explicit namespace (prefix)

–

Declare namespace within scopes of

WITH

clause, column

definitions, constraint definitions, insert/delete/update

statements, compound statements

40

(41)

6.1Introduction

6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries

6.5 Validation

6.7 Overview

6.8 References

(42)

•

SQL/XML standard published as

–

ISO/IEC 9074-14:2003

•

Mappings and Publishing Functions

–

ISO/IEC 9075-14:2006

•

Adds XQuery, including Data Model, Validation

•

Adds XQuery, including Data Model, Validation

–

ISO/IEC 9075-14:2008

•

Updates

•

Something else?

42

(43)

•

SQL/XML:2003 plus

–

Additional publishing functions

–

XQuery data model

–

More precise XML type (modifiers)

–

XMLQuery, XMLTable

–

XMLQuery, XMLTable

–

XMLValidate, IS VALID

–

XMLExists, IS DOCUMENT, IS CONTENT

–

Casting between XML type and SQL types

43

(44)

•

Overview of some operators for the XML

type

–

XMLELEMENT – creates an XML element node

–

XMLFOREST – creates a sequence of XML element nodes

from a table

–

XMLCOMMENT – creates an XML comment node

–

XMLTEXT – creates a text node

–

XMLPI – creates a processing instruction

–

XMLAGG – aggregates XML values of a group

–

XMLCONCAT – concatenates XML type values

–

XMLTRANSFORM – applies an XSL to a document

44

(45)

•

... Overview of some operators for the XML

type

–

XMLPARSE – a well-formed SQL text to XML value

–

XMLSERIALIZE – converts an XML value to a SQL text

–

XMLDOCUMENT – creates an XML document node

–

XMLDOCUMENT – creates an XML document node

from an XML value

–

XMLVALIDATE – validates an XML value with a schema

–

XMLQUERY – evaluates an XQuery expression

–

XMLTABLE – transforms an XQuery result to a SQL table

–

XMLITERATE – transforms an XQery sequence to a SQL

table

45

(46)

•

Review of SQL/XML

–

Two components

•

A data type XML to store XML data

•

Functions to map relational structures to XML

–

Only construction operators

•

No extraction of values or search

•

No extraction of values or search

•

But construction operators are based on XQuery

–

Mapping of tables, schemas, catalogues ignores some

information from the relational schema

•

UNIQUE

•

REFERENCES

•

CHECK

–

Further extensions are expected

46

(47)

1. Introduction

2. XML Basics

3. Schema definition

4. XML query languages I

5. Mapping relational data

8. XML query languages II

9. XML storage I

10. XML storage - index

11. XML storage - native

6.7 Overview

5. Mapping relational data

to XML

6. SQL/XML

7. XML processing

11. XML storage - native

12. Updates / Transactions

13. Systems

14. XML Benchmarks

47

(48)

•

"XML und Datenbanken" [Tür08]

–

Can Türker

–

Lecture, University of Zurich, 2008

•

Beginning XML Databases. [Pow07]

–

Gavin Powell

Wiley & Sons, 2007, ISBN 0471791202

6.8 References

–

Wiley & Sons, 2007, ISBN 0471791202

•

"XML-Datenbanken", [Kud07]

–

Thomas Kudraß

–

Lecture, HTWK Leipzig, WS2007/2008

•

"SQL/XML", [Mel05]

–

Jim Melton,

–

Oracle Corp. 2005

48

(49)

•

XQuery und SQL/XML in DB2-Datenbanken:

Verwaltung und Erzeugung von

XML-Dokumenten in DB2 [Moo08]

–

Alfred Moos

6.8 References

–

Vieweg+Teubner, 2008

•

ISO/IEC 907514:2003 Information Technology

-Database Languages - SQL - Part 14: XML-Related

Specifications (SQL/XML)

•

DB2 SQL-Reference, IBM, March 2008 [IBM]

49

(50)

•

Now, or ...

•

Room:

IZ 232

•

Office our:

Tuesday, 12:30 – 13:30 Uhr

Questions, Ideas, Comments

•

Office our:

Tuesday, 12:30 – 13:30 Uhr

or on appointment

•

Email:

[email protected]