XML and Databases Orcale 10gR2 XML DB SQL Server 2005 DB2 UDB 8.2 - PDF document

XML and Databases Orcale 10gR2 XML DB SQL Server 2005 DB2 UDB 8.2 XML Extender Helia / Martti Laiho 2005-2006 XML and Databases - Overview in terms of Standards Data Access Scripting languages (processors) SQL Programming APIs SQL XSL XQuery XQuery Xlink XSLT XSL-FO XSLT SQL/XML SQL/XML XPointer DOM? SAX native XPath DOM tree DOM tree XML Schema XML Schema Infoset “depends on” Namespaces “can make use of” XML DBMS Infra DBMS Infra Unicode Helia / Martti Laiho and Markku Kuitunen 2004 1

CLOB or Shredding XML Application document Application logic + SQL CLOB shredding into column relational tables CLOB value Helia / Martti Laiho 2005-2006 CLOB, XML or Shredding XML document CLOB/ XML XML shredding into column column ”nested tables” / ”side tables” CLOB/XML value Helia / Martti Laiho 2005-2006 2

Shredding and Publishing Source: MXL for DB2 Information Integration, www.redbooks.com => SQL/XML standard and implementations SQL/XML Work prepared by SQLX Group (www.sqlx.org) adapted by ISO into SQL standard part SQL/XML • Mapping SQL and XML concepts – Mapping Identifiers – Mapping Data Types – Mapping Data Values – Mapping SQL Tables • Publishing SQL Data using XML – Storing XML in SQL Databases – XMLType – Querying XML within SQL Databases – XML Publishing Functions Source: Jim Melton, Advanced SQL:1999 Understanding Object-Relational and Other Advanced Features, 2003 3

SQL/XML Mapping Identifiers • SQL regular identifiers – case-insensitive, upshifted internally – starts with a letter (A-Z), followed by letters, digits, underscore (_) – max length 18 chars (core SQL:1999) • SQL delimited identifier in quotes – ”Any chars.” • XML – case-sensitive – starts with a letter, followed by letters, digits, underscore (_), colon (: ) – must not start with ”XML” – no analogy with SQL delimited identifiers – any length SQL/XML Mapping Data Types SQL: • XML: • Predefined types • Simple types – exact numeric – primitive types • integer, smallint, ... • integer, .. – approximate numeric – built-in derived types – logical – derived types – character string types • facets: restriction, ... – binary types – datetime types • Constructed types • Complex types – array – sequence – row – multiset 4

Rewriting SQL + XPath into SQL Oracle SQL language implements now part of the SQL/XML standard functions and XPath expressions for processing XML documents. To make use of the capabilities of the already sophisticated database engine the XPath expressions and the following Oracle’s extended SQL functions are rewritten internally into pure SQL: ■ extract( ) ■ existsNode( ) ■ extractValue( ) ■ updateXML( ) ■ XMLSequence( ) The rewrite happens when these SQL functions are present in any expression in a query, DML, or DDL statement. Rewriting enables use of B-Tree, bitmap and other index access paths to be chosen by the cost-based optimizer. Source: XML Schemas in Oracle XML DB, VLDB 2003 For more detailed information see: Query Rewrite for XML in Oracle XML DB, VLDB 2004 SQL/XML – XML View on Relational Data Source: Oracle XML DB demo 5

