- IfIS - Technische Universität Braunschweig
Werbung
11.02.2009
13. Systems
13.1 Introduction
13.2 Oracle
XML Databases
13.3 DB2
13. Systems
13.4 SQL Server
Silke Eckstein
Andreas Kupfer
Institut für Informationssysteme
Technische Universität Braunschweig
http://www.ifis.cs.tu-bs.de
13.5 Tamino
13.6 Summary
13.X Overview and References
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
13.1 Introduction
2
13.1 Introduction
• After discussing various aspects of XML and XML databases ...
• RDBMS with XML support
• Native XML-DBMS systems
• ... we are now going to have a closer look at some of the database
systems.
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
3
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
13. Systems
4
13.2 Oracle 11g
Architecture
13.1 Introduction
13.2 Oracle
13.3 DB2
13.4 SQL Server
13.5 Tamino
13.6 Summary
13.X Overview and References
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
Figure taken from Oracle®
XML Developer's Kit
Programmer's Guide 11g
Release 1 (11.1), April 2008
5
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
6
1
11.02.2009
13.2 Oracle 11g
13.2 Oracle 11g
Architecture (2)
• Mapping variants from XML to databases
– XML column approach: Column is based on XML type
– XML table approach: Table is based on XML type
• Using objectrelational extensions of Oracle
– XMLTYPE as predefined object type with SQL/XML
functions as methods
– Intermedia-Text-Package with full text functions
– DBMS_XMLDOM package with DOM methods
– DBMS_XMLSCHEMA package with administration and
generation methods
– DBMS_XMLGEN package with methods to generate XML
from SQL
Figure taken from Oracle®
XML DB Developer’s
Guide 11g Release 1 (11.1)
October 2007
7
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
[Tür08]
13.2 Oracle 11g
8
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
13.2 Oracle 11g
• Storage options
–
–
–
–
text-based (unstructured as CLOB)
binary (compact storage in XML binary format)
schema-based (object-relational storage requires XML Schema)
hybrid (semistructured)
Figure taken from
Oracle® XML DB
Developer’s Guide
11g Release 1 (11.1)
October 2007
Figure taken from
Oracle® XML DB
Developer’s Guide
11g Release 1 (11.1)
October 2007
[Tür08]
9
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
• User-defined function getDocument(file) to
read XML documents
• XML-column vs. XML-table approach
– Table with XML column
CREATE DIRECTORY xmldir AS 'c:\xmldir';
GRANT READ ON DIRECTORY xmldir TO PUBLIC WITH GRANT OPTION;
CREATE FUNCTION getDocument(filename VARCHAR2)
RETURN CLOB
AUTHID CURRENT_USER IS
xbfile BFILE;
xclob CLOB;
BEGIN
xbfile := BFILENAME('xmldir', filename);
DBMS_LOB.open(xbfile);
DBMS_LOB.createTemporary(xclob TRUE, DBMS_LOB.session);
DBMS_LOB.loadFromFile(xclob, xbfile,
DBMS_LOB.getLength(xbfile));
DBMS_LOB.close(xbfile);
RETURN xclob;
END;
/
CREATE TABLE <table name> ( <column name> XMLTYPE)
[XMLTYPE [COLUMN] <column name>
[STORE AS {OBJECT RELATIONAL | CLOB ( <LOB parameter>) | BINARY XML ( <LOB parameter>) })
[XMLSCHEMA <url> ELEMENT [ <url> #] <element> ]]
text-based
binary
– XML table
CREATE TABLE <table name> OF XMLTYPE
[XMLTYPE
[STORE AS {OBJECT RELATIONAL | CLOB ( <LOB parameter>) | BINARY XML ( <LOB parameter>) })
[XMLSCHEMA <url> ELEMENT [ <url> #] <element> ]]
– Inserting documents in both cases
INSERT INTO table VALUES (XMLTYPE (getDocument('input1.xml')));
[Tür08]
10
13.2 Oracle 11g
13.2 Oracle 11g
schema-based
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
11
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
12
2
11.02.2009
13.2 Oracle 11g
13.2 Oracle 11g
• Package DBMS_XMLSCHEMA offers methods
to register, compile, generate and delete XML
Schemas
• Some methods of the XMLTYPE
– XMLTYPE(<value-expr>) is the constructor. Expression can be a string or a
user defined type
– getClobVal()/getStringVal() returns XML value as CLOB or string
DBMS_XMLSCHEMA.registerSchema( 'schema-URL', 'schema-name' );
DBMS_XMLSCHEMA.registerSchema( 'text.xsd', getDocument('test.xsd') );
– getNumVal() only applicable to text nodes containing a numeric string
DBMS_XMLSCHEMA.compileSchema( 'schema-URL' );
– isFragment() returns 1 if instance has more than one root element
DBMS_XMLSCHEMA.generateSchema( 'schema-URL', 'type-name' );
– existsNode(<XPath-expr>) returns 1 if the expression returns a node
DBMS_XMLSCHEMA.deleteSchema( 'schema-URL', DeleteOption );
– extract(<XPath-expr>) extracts a part of the XML value
– transform(<XML-value-expr>) transforms according to a stylesheet
DeleteOption:
DELETE_RESTRICT
DELETE_INVALIDATE
DELETE_CASCADE
DELETE_CASCADE_FORCE
– toObject() converts to an object
– isSchemaBased() returns 1 if the XML value is based on a schema
– getSchemaURL() returns the URL to the schema
– getRootElement() returns the root element or NULL for fragments
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13
13.2 Oracle 11g
14
13.2 Oracle 11g
• Queries
• EXTRACT
– Support of SQL/XML functions
•
•
•
•
•
•
•
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
– extracts an excerpt of the XML value described by an
XPath query
XMLQUERY
XMLTABLE
XMLAGG
XMLELEMENT
XMLATTRIBUTE
XMLFOREST
…
EXTRACT( <XML-value-expression>, <XPath-expression> [, <Namespace>] )
SELECT
EXTRACT( VALUE(b), '//@ISBN' ) AS ISBNumber,
EXTRACT( VALUE(b), '//Title/text()' ) AS Title_content,
EXTRACT( VALUE(b), '//Title' ) AS Title_element
FROM Book b;
– And additional functions
• EXTRACT
• EXISTSNODE
• ...
– Full text search with the Intermedia-Text-Package
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
15
ISBNumber
Title_content
Title_element
3-89864-148-1
XML &amp; Datenbanken
<Title>XML &amp; Datenbanken</Title>
3-89864-219-4
SQL-1999 &amp; SQL:2003
<Title>SQL-1999 &amp; SQL:2003</Title>
13.2 Oracle 11g
16
13.2 Oracle 11g
• Indexing
• EXISTSNODE
– Full text index
– Returns 0 if the query returns the empty sequence
EXISTSNODE( <XML-value-expression>, <XPath-expression> [, <Namespace>] )
Example:
SELECT
EXTRACT( VALUE(b), '//@ISBN' ) AS ISBNumber,
EXTRACT( VALUE(b), '//Title/text()' ) AS Title_content,
EXTRACT( VALUE(b), '//Title' ) AS Title_element
FROM Book b
WHERE EXISTSNODE( VALUE(b), '//Book[@ISBN="3-89864-219-4"]' ) = 1;
ISBNumber
Title_content
Title_element
3-89864-219-4
SQL-1999 &amp; SQL:2003
<Title>SQL-1999 &amp; SQL:2003</Title>
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
17
CREATE INDEX xmlfulltextidx ON Book b
(VALUE(b)) INDEXTYPE IS CTXSYS.CONTEXT;
– Path index
CREATE INDEX xmlpathidx ON Book b
(VALUE(b)) INDEXTYPE IS CTXSYS.CTXXPATH;
– Functional index
(value index)
– XML index
CREATE INDEX xmlfunctionalidx ON Book b
(EXTRACTVALUE(VALUE(b),'//@year'));
CREATE INDEX xmlidx ON Book b
(VALUE(b)) INDEXTYPE IS XDB.XMLIndex;
• Creates a set of secondary indexes
– Path index with all XML tags and fragments
– Value index with the oder of the document (node positions)
– Value index to index the values of the nodes
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
18
3
11.02.2009
13.2 Oracle 11g
13.2 Oracle 11g
• Manipulation – UPDATEXML
• Using indexes
– Change a part (defined by an XPath query) of the XML value
– Query using the path index:
UPDATEXML (<XML-value-expr>, <replacement-list> [, <namespace>])
<replacement-list> := <XPath-expr>, <value-expr>
SELECT EXTRACTVALUE (VALUE(b),'//Title') AS Title FROM Book b
WHERE EXISTSNODE (VALUE(b),'/Book/Publisher[text()="dpunkt"]') = 1;
– Example to change the value of an attribute:
UPDATE Book b
SET VALUE(b) = UPDATEXML (VALUE(b),'//Publisher[text()="dpunkt"]/@City', 'Zürich');
– Query using the full text index:
• Manipulation – DELETEXML
SELECT SCORE (o), EXTRACT(VALUE(b),'//@ISBN') AS ISBN FROM Book b
WHERE CONTAINS (VALUE(b),'Java', o) > o ORDER BY SCORE (o) DESC;
– Deletes a sequence of nodes (selected by an XPath query)
from the XML value
– Query using the functional index:
DELETEXML (<XML-value-expr>, <replacement-list> [, <namespace>])
– Example to delete a specific Author node:
SELECT EXTRACTVALUE (VALUE(b),'//Title') AS Title FROM Book b
WHERE EXTRACTVALUE (VALUE(b),'//Year') = 2009;
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
UPDATE Book b
SET VALUE(b) = DELETEXML (VALUE(b),'//Book[@ISBN="3-89864-148-1"]/Author[text()="Holger Meyer"]');
19
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.2 Oracle 11g
20
13.2 Oracle 11g
• Export of database contents with XML syntax
• XML views
– Standard mapping: SQL XML with
– Allow XML-based views on SQL and XML values
– Are based on the principle of object views
DBMS_XMLGEN.getXML('query')
• Top level elements result from columns
• Simple types (with scalar values) as elements with PCDATA
• Structured types and their attributes as elements with subelements for
attributes
• Complex attributes as hierarchically nested elements
• Collection types are mapped to lists of elements
• Object references and referential integrity as ID/IDREF within the
document
• Table content is mapped to ROWSET elements:
• The object type is XMLTYPE in this case
– Example:
CREATE VIEW DpunktBooks OF XMLTYPE
WITH OBJECT ID DEFAULT
AS SELECT VALUE (b) FROM Book b
WHERE EXISTSNODE (VALUE(b),'//Publisher[text()="dpunkt"]') ;
<ROWSET>
<ROW num="1" > … </ROW>…<ROW num="n" > … </ROW>
</ROWSET>
– User defined transformation from SQL to XML is possible with XSLT
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
21
13.2 Oracle 11g
[Tür08]
Extensible, object relational
Schema definition
Validation possible
Storage type
Text-based or schema-based
Mapping DB XML
By SQL/XML functions, schema
generators, XML views
XML data type
Available
Value/function index
Available
Full text index
Available
Path index
Available
Queries
SQL/XML with XQuery support
Full text search
With the Intermedia-Text-Package
Manipulation
SQL methods with XPath
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
22
13. Systems
• Summary Oracle XML support
XML storage modelccccc
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.1 Introduction
13.2 Oracle
13.3 DB2
13.4 SQL Server
13.5 Tamino
13.6 Summary
13.X Overview and References
23
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
24
4
11.02.2009
13.3 DB2 V9
13.3 DB2 V9
• Mapping XML data to relational databases
• IBM DB2
– Variants:
XML documents
Application
• XML column approach: based on XML data type
• XML collection approach: based on decomposition of XML documents into
database tables and attributes
– Table with XML column:
• Diverse XML datatypes:
PureXML
– XML:
modelbased / hierarchical storage
– XMLCLOB:
XML documents stored as CLOBs
– XMLVARCHAR:
XML documents stored as VARCHAR XML
– XMLFILE:
XML documents stored in file system Extender
• XML schema validation for datatype XML only
• In addition: materialized views
– Extract selected XML content from documents
– Materialise those content into so-called side tables
– Side tables are defined in Document Access Definition (DAD)
file
system
Database
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
25
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.3 DB2 V9
13.3 DB2 V9
• Ways to put XML
data into the
database
(PureXML)
• "pureXML and relational hybrid database"
[IBM06a]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
26
27
[IBM06b]
13.3 DB2 V9
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
28
13.3 DB2 V9
• Ways to get
XML data out of
the database
(PureXML)
• PureXML – Queries and Indexes
– Application of SQL in XQuery:
XQUERY db2-fn:xmlcolumn (‘t1.xml1’)
– Delivers the value of column xml1 of table t1 as a node
sequence (column must be of type XML)
XQUERY db2-fn:sqlquery (’SELECT xml1 FROM t1’)
– Delivers the XML value of the single-column table t1 as a
node sequence (column must be of type XML)
– Definition of a path index:
CREATE INDEX Idx_Author_Path ON Book (Content)
GENERATE KEY
USING XMLPATTERN '//Author' AS SQL VARCHAR(50)
[IBM06b]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
29
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
30
5
11.02.2009
13.3 DB2 V9
13.3 DB2 V9
• XML Extender – Mapping between XML and SQL
• XML Extender – Tables with XML Types
– XML extension setup with XML Extender Admin Wizard
or Command Window:
> dxxadm enable_db XMLDB
– Definition of tables accepting XML documents:
• Variant 1: Create with XML Extender Admin Wizard
• Variant 2: SQL
CREATE TABLE Buch
(Inhalt DB2XML.XMLVARCHAR)
– Insertion of an XML document:
INSERT INTO Buch (Inhalt)
VALUES (DB2XML.XMLVARCHARFromFile('C:\XMLDIR\buch01.xml'))
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
31
[Tür08]
13.3 DB2 V9
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
32
13.3 DB2 V9
• ExtractXXX(<XML value expression>, <XPath expression>)
• XML Extender – Queries
– SQL-XML Extender offers functions for queries and
updates
• Extract functions:
DB2XML.EXTRACT<datatype>(<XML value expression>, <XPath expression>)
• Example:
SELECT a.RETURNEDVARCHAR
FROM Buchlob, TABLE(DB2XML.EXTRACTVARCHARS(Inhalt, '//Autor')) a
– Limited supportof SQL/XML standard
• XMLAGG
• XMLELEMENT
• XMLATTRIBUTE
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
"IBM DB2 Universal Database XML Extender
Administration and Programming, Version 8, 2002"
33
[Tür08]
13.3 DB2 V9
• XML Extender – Indexing
– Updates possible with special XML Extender methods
– Syntax:
– Index support
DB2XML.UPDATE(<XML value expression>, <XPath expression >, <new value>)
• Value index (B-Tree, Bitmap, etc.) on side tables (XML
Extender)
• Full text index (with Text Extender) on XML types
– Restriction: predicates with elements are not supported
• Example: not supported predicate
UPDATE Buchlob
SET Inhalt = DB2XML UPDATE(Inhalt '//Verlag[text()="dpunkt"]/@Ort'
'Zürich')
– Extension of full text index for IR on XML
• Path information included in index
• Support for path expressions
• Example:
• Example: supported predicate
UPDATE Buchlob
SET Inhalt = DB2XML.UPDATE(Inhalt, '// Buch[@ISBN="3-89864-1481"]/Verlag/ @Ort', 'Köln')
– With XML column approach updates are transferred to side tables
automatically
– In PureXML an XML value can only be fully replaced
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
34
13.3 DB2 V9
• XML Extender – Updates
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
Retrival model
SELECT Inhalt
FROM Buchlob
WHERE contains(dscrHandel, ‘MODEL order
SECTION(//Buch/Beschreibung) "Datenbank"‘) = 1
35
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
36
6
11.02.2009
13.3 DB2 V9
13. Systems
• Summary IBM DB2 XML Support
[Tür08]
13.1 Introduction
XML storage model
Extensible, object relational
Schema definition
Validation possible
13.2 Oracle
Storage type
Model-based (PureXML), text-based or
userdefined schema-based (XML Extender)
13.3 DB2
Mapping DB XML
DAD (XML Extender)
13.4 SQL Server
XML data type
Available (PureXML)
Value/function index
Standard DBS indexes on side tables
Full text index
With TextExtender
Path index
Available
Queries
SQL/XML with XQuery support
Full text search
With TextExtender
Manipulation
SQL functions with XPath
13.5 Tamino
13.6 Summary
13.X Overview and References
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
37
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
13.4 SQL Server
13.4 SQL Server
• Mapping XML data to relational databases
• Microsoft SQL Server Architecture
Application
38
– 4 storage variants:
XML documents
•
•
•
•
Native (binary) storage
Text-based storage as CLOB
Model-based storage according to EDGE approach
Schema-based storage via STORED-queries
– Datatype XML with methods based on XQuery
•
•
•
•
•
Query() – evaluates an XQuery and returns a value of type XML
Value() – evaluates an XQuery and returns a scalar SQL value
Exist() – returns true, if XQuery result is not empty
Modify() – updates a value of type XML
Nodes() – returns subtree of XML value
– Integrated Usage of SQL and XQuery
• Access to SQL data in XQuery via sql:column() and
sql:variable()
• Evaluation of XQuery expressions in SQL via XML methods from above
Database
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
39
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.4 SQL Server
40
13.4 SQL Server
• Native storage – SQL/XML queries & updates
• Native storage – table definition
– Find all author elements from books whose first author is
"Gunter Saake"
– Schema registration
CREATE XML SCHEMA COLLECTION BuchXSD
AS '<?xml version="1.0"?>…'
SELECT Inhalt.query('//Autor') AS Autoren
FROM Buch
WHERE Inhalt.exist('/Buch[Autor[1] = "Gunter Saake"]') = 1
– Table definition
Autoren
CREATE TABLE Buch (
Id
INT PRIMARY KEY,
Inhalt XML BuchXSD)
)
<Autor>Gunter Saake</Autor><Autor>Ingo Schmitt</Autor>
<Autor>Can Türker</Autor>
<Autor>Gunter Saake</Autor><Autor>Kai-Uwe Sattler</Autor>
– Insertion of an XML document from a file
– Update the value of the attributes "City" from all those
publisher elements to "Zürich", where the publisher is "dpunkt"
INSERT INTO Buch
SELECT 1, xCol
FROM (SELECT *
FROM OPENROWSET
(BULK 'C:\XMLDIR\buch1.xml', SINGLE_BLOB) AS xCol)
AS R(xCol)
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
UPDATE Buch
SET Inhalt.modify
('replace value of (//Verlag[. = "dpunkt"]/@Ort)[1]
with "Zürich"')
41
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
42
7
11.02.2009
13.4 SQL Server
13.4 SQL Server
• Model-based storage with EDGE
• Native storage – indexing
– Definition of a primary XML indexes
– Invocation of OPENXML without WITH claus creates EDGE
table
CREATE PRIMARY XML INDEX Idx_Inhalt ON Buch (Inhalt)
• Creates clustered index with entries of form (ID, ORDPATH, TAG, NODETYPE,
VALUE, PATH_ID, ...)
• necessary in order to create secondary indexes
– Secondary XML index types:
PATH | PROPERTY | VALUE
• Path index (path, value)
• Property index (primary key, path, value)
• Value index (value, path)
– Definition of a secondary XML index:
CREATE XML INDEX Idx_Inhalt_Path ON Buch (Inhalt)
USING XML INDEX Idx Inhalt
FOR <Indextyp>
– Full text index is also supported:
CREATE FULLTEXT INDEX Idx_Inhalt_FT ON Buch (Inhalt)
KEY INDEX b
43
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
unique node id
parentid
bigint
parent node id
nodetype
int
distinguishes elements, attributes, comments
localname
nvarchar
tag
prefix
nvarchar
XML namespace prefix
namespaceuri
nvarchar
XML namespace URI
datatype
nvarchar
datatype (derived from DTD or XML schema)
prev
bigint
id of previous node (in document order)
text
ntext
node content
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
44
• Schema-based storage of STORED queries
– SQL extension with OPENXML
– OPENXML transforms XML contents into database tables
(shredding)
– OPENXML therefore offers possibility to implement STORED
queries
– Example for the realization of a STORED query:
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldoctext
INSERT INTO EDGE
SELECT *
FROMOpenXML (@hdoc, '', 0)
EXEC sp_xml_removedocument @hdocC
– EDGE table:
id
parent
nodetype
localname
prefix
namespaceuri
datatype
prev
text
0
NULL
1
book
NULL
NULL
NULL
NULL
NULL
6
3
#text
NULL
NULL
NULL
NULL
'Vossen'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldoctext
INSERT INTO book
SELECT *
FROM OpenXML (@hdoc, '//book/', 0) WITH
(
title
NVARCHAR(3000) ‘./title',
publisher NVARCHAR(200) ‘./publisher‘,
isbn
NVARCHAR(15)
‘./isbn‘
)
EXEC sp_xml_removedocument @hdoc
...
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
45
46
13.4 SQL Server
• Mapping of databases to XML
• Updates
– SQL Server does not offer functions to update XML documents
stored as CLOBs
– Variant 1: Standard transformation with SQL SELECT and FOR
XML clause
• Results in heavy restrictions of text-based approach
• FOR XML RAW: Transformation in ROW-XML elements and XML
attributes
• FOR XML AUTO:
– Semantically rich XML element names
– Foreign key relationships are transformed into hierarchies
• FOR XML EXPLICIT: User controls XML assembling through metadata
(EDGE)
– Updates for schema-based approach possible via so called
updategrams
• Builds on annotated XML schemas
• Updates are specified as an XML document
• New namespace:
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
– Element before: Definition of a previous state (to be modified)
– Element after:
Definition of the new state
• Different update operations through varying element contents
– Insert:
before element remains empty
– Delete:
after element remains empty
– Update:
both elements have non-empty contents
• Automatic execution of necessary database operations
– Variant 2: User defined XML view
• Use of a (available) XML schema
• Annotation of the schema with information about tables and columns
• Accesss from the application to the XML view via:
– IIS functionality
– ADO (ActiveX Data Objects) – middleware for DB access
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.4 SQL Server
[Tür08]
Task
bigint
13.4 SQL Server
• Model-based storage with EDGE
[Tür08]
Datatype
id
[Tür08]
13.4 SQL Server
17
Column
47
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
48
8
11.02.2009
13.4 SQL Server
13.4 SQL Server
• Updates: updategram example
• Summary SQL Server XML support
– Update of publisher information
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<Buch>
<Titel> Objektdatenbanken </Titel>
<ISBN>3-8266-00258-7 </ISBN>
<Verlag> Thomson </Verlag>
</Buch>
</updg:before>
<updg:after>
<Buch>
<Titel> Objektdatenbanken </Titel>
<ISBN>3-8266-00258-7 </ISBN>
<Verlag> International Thomson Publishing </Verlag>
<Buch>
</updg:after>
</updg:sync>
</ROOT>
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
XML storage model
Relational
Schema definition
inline DTD or XML schema
Storage type
Native: XML column
text-based: CLOB column
modelbased: with OPENXML
user-defined schema-based: with OPENXML-STORED queries
Mapping DB XML
Automatically: FOR XML clause
user-defined: XSD annotations
XML data type
Available
Value index
Available
Full text index
No XML specific functions
Path index
Available
Queries
SQl extensions (query and value not compatible with
SQL/XML), XQuery
XML method modify with updategrams
Manipulation
49
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13. Systems
50
13.5 Tamino
• Architecture
13.1 Introduction
13.2 Oracle
13.3 DB2
13.4 SQL Server
13.5 Tamino
13.6 Summary
13.X Overview and References
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
51
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.5 Tamino
13.5 Tamino
• Storage structures: Mapping of XML
• Architecture (2)
XML Output
52
Query (URL)
– Tamino uses "native" storage structures for XML data
– Native storage is supplemented with diverse classical index types
XML Objects, DTDs
• B-Tree index
• Full text index
• Path index
– Storage alternatives:
• Storage of well-formed XML documents without schema
• Storage of valid XML documents
– Annotation of schema definition with storage alternatives
– Storage hierarchy:
Data from external
sources and/or
internal data storage
[Tür08]
•
•
•
•
Data to external
sources and/or
internal data storage
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
53
[Tür08]
Tier 1: Tamino
Tier 2: Collection
Tier 3: Document type (defined by set of XML schema definitions)
Tier 4: document instance
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
54
9
11.02.2009
13.5 Tamino
13.5 Tamino
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:tsd="namespaces.softwareag.com/tamino/TaminoSchemaDefinition">
<xs:annotation> <xs:appinfo>
<tsd:schemaInfo name="book">
<tsd:collection name="books"></tsd:collection>
<tsd:doctype name="book">
<tsd:logical> <tsd:content>open<tsd:content></tsd:logical>
</tsd:doctype>
</tsd:schemaInfo>
</xs:appinfo> </xs:annotation>
<xs:element name = "book">
<xs:complexType> <xs:sequence>
<xs:element name = "title" type = "xs:string"></xs:element>
…
<xs:element name = "summary" type = "xs:string">
<xs:annotation> <xs:appinfo>
<tsd:elementInfo>
<tsd:physical> <tsd:native>
<tsd:index> <tsd:text></tsd:text> </tsd:index>
</tsd:native> </tsd:physical>
</tsd:elementInfo>
</xs:appinfo> </xs:annotation>
</xs:element>
</xs:sequence> </xs:complexType>
</xs:element>
• Queries
– Access possibilities
• Program controlled, e.g. via DCOM components
• Ad-hoc queries with X-Plorer query tool
• "Interactive Interface"
– Supported query languages
• XPath 1.0 dialect with extensions for text search (also possible without
index)
– Containedness (~=)
/Buch[Titel ~= "Datenmodelle"]/Beschreibung
– Wildcard character (*)
/*[. ~= "*XML*"]
Storage:
Example schema
with annotations
for text index
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
– Consideration of context (NEAR)
/*[/Autor ~= "Gunter" NEAR "Saake"]
• XQuery dialect
55
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.5 Tamino
56
13.5 Tamino
• Indexing
• Updates
– Classical indexes for data
– Operations
• Numbers and strings
– Text indexes for document centric parts
• Delete:
• With wildcards
UPDATE DELETE $buch//Verlag[@Ort="Zürich"]/@Ort
– Structure index
• Insert:
• Full
• Condensed
UPDATE INSERT <Preis Waehrung="EUR">35</Preis>
INTO $buch[@ISBN="3-8266-0258-7"]
– Combined index
• Multiple elements and attributes, even on different levels
• Replace:
– Multi path index
UPDATE REPLACE $buch//Verlag[@Ort="Zürich"]/@Ort
WITH ATTRIBUTE Ort {"Wiesbaden"}
• Different paths indexed together
– Reference index
• Hierarchy aware index
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
57
13.5 Tamino
58
13. Systems
• Summary Tamino
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Tür08]
13.1 Introduction
Native
Relational
Schema definition
Validation possible
Storage type
Model-based
Mapping DB XML
Native
XML data type
Available
Value index
Available
Full text index
Available
Path index
Available
Queries
Tamino X-Query (with extensions and small
differences compared to W3C XQuery)
Full text search
Supported
Manipulation
Supported
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
13.2 Oracle
13.3 DB2
13.4 SQL Server
13.5 Tamino
13.6 Summary
13.X Overview and References
59
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
60
10
11.02.2009
13.5 Summary
13.6 Overview
Introduction XML Basics Schema definition XML query languages I Mapping relational data
to XML 6. SQL/XML 7. XML processing 1.
2.
3.
4.
5.
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
61
13.6 References
8. XML query languages II –
XQuery Data Model 9. XML query languages III
– XQuery 10. XML storage I –
Overview
11. XML storage II 12. Updates 13. Systems XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
62
Questions, Ideas, Comments
• "XML und Datenbanken" [Tür08]
• Now, or ...
– Can Türker
– Lecture, University of Zurich, 2008
• "XML und Datenbanken" [KM03]
– M. Klettke, H. Meier
– dpunkt.verlag, 2003
• Room:
IZ 232
• Office our:
Tuesday, 12:30 – 13:30 Uhr
or on appointment
• " DB2 9 pureXML Guide" [IBM06a]
• Email:
– IBM
– December 2006
[email protected]
• "DB2 Version 9. XML Guide" [IBM06b]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
63
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
64
11