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

▶
xml and databases
SMART_READER_LITE
LIVE PREVIEW

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

Jul 28, 2023 202 likes •344 views

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

slide-1
SLIDE 1

1

XML and Databases

Helia / Martti Laiho 2005-2006

Orcale 10gR2 XML DB SQL Server 2005 DB2 UDB 8.2 XML Extender

XSL XSL-FO XSLT XSLT XPath XQuery XQuery Xlink DOM? SAX Unicode XML Namespaces DOM tree DOM tree XML Schema XML Schema “depends on” “can make use of”

Helia / Martti Laiho and Markku Kuitunen 2004

SQL/XML SQL/XML SQL SQL Data Access Scripting languages (processors) Programming APIs DBMS Infra DBMS Infra Infoset native XPointer XML and Databases

  • Overview in terms of Standards
slide-2
SLIDE 2

2

CLOB or Shredding

Helia / Martti Laiho 2005-2006 Application logic + SQL

XML document

CLOB column CLOB value

shredding into relational tables

Application

CLOB, XML or Shredding

Helia / Martti Laiho 2005-2006

XML document

CLOB/ XML column CLOB/XML value XML column

shredding into ”nested tables” / ”side tables”

slide-3
SLIDE 3

3

Shredding and Publishing

Source: MXL for DB2 Information Integration, www.redbooks.com

=> SQL/XML standard and implementations

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 Work prepared by SQLX Group (www.sqlx.org) adapted by ISO into SQL standard part SQL/XML

slide-4
SLIDE 4

4

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:

  • Predefined types

– exact numeric

  • integer, smallint, ...

– approximate numeric – logical – character string types – binary types – datetime types

  • Constructed types

– array – row – multiset

  • XML:
  • Simple types

– primitive types

  • integer, ..

– built-in derived types – derived types

  • facets: restriction, ...
  • Complex types

– sequence

slide-5
SLIDE 5

5

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

slide-6
SLIDE 6

6

“SQL/XML” – SELECT .. FOR XML

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

SQL Server 2005

Relational View on XML data - OpenXML

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

SQL Server 2005

slide-7
SLIDE 7

7

SQL Server 2005 – XML Schema

strongly typed XML column: col XML(schemaCollection)

table

XML documents in the table:

schemaCollection

<schema … … </schema> <schemaN … … </schemaN> <doc1 xmlns=… … </doc1> <doc1 xmlns=… … </doc1> <docN xmlns=… … </docN> id

Martti Laiho 2005-04-29 schema versions

  • r different schemas

Note: a table could have multiple XML columns each having different schema collection

Local Registering of a Schema

local URL of the schema in the SQL Server instance

Michael Otey: Microsoft SQL Server 2005 New Features

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" attributeFormDefault="unqualified" targetNamespace="http://MyDocSchema" > <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

slide-8
SLIDE 8

8

Strongly Typed XML Column

CREATE TABLE MyDocs ( ID INT CONSTRAINT PK_MyDocs PRIMARY KEY IDENTITY, XmlDoc XML(DOCUMENT MyDocSchemaColl ) NOT NULL ) GO INSERT INTO MyDocs VALUES (N'<MyXMLDoc xmlns="http://MyDocSchema"> <DocID>1</DocID> <DocBody>My Text1</DocBody> </MyXMLDoc>');

Michael Otey: Microsoft SQL Server 2005 New Features

refering to the URL of the registered schema in the collection refering to the schema collection

Creating XML indexes

  • - primary index

CREATE PRIMARY XML INDEX xmlidx1 ON xml_tb1 (xml_col); GO

  • - 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

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:

slide-9
SLIDE 9

9

Oracle XML DB Oracle - Registering XMLSchema

begin DBMS_XMLSCHEMA.registerSchema ( 'http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd', xdbURIType('/home/SCOTT/poSource/xsd/purchaseOrder.xsd').getClob(), TRUE, -- local TRUE, -- gentypes FALSE, -- genbean TRUE

  • - gentables

); end; purchaseOrder.xsd with annotations

Schema’s URL

PL/SQL Packages and Types Reference:

repository nested tables types

Martti Laiho 2005