“SQL/XML” – SELECT .. FOR XML SQL Server 2005 SELECT dname as "Name", loc as "Location", (SELECT empno as "employeeNumber", (SELECT ename as "Name", job as "Title", mgr as "Manager", CONVERT(CHAR(10),hiredate,20) as "StartDate", sal as "Salary", comm as "Commission" FROM Emp e -- <e> ... </e> WHERE Employee.empno = e.empno FOR XML AUTO, TYPE, ELEMENTS) -- ~ XMLForest(...) FROM Emp Employee WHERE Employee.deptno = Department.deptno FOR XML AUTO, TYPE, -- ~ XMLAgg(XMLElement("Employee", XMLAttributes ("employeeNumber"))) ROOT('EmployeeList')) -- ~ XMLElement("EmployeeList" FROM Dept Department FOR XML AUTO, ELEMENTS -- ~ XMLElement("Department") GO Relational View on XML data - OpenXML SQL Server 2005 CREATE TABLE PublishedMedia (ISBN int PRIMARY KEY, Title varchar(50), Publisher varchar(50)) GO CREATE PROCEDURE [dbo].[xml_insert] (@doc NTEXT) AS BEGIN declare @idoc int -- Create the internal representation exec sp_xml_preparedocument @idoc OUTPUT, @doc -- SELECT statement using OPENXML rowset provider INSERT INTO PublishedMedia SELECT * FROM OPENXML (@idoc, '/Books/Book', 1) WITH (ISBN int '@ISBN', Title varchar(50) '@Title', Publisher varchar(50) '@Publisher') -- Remove the internal representation EXEC sp_xml_removedocument @idoc END GO 6

SQL Server 2005 – XML Schema strongly typed XML column: col XML(schemaCollection) table schemaCollection id <schema … XML documents … in the table: <doc1 xmlns=… </schema> … </doc1> schema versions or different schemas <doc1 xmlns=… … </doc1> <schemaN … … </schemaN> <docN xmlns=… … </docN> Note: a table could have multiple XML columns each having different schema collection Martti Laiho 2005-04-29 Local Registering of a Schema CREATE XML SCHEMA COLLECTION MyDocSchemaColl AS N'<?xml version="1.0" encoding="UTF-16"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" local URL of the attributeFormDefault="unqualified" schema in the targetNamespace="http://MyDocSchema" > SQL Server instance <xsd:element name="MyXMLDoc"> <xsd:complexType> <xsd:sequence> <xsd:element name="DocID" type="xsd:string" /> <xsd:element name="DocBody" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>' ; GO Michael Otey: Microsoft SQL Server 2005 New Features 7

Strongly Typed XML Column CREATE TABLE MyDocs ( ID INT CONSTRAINT PK_MyDocs PRIMARY KEY IDENTITY, XmlDoc XML(DOCUMENT MyDocSchemaColl ) NOT NULL ) refering to the schema GO collection INSERT INTO MyDocs VALUES (N'<MyXMLDoc xmlns="http://MyDocSchema"> <DocID>1</DocID> <DocBody>My Text1</DocBody> refering to the URL of </MyXMLDoc>'); the registered schema in the collection Michael Otey: Microsoft SQL Server 2005 New Features Creating XML indexes -- primary index CREATE PRIMARY XML INDEX xmlidx1 ON xml_tb1 (xml_col); GO B+tree index on all tags, values, and paths of the XML instances in the column. Requires clustered primary key index for the base table (Source: Sankhar Pal et all, ”XML Support ..”) Secondary XML indexes based on the Primary XML index: -- document path is used to build the index CREATE XML INDEX xmls1 ON xml_tb1 (xml_col) USING XML INDEX xmlidx1 FOR PATH ; GO -- property index for name/value pairs CREATE XML INDEX xmlp1 ON xml_tb1 (xml_col) USING XML INDEX xmlidx1 FOR PROPERTY ; GO -- document node values are used to build the index CREATE XML INDEX xmlv1 ON xml_tb1 (xml_col) USING XML INDEX xmlidx1 FOR VALUE ; GO Source: Bob Beauchemin et al, A First Look at SQL Server 2005 for Developers 8

Oracle XML DB Oracle - Registering XMLSchema repository Schema’s URL begin DBMS_XMLSCHEMA.registerSchema ( 'http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd', xdbURIType('/home/SCOTT/poSource/xsd/purchaseOrder.xsd').getClob(), TRUE, -- local purchaseOrder.xsd TRUE, -- gentypes with annotations FALSE, -- genbean TRUE -- gentables PL/SQL Packages and Types Reference: ); end; types nested tables Martti Laiho 2005 9

Transforming XMLType Data CREATE TABLE xml_tab DBMS_XMLSCHEMA. registerSchema id number, xmlcol XMLType ) XMLType COLUMN xmlcol XML Schema XMLSCHEMA "<url>" url doc ELEMENT "<elementname>" xml_tab INSERT id xmlcol XML doc CREATE TABLE xsl_tab id number, SELECT XMLtransform ( stylesheet XMLType ) X.xmlcol, DBURIType('XDB/XSL_TAB/ROW [ID=1/STYLESHEET/text()'.getXML( )).getStringVal( ) xsl _tab AS result id stylesheet FROM xml_tab X result 1 XML doc INSERT XSL doc Helia / Martti Laiho 2005-2006 Transforming XMLType Data CREATE TABLE xml_tab DBMS_XMLSCHEMA. registerSchema id number, xmlcol XMLType ) XMLType COLUMN xmlcol XML Schema XMLSCHEMA "<url>" url doc ELEMENT "<elementname>" xml_tab INSERT id xmlcol XML doc CREATE TABLE xsl_tab id number, SELECT XMLtransform ( stylesheet XMLType ) X.xmlcol, (SELECT stylesheet FROM xsl_tab WHERE id = 1)).getStringVal( ) xsl _tab AS result id stylesheet FROM xml_tab X result 1 XML doc INSERT XSL doc Helia / Martti Laiho 2005-2006 10