SQL/XML for Developers
Lewis Cunningham Data Architect, JP Morgan Chase http://it.toolbox.com/blogs/oracle-guide http://databasewisdom.com/
ODTUG Kaleidoscope 2009 06/21/2009
Introduction • Oracle ACE Director
• Author • Data Architect for JP Morgan Chase
• Twitter: @oracle_ace • Blogger – An Expert's Guide to Oracle Technology • http://it.toolbox.com/blogs/oracle-guide
– The Database Geek • http://databasegeek.com
Developers 3
Agenda
What is XML?
What is the XMLType data type?
What is SQL/XML?
What should(n’t) I do with XML?
What is XML? 4
What is XML? 5
Semi-Structured
Markup “Language”
Hierarchical Not relational Not freeform
Tags Identify Data Lewis
Human AND machine readable
What is XML? 6
Elements
Elements are nodes
Root Element Child Elements
Some nodes contain elements Some nodes contain character data A node can contain both
Namespaces
What is XML? 7
Well Formed XML
Follows XML formatting rules
All open tags have closing tags All attribute values are enclosed in quotes
If a document is not well formed, it is not XML A well formed document may not be a VALID document
Valid XML
Conforms to a specific specification (DTD, XSD, RNG) A valid document will always be a well formed document
What is XML? 8
Element/Tag
Simple Example
Childnode1 Child to rootnode
Some Data Childnode1 is Sibling to Some more childnode2 data Closing Empty Tag Tag
What is XML? 9
Namespaces
A namespace prevents naming collisions
A namespace provides clarity
A namespace allows multiple documents to be combined into a single document
What is XML? 10
Simple Example
Namespace Identifier
Namespace Name
Namespace URI
Some Data Some more Namespace data Usage Default Usage
What is XML? 11
Describing XML
DTD – Document Type Description
XSD – XML Schema
Relax NG – REgular LAnguage for Xml Next Generation
What is XML? 12
DTD
A list of valid elements and attributes May be inline or external Original descriptive language Limited and mostly obsolete No data type definitions No support for Namespaces
DTD Example 13
]>
What is XML? 14
XSD
The XML Schema is the W3C replacement to DTDs XSD supports data types an namespaces XML Schemas are defined as XML Allows you to define ordering/number of elements Allows you to define mandatory elements XML Schemas are extensible
XSD Example 15
What is XML? 16
RELAX NG
XML and non-XML formats Simpler than XSD More like a speaking dialect than an XML dialect Supports data types and namespaces Not as robust as XSD (fewer data types, not as many rules (defaults and such) Not as widely utilized as XSD
RELAX NG (XML) Example 17
RELAX NG (Compact) Example 18
element patron { element name { text } & element id-num { text } & element book { (attribute isbn { text } | attribute title { text } ) }* }
What is the XMLType data type? 19
Object Data Type
Clob based
Well formed check
Validating and Non-Validating
May contain XML fragments
What is the XMLType data type? 20
XML Document data goes here XML Content (fragment) Lewisyes
What is the XMLType data type? 21
Declare a column as XML create table xml_tab ( id integer, data XMLType );
Declare a variable as XML DECLARE v_xml XMLType; BEGIN ……
What is SQL/XML? 22
SQL/ XML
What is SQL/XML? 23
SQL/XML (or SQL/X) is standards based
Combines XML and SQL
IBM, Oracle, Microsoft and Sybase all played a large part in defining SQL/XML Home on the web (but no longer maintained): www.sqlx.org
What is SQL/XML? 24
SQL/XML defines a set of mappings and a set of functions Based on XQuery and XPath Oracle implements the core functions of SQL/XML XQuery is also supported
What is SQL/XML?
SQL/X Functions
XMLParse XMLSerialize XMLSequence XMLTable XMLElement XMLForest XMLAgg XML Comment XMLConcat More
25
What is SQL/XML? 26
Sample data CREATE TABLE EMP ( LAST_NAME VARCHAR2(50), EMP_ID NUMBER NOT NULL, FIRST_NAME VARCHAR2(50), DEPT_ID NUMBER, SALARY NUMBER, CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID) )
What is SQL/XML? Sample sample data:
27
INSERT INTO EMP( LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY) VALUES ('Blow', 1, 'Joe', 1, 10000); Also INSERT..... ('Head', 2, 'Helmut', 1, 12000), ('Jack', 3, 'Noe', 1, 12000), ('Hard', 4, 'Blow', 2, 20000), ('First', 5, 'Hugo', 2, 21000), ('Spaem',6, 'Kingoof', 2, 20000), ('Ventura', 7, 'Ace', 3, 35000), ('Nickleby', 8, 'Nick', 3, 400000), ('Budd', 9, 'Billy', 4, 99000), ('Cleaver', 10, 'Wally', 4, 100000) ;
What is SQL/XML? - XML Parse 28
The XMLType constructor, or the SQL/XML function XMLParse, will convert text (or clobs) into XML vXMLVar XMLType; vXMLVar := XMLType(‘data’); vXMLVar := XMLParse(DOCUMENT ‘data’);
What is SQL/XML? XMLSerialize 29
XMLSerialize turns XML into text (you can also use XMLType.toString) vString := XMLSerialize( DOCUMENT v_xml AS VARCHAR2); vString := XMLSerialize( CONTENT v_xml AS CLOB);
What is SQL/XML? XMLElement 30
SELECT XMLElement(name main, last_name) from emp;
Blow Head Jack Hard First
What is SQL/XML? – XMLElementCont’d 31
SELECT XMLElement(name main, last_name), XMLElement(name main, first_name) FROM emp; Blow Head Jack Hard
| | | |
Joe Helmut Noe Blow
What is SQL/XML? - XMLForest 32
SELECT XMLForest(last_name, first_name) FROM emp; BlowJoe HeadHelmut
What is SQL/XML? – XMLForestCont’d 33
SELECT XMLElement(name main, XMLForest(last_name, first_name) ) FROM emp; Blow Joe Head Helmut
What is SQL/XML? – XMLAgg 34
SELECT XMLAgg( XMLForest(last_name, first_name) ) FROM emp; Blow Joe Head Helmut Jack Noe…
What is SQL/XML? – XMLAgg Cont'd 35
SELECT XMLElement(name main, XMLAgg(XMLForest(last_name, first_name) )) FROM emp; Blow Joe Head Helmut Jack Noe…
What is SQL/XML? – Concatenating Columns 36
SELECT XMLElement(name main, XMLForest(last_name || ',' || first_name AS fullname, salary) ) FROM emp; Blow,Joe 10000 Head,Helmut 12000
What is SQL/XML? – Concat and Attributes 37
SELECT XMLElement(name main, XMLElement(name fullname, XMLAttributes(dept_id), last_name || ',' || first_name ), XMLForest(salary) ) FROM emp;
Blow,Joe 10000
What is SQL/XML? XMLComment 38
SELECT XMLElement(name main, XMLComment('Comment goes here'), XMLForest(last_name, first_name)) FROM emp; Blow Joe
What is SQL/XML? - XMLConcat 39
SELECT XMLElement(name lastname, last_name), XMLElement(name firstname, first_name) FROM emp; Blow Joe Head Helmut
What is SQL/XML? – XMLConcat Cont'd 40
SELECT XMLConcat( XMLElement(name lastname, last_name), XMLElement(name firstname, first_name) ) FROM emp; BlowJoe HeadHelmut
What is SQL/XML? – XMLConcat Cont'd 41
SELECT XMLElement(name main, XMLConcat( XMLElement(name lastname, last_name), XMLElement(name firstname, first_name) ) ) FROM emp; BlowJoe HeadHelmut
What should(n’t) I do with XML? 42
XML
Whatshould(n’t)IdowithXML? 43
Oracle is a RELATIONAL database
Store your data relationally, unless
Your XML is read only Your XML is transient Your XML is fairly static Your XML is very small You have a discrete key external to the XML Preserved white space is critical
What should I do with XML? 44
Convert your XML to Relations by Shredding
Map your relational schema to the XML by its Schema or DTD Use XPath to extract columnar data
Use SQL/XML to recreate the original XML document
Whatshould(n’t)Idowith XML? Cont'd 45
Relational data is much easier to
Index Update Manipulate
XML data is better for
Use by some programming languages CMS systems Very unstructured data For reporting Web 2.0 (AJAX, SOAP, Etc)
Whatshould(n’t)Idowith XML? Cont'd 46
XML is great for
Public stored procedure interfaces
Web based processing
Publish the XML spec Include a version element or attribute Change parameters and let users adapt over time Many web apps support XML XML + XSLT = Web Happiness
Data interfaces
Platform independent Current parsers are fast Validation and versioning built-in Public Specifications
SQL/XML for Developers
Lewis Cunningham Data Architect, JP Morgan Chase http://it.toolbox.com/blogs/oracle-guide http://databasewisdom.com/
ODTUG Kaleidoscope 2009 06/21/2009