slide-10
SLIDE 10

10

Transforming XMLType Data

XML doc XML Schema doc

DBMS_XMLSCHEMA.registerSchema

url

CREATE TABLE xml_tab id number, xmlcol XMLType) XMLType COLUMN xmlcol XMLSCHEMA"<url>" ELEMENT "<elementname>" xml_tab id xmlcol INSERT CREATE TABLE xsl_tab id number, stylesheet XMLType) xsl _tab id stylesheet

XSL doc

INSERT SELECT XMLtransform( X.xmlcol, DBURIType('XDB/XSL_TAB/ROW [ID=1/STYLESHEET/text()'.getXML( )).getStringVal( ) AS result FROM xml_tab X 1

XML doc

result Helia / Martti Laiho 2005-2006

Transforming XMLType Data

XML doc XML Schema doc

DBMS_XMLSCHEMA.registerSchema

url

CREATE TABLE xml_tab id number, xmlcol XMLType) XMLType COLUMN xmlcol XMLSCHEMA"<url>" ELEMENT "<elementname>" xml_tab id xmlcol INSERT CREATE TABLE xsl_tab id number, stylesheet XMLType) xsl _tab id stylesheet

XSL doc

INSERT SELECT XMLtransform( X.xmlcol, (SELECT stylesheet FROM xsl_tab WHERE id = 1)).getStringVal( ) AS result FROM xml_tab X 1

XML doc

result Helia / Martti Laiho 2005-2006

slide-11
SLIDE 11

11

Dept10Employee

Transforming SQL Data per Rows

10 EMP empno ename ... sal deptno SELECT XMLTransform(XMLElement("Emp", XMLForest(e.empno, e.ename, e.sal)) , XMLType('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="*"> <ID><xsl:value-of select="/Emp/EMPNO"/></ID> <NAME><xsl:value-of select="/Emp/ENAME"/></NAME> </xsl:template> </xsl:stylesheet>')).GetClobVal() AS Dept10Employee FROM Scott.emp e WHERE Deptno = 10 / XML doc 10 10 XML doc XML doc XMLelement( .. ) XML doc XML doc XML doc XMLtransform( .. ) XSL Helia / Martti Laiho 2005-2006 Dept10Employees

Transforming SQL Data as Forest

SELECT XMLTransform(XMLElement("Dept10", XMLForest(CAST(MULTISET(SELECT empno, ename FROM Scott.emp e WHERE e.deptno = 10) AS emplist_t) AS empforest) ), XMLType('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="*"> <xsl:for-each select="//EMP_T"> <ID><xsl:value-of select="@EMPNO"/></ID> <NAME><xsl:value-of select="ENAME"/></NAME> </xsl:for-each> </xsl:template> </xsl:stylesheet>')).GetClobVal() AS Dept10Employees FROM DUAL / XML doc 10 EMP empno ename ... sal deptno 10 10 XMLelement( .. XMLforest (.. ) ) XML doc XMLtransform( .. ) XSL emplist_t emp_t Helia / Martti Laiho 2005-2006

slide-12
SLIDE 12

12

dept_t Dept10Employees

Transforming SQL Data of MultiTables

SELECT XMLTransform(XMLElement("Dept10", XMLForest( dept_t(deptno, dname, CAST (MULTISET (SELECT empno, ename FROM Scott.emp e WHERE e.deptno = d.deptno) AS emplist_t) ) AS "Department" )), XMLType('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="*"> <DEPT><xsl:value-of select="//DNAME"/></DEPT> <xsl:for-each select="//EMP_T"> <ID><xsl:value-of select="@EMPNO"/></ID> <NAME><xsl:value-of select="ENAME"/></NAME> </xsl:for-each> </xsl:template> </xsl:stylesheet>')).GetClobVal() AS Dept10Employees FROM Scott.dept d WHERE Deptno = 10 / XML doc 10 EMP empno ename ... sal deptno 10 10 XMLelement( .. XMLforest (.. ) ) XML doc XMLtransform( .. ) XSL 10 20 30 DEPT deptno dname loc emplist_t emp_t Helia / Martti Laiho 2005-2006

XQuery implementations by Oracle