SQL/XML on Oracle

Converting tables to XML documents and vice versa on the Oracle RDBMS:

1. SQL/XML on Oracle
Kristian Torp
Department of Computer Science
Aalborg University
people.cs.aau.dk/˜torp
[email protected]
November 26, 2015
daisy.aau.dk
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 1 / 73

2. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 2 / 73

3. Learning Goals
Goals
Overview of SQL/XML
Extract relational information as XML
Introduction querying XML on PostgreSQL
Note
SQL/XML is part of the SQL standard
SQL/XML is being supported by the major DBMS vendors
It has nothing to do with SQLXML from Microsoft
Standard
Concepts are general
Code is sometimes DBMS specic
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 3 / 73

4. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 4 / 73

5. Overall Picture
The Main Idea
SQL/XML query<stuff></stuff>
Note
SQL/XML is an SQL extension
Added in 2003, extended in 2008 and 2011 versions of SQL
SQL/XML is bidirectional from tables to XML
Started as tables to XML
Extended with XML to tables
The major DBMS vendors do not agree on the SQL/XML syntax!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 5 / 73

6. XPath and XQuery vs. SQL/XML
XPath and XQuery
XPathandXQueryareXML
centric
SQL/XML
SQL/XMLisSQLcentric
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 6 / 73

7. XPath and XQuery vs. SQL/XML
XPath and XQuery
XPathandXQueryareXML
centric
SQL/XML
SQL/XMLisSQLcentric
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 6 / 73

8. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 7 / 73

9. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 8 / 73

10. Create Relational Schema
Example (Create the Table)
c r e a t e
i d
name
semester
descr
) ;
Example (Load the Data)
i n s e r t
( 4 , o r i e n t e d
i n s e r t
( 2 ,
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 9 / 73

11. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 10 / 73

12. Introduction: SQL/XML
Steps
Main purpose to map between XML and SQL
A wrapper layer
A standard dened by the ISO/ANSI SQL committee
Part 14 of of SQL 2003
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 11 / 73

13. Get XML Out, First Try
Example (Query One)
s e l e c t (
X M L A t t r i b u t e s( i d
XMLElement(
XMLElement(
XMLElement(
from
Note
It is aselectstatement!
The SQL/XMLpublishing functions
It is fairly easy to guess what it does!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 12 / 73

14. First Result
Example (Result Query One)
<course >
<name>OOP</ name>
<semester>3</ semester>
<desc>O b j e c to r i e n t e d </ desc>
</ course>
<course >
<name>DB</ name>
<semester>7</ semester>
<desc>Databases </ desc>
</ course>
Note
Not a
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 13 / 73

15. Get XML Out, Adding a Root Element
Example (Query Two)
s e l e c t
XMLAgg( XMLElement (
X M L A t t r i b u t e s ( i d
XMLElement (
XMLElement (
XMLElement (
from
Note
The XMLAgg another publishing function
XMLAgg is an aggregate function like min, max, and avg
XMLAgg has optionalorder clause
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 14 / 73

16. Second Result
Example (Result Query Two)
<c o u r s e c a t a l o g>
<course >
<name>OOP</ name>
<semester>3</ semester>
<desc>O b j e c to r i e n t e d </ desc>
</ course>
<course >
<name>DB</ name>
<semester>7</ semester>
<desc>Databases </ desc>
</ course>
</ c o u r s e c a t a l o g>
Note
Now the XML document has a root element
Still missing the
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 15 / 73

17. Get XML Out, Adding XML Denition
Example (Query Three)
s e l e c t (
(
XMLAgg( XMLElement (
X M L A t t r i b u t e s ( i d
XMLElement (
XMLElement (
XMLElement (
from
from
Note
It is now a
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 16 / 73

18. Third Result
Example (Result Query Three)
<? >
<c o u r s e c a t a l o g>
<course >
<name>OOP</ name>
<semester>3</ semester>
<desc>O b j e c to r i e n t e d </ desc>
</ course>
<course >
<name>DB</ name>
<semester>7</ semester>
<desc>Databases </ desc>
</ course>
</ c o u r s e c a t a l o g>
Note
Got the XML header
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 17 / 73

19. Get XML Out, Adding an XSLT
Example (Query Four)
s e l e c t
XMLConcat
(
XMLPI(NAME s t y l e s h e e t
h r e f
(
s e l e c t
XMLAgg( XMLElement (
X M L A t t r i b u t e s ( i d
XMLElement (
XMLElement (
XMLElement (
from
)
) ,
VERSION
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 18 / 73

20. Final Result
Example (Result Query Four)
<? >
<? x m ls t y l e s h e e t
h r e f = >
<c o u r s e c a t a l o g>
<course >
<name>OOP</ name>
<semester>3</ semester>
<desc>O b j e c to r i e n t e d </ desc>
</ course>
<course >
<name>DB</ name>
<semester>7</ semester>
<desc>Databases </ desc>
</ course>
</ c o u r s e c a t a l o g>
Note
A nice valid XML document!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 19 / 73

21. SQL/XML Publishing Functions
Overview
Function Description
XMLRoot Creates a root node
XMLElement Creates an XML element
XMLAttributesCreates attributes on elements
XMLAgg Aggregates XML fragments
XMLForest Creates a forest of elements
XMLConcat Appends elements
XMLPI Create processing instructions
XMLComment Create comments
Note
These are all standard publishing functions
The uses/misuse of abbreviations
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 20 / 73

22. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 21 / 73

23. Quick and Dirty
Example (Shortest Possible)
s e l e c t ( id , name , semester , descr )
from
Example (The Result)
<ID>4</>NAME>OOP</NAME>SEMESTER>3</SEMESTER>DESCR>. . .</DESCR>
<ID>2</>NAME>DB</NAME>SEMESTER>7</SEMESTER>DESCR>. . .</DESCR>
Note
Two rows are returned
Element names are in upper case (Oracle/SQL default)
No container element for each row
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 22 / 73

24. Quick and Dirty
Example (Shortest Possible)
s e l e c t )
from
Note
Not allowed on Oracle
Example (Shortest Possible)
s e l e c t .)
from
Note
Not allowed either!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 23 / 73

25. Quick and Dirty
Example (Shortest Possible)
s e l e c t )
from
Note
Not allowed on Oracle
Example (Shortest Possible)
s e l e c t .)
from
Note
Not allowed either!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 23 / 73

26. Rename Columns
Example (Rename Columns)
s e l e c t
name name
semester
descr
from
Example (The Result)
<i d>4</>coursename>OOP</ coursename>semester>3</ semester>. . .
<i d>2</>coursename>DB</ coursename>semester>7</ semester>. . .
Note
Must use double quote ” and not single quote
As is optional if scalar value
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 24 / 73

27. Added Outer Element
Example (A Tag for Each Row)
s e l e c t (
”
XMLForest ( ID
name name
semester
descr
from
Example (The Result)
<course>i d>4</>coursename>OOP</ coursename>. . .</ >
<course>i d>2</>coursename>DB</ coursename>. . .</ >
Note
Still two rows being returned
The wrapping of each row
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 25 / 73

28. Quick and Dirty, cont
Example (With Projection)
s e l e c t ( id , name , semester )
from
Example (The Result)
<ID>4</>NAME>OOP</NAME>SEMESTER>3</SEMESTER>
<ID>2</>NAME>DB</NAME>SEMESTER>7</SEMESTER>
Example (With Projection and Renaming)
s e l e c t ( i d
from
Example (The Result)
<c i d>4</ c i d>coursename>OOP</ coursename>
<c i d>2</ c i d>coursename>DB</ coursename>
Note
Filtering (selection) is possible in the where clause
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 26 / 73

29. Quick and Dirty, cont
Example (With Projection)
s e l e c t ( id , name , semester )
from
Example (The Result)
<ID>4</>NAME>OOP</NAME>SEMESTER>3</SEMESTER>
<ID>2</>NAME>DB</NAME>SEMESTER>7</SEMESTER>
Example (With Projection and Renaming)
s e l e c t ( i d
from
Example (The Result)
<c i d>4</ c i d>coursename>OOP</ coursename>
<c i d>2</ c i d>coursename>DB</ coursename>
Note
Filtering (selection) is possible in the where clause
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 26 / 73

30. Quick and Dirty, cont
Example (With Element per Row)
s e l e c t
XMLForest( i d
from
Example (The Result)
<course>c i d>4</ c i d>cname>OOP</ cname>/ >
<course>c i d>2</ c i d>cname>DB</ cname>/ >
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 27 / 73

31. Quick and Dirty, cont
Example (With Root Element)
s e l e c t
XMLAgg(
XMLElement (
XMLForest ( i d
from
Example (The Result)
<c o u r s e c a t a l o g>
<course>c i d>4</ c i d>cname>OOP</ cname>/ course>
<course>c i d>2</ c i d>cname>DB</ cname>/ course>
</ >
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 28 / 73

32. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 29 / 73

33. XMLTypeViews
Example (Create an XML view on an existing table)
c r e a t e xml
(
( o b j e c tv a l u e ,
as
XMLAgg( XMLElement (
X M L A t t r i b u t e s ( i d
XMLElement (
XMLElement (
XMLElement (
from
Note
A create view statement
Use the SQL/XML publishing functions
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 30 / 73

34. XQuery on Oracle
Example (Get info on the OOP course)
XQUERY
f o r xml
where
r e t u r n
Example (Result)
columnvalue
<course >
<name>OOP</ name>
<semester>3</ semester>
<desc>Objecto r i e n t e d </ desc>
</ course>
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 31 / 73

35. XQuery on Oracle
Example (Count the number of courses)
XQUERY
f o r xml
l e t
r e t u r n
Example (Result)
columnvalue
2
Note
Remember '/' to execute from command line
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 32 / 73

36. Summary: Tables to XML
Summary
SQL/XML is an ISO/ANSI standard not part of W3C
Oracle has made vendor specic extensions to SQL/XML
SQL/XML is good for mapping SQL to XML
It is often very convenient to be able to do the mapping in plain SQL
Start building SQL/XML queries from the inside and out
Alternatives to SQL/XML
Store XML in native format in the database
DBMS vendor specic extension, e.g. DBMSXMLGEN
Do SQL to XML in programming language
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 33 / 73

37. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 34 / 73

38. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 35 / 73

39. Introduction
Example (The Best of Both Worlds)
IdTxt
1<course='22' ><name>OOP</name></course>
2<course='11' ><name>DB</name></course>
3<course='33' ><name>SQL</name></course>
SQL and XML
Store XML as any other data type in a table
Retain possiblity to use SQL for querying data
Be able to query the XML data using XPath and XQuery
Conversion of XML to SQL
Retain pure SQL view on all data
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 36 / 73

40. ThecoursecatTable
Example
iddsc exercises
11
<course c i d = >
<name>Database</ name>
</ course>
22
<course c i d = >
<name>OOP</ name>
</ course>
<e x e r c i s e s>
<e x e r c i s e e i d = >
<desc>What </ desc>
<answer>I t </ answer>
</ e x e r c i s e>
</ e x e r c i s e s>
23
<course c i d = >
<name>SQL</ name>
</ course>
<e x e r c i s e s>
<e x e r c i s e e i d = >
<desc>What </ desc>
<answer>I t </ answer>
</ e x e r c i s e>
<e x e r c i s e e i d = >
<desc>What </ desc>
<answer>I t </ answer>
</ e x e r c i s e>
</ e x e r c i s e s>
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 37 / 73

41. Create Relational Schema
Example (Create the Table)
c r e a t e
i d
dsc
e x e r c i s e s
) ;
Example (Load the Data)
i n s e r t
22 ,
'<course >
<name>OOP</ >
</ >'
'<e x e r c i s e s>
<e x e r c i s e >
<desc>What </ >
<answer>I t </ >
</ >
</ >'
) ;
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 38 / 73

42. Say Hello
Example (Hello, World!)
s e l e c t (
l e t
r e t u r n
from
Example (Result)
o
Hello, World!
Note
The XMLQuery function
The XQuery in a string
Thereturning is required
The alias (o) is optional (otherwise ugly column name)
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 39 / 73

43. Simple XPath
Example (Use SQL and XPath)
s e l e c t (
passing
r e t u r n i n g
from
Example (Result)
ID RES
11
22(XMLTYPE)
23(XMLTYPE)
Note
Passing by value exercises
Return value is of the XMLType data type
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 40 / 73

44. Extracting Values
Example (extractvalue Function)
s e l e c t
e x t r a c t v a l u e ( dsc , name
from
Example (Result)
IDCOURSENAME
11 Database
22 OOP
23 SQL
Note
The extracevalue function is Oracle specic
//course/namewill do the same
//coursewill fail (more than one node)!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 41 / 73

45. Extracting Multiple Values
Example (XML to SQL)
s e l e c t
e x t r a c t v a l u e ( dsc , i d ,
e x t r a c t v a l u e ( dsc , name
from
Example (Result)
IDCOURSEIDCOURSENAME
11 11 Database
22 22 OOP
23 33 SQL
Note
Attributes and elements extract in the same manner
Now have a pure relational access to data
Can lter onCOURSEIDin a where clause
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 42 / 73

46. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 43 / 73

47. Extracting Content Directly from XML
Example (XMLQuery Function)
s e l e c t
xmlquery(
f nROW/
r e t u r n
from
Example (Result)
XMLQUERY(: : :)
<course cid=”11” ><name>Database</name>...</course>
Note
All in upper case
PUBLIC if table is available to current user
ROW is required in the XPath
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 44 / 73

48. From SQL Directly
Example (XQUERY SQLPlus Keyword)
XQUERY
f o rROW/
where
r e t u r n
Example (Result)
COLUMNVALUE
<exercises><exercise eid=”1” ><desc>What? </desc>...
Note
XQUERY not XMLQuery
The collection is the fn:collection method
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 45 / 73

49. Summary: XML Data Type
Summary
XMLQuery is part of the SQL standard
Allows full XQuery
In princip XQuery on XML le in Oracle!
Oracle as a pure XQuery engine
Supported by DB2 and Oracle
Data type other name (xml) on SQL Server
Oracle made extensions to support SQLPlus
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 46 / 73

50. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 47 / 73

51. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 48 / 73

52. The Overall Idea
The Flow
XMLTable query<stuff></stuff>
Note
XMLTable is a part of SQL/XML
XMLTable returns a rowset
It is a
Overview SQL/XML Functions
SQL ClauseSQL/XML Function
select XMLQuery
from XMLTable
where XMLExists
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 49 / 73

53. ThecoursecatTable
Example
iddsc exercises
11
<course c i d = >
<name>Database</ name>
</ course>
22
<course c i d = >
<name>OOP</ name>
</ course>
<e x e r c i s e s>
<e x e r c i s e e i d = >
<desc>What </ desc>
<answer>I t </ answer>
</ e x e r c i s e>
</ e x e r c i s e s>
23
<course c i d = >
<name>SQL</ name>
</ course>
<e x e r c i s e s>
<e x e r c i s e e i d = >
<desc>What </ desc>
<answer>I t </ answer>
</ e x e r c i s e>
<e x e r c i s e e i d = >
<desc>What </ desc>
<answer>I t </ answer>
</ e x e r c i s e>
</ e x e r c i s e s>
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 50 / 73

54. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 51 / 73

55. From XML to SQL
Example (Get the Course Name)
s e l e c t name
from
XMLTable (
columns name
Example (Result)
IDCOURSENAME
11 Database
22 OOP
23 SQL
Note
Thepassing,columns, andpathkeywords
/courseis the, here XPath full XQuery supported
nameis the, here XPath full XQuery supported
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 52 / 73

56. More Advanced XPath
Example (Multiple XML Columns)
s e l e c t name , n oe x e r c i s e s
from
XMLTable (
columns name
XMLTable (
columns e x e r c i s e s
Example (Result)
IDCOURSENAME NOEXERCISES
22 OOP 1
23 SQL 2
Note
Multiple XMLTable calls
No join condition, recall implicitely joined
Advanced path expressionscount(exercise)
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 53 / 73

57. Using in the Where Clause
Example (Find where IDs are not Matching)
s e l e c t i d , coursename
from
XMLTable (
columns i d
coursename
where i d
Example (Result)
IDCOURSEIDCOURSENAME
23 33 SQL
Note
Attribute used in column pattern:@cid
Element used in column pattern:name
Comparison in where clause
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 54 / 73

58. Ordinality and Default Value
Example (Find where IDs are not Matching)
s e l e c t i d , a u t h o r
from
XMLTable (
passing
columns
num
e x e r c i s ei d
a u t h o r
Example (Result)
IDNUMEXERCISEIDAUTHOR
22 1 1 Ib
23 1 1 Ib
23 2 11 Ib
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 55 / 73

59. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 56 / 73

60. Information on Course ID and Name
Example
s e l e c t name
from
XMLTable (
passing
columns
coursename
Example (Result)
idcoursename
11Database
22OOP
23SQL
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 57 / 73

61. Information on Course ID and Name
Example
s e l e c t name
from
XMLTable (
passing
columns
coursename
Note
Displays relation data along side XML data!
Implicit join
Columns explicitely named and typed
XMLTable alias (x) is optional
The XPath expression in columns can only return one item (per row)!
/coursecan be simplied to(/)
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 58 / 73

62. Information on Course ID, Name, and Exercises
Example
s e l e c t name , e x e r c i s ei d
from
XMLTable (
passing t a b l ename name
columns
coursename
XMLTable (
passing o n l y
columns
e x e r c i s ei d
Example (Result)
idcoursenameexerciseid
22OOP 1
23SQL 1
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 59 / 73

63. Information on Course ID, Name, and Exercises
Example
s e l e c t name , e x e r c i s ei d
from
XMLTable (
passing t a b l ename name
columns
coursename
XMLTable (
passing o n l y
columns
e x e r c i s ei d
Note
Multiple XMLTable functions
Both elements and attributes are converted to tabular
The use of absolute and relative paths
Must select rst exercise otherwise error
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 60 / 73

64. Information on Course ID, Name, and Exercises, cont
Example
s e l e c t name , e x e r c i s ei d , descr , answer
from
XMLTable (
passing
columns
coursename
XMLTable (
passing
columns
e x e r c i s ei d
descr
answer
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 61 / 73

65. Information on Course ID, Name, and Exercises, cont
Example (Result)
idcoursenameexerciseiddescr answer
22OOP 1 What is OOP? It ...
23SQL 1 What is SQL? It ...
23SQL 11 What is a query?It ...
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 62 / 73

66. Information on Course ID, Name, and Exercises, cont
Example
s e l e c t name , e x e r c i s ei d , descr , answer
from
XMLTable (
passing
columns
coursename
XMLTable (
passing
columns
e x e r c i s ei d
descr
answer
Note
Change the XPath for exercises to a lower level
Missing information on one course
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 63 / 73

67. Information on Course ID, Name, and Exercises, cont
Example
s e l e c t name , e x e r c i s ei d , descr , answer
from
XMLTable (
passing
columns
coursename
l e f t
XMLTable (
passing
columns
e x e r c i s ei d
descr
answer
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 64 / 73

68. Information on Course ID, Name, and Exercises, cont
Example (Result)
idcoursenameexerciseiddescr answer
11Database
22OOP 1 What is OOP? It ...
23SQL 1 What is SQL? It ...
23SQL 11 What is a query?It ...
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 65 / 73

69. Information on Course ID, Name, and Exercises, cont
Example
s e l e c t name , e x e r c i s ei d , descr , answer
from
XMLTable (
passing
columns
coursename
l e f t
XMLTable (
passing
columns
e x e r c i s ei d
descr
answer
Note
The left outer join between the two XMLTable function calls
The on clauseon
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 66 / 73

70. Filtering Based on XML Content
Example
s e l e c t
from
passing
columns
e x e r c i s ei d
descr
answer
Example (Result)
exerciseiddescr answer
1 What is OOP? It ...
1 What is SQL? It ...
11 What is a query?It ...
Note
Too much information, i.e., no ltering
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 67 / 73

71. Filtering Based on XML Content
Example
s e l e c t
from
passing
columns
e x e r c i s ei d
descr
answer
where
Example (Result)
exerciseiddescr answer
1 What is SQL? It ...
11 What is a query?It ...
Note
The XMLExist for ltering in XML content
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 68 / 73

72. Summary: XML to Tables
Summary
The core function is XMLTable
Supported on most DBMS
Explicit naming
Cannot guess SQL column names element/attributes
Explicit typing
Cannot guess SQL data types from XML document
Result of XMLTable can be joined like other tables!
Literature
XMLTABLE by example, Part 1
Walk-through on how to handle multiple rows (ve diff. ways)
www.ibm.com/developerworks/data/library/techarticle/
dm-0708nicola/
XMLTABLE by example, Part 2
On scredding large XML documents plus insert relational from XML
www.ibm.com/developerworks/data/library/techarticle/
dm-0709nicola/
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 69 / 73

73. Outline
1
Introduction
2
From Tables to XML
Introduction
SQL/XML Publishing Functions
Quick and Dirty Publishing Functions
XML View on Relational Data
3
The XML Data Type
Introduction
oradb: Protocol
4
From XML to Tables
Introduction
A Simple Example
A Longer Example
5
Summary
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 70 / 73

74. SQL/XML vs. XPath/XQuery
SQL/XML
SQL centric
Small extension to SQL
nullwell understood
No implicit order
Bad support hierarchies
XPath/XQuery
XML centric
New programming languages
Handling ofnullbe aware!
Ordering (a sequence)
Excellent support hierarchies
Note
SQL/XML and XQuery serve different purposes
Are not competing technologies!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 71 / 73

75. SQL/XML vs. XPath/XQuery
SQL/XML
SQL centric
Small extension to SQL
nullwell understood
No implicit order
Bad support hierarchies
XPath/XQuery
XML centric
New programming languages
Handling ofnullbe aware!
Ordering (a sequence)
Excellent support hierarchies
Note
SQL/XML and XQuery serve different purposes
Are not competing technologies!
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 71 / 73

76. Summary
Main Points
SQL/XML is an ISO/ANSI standard
SQL/XML example of wrapper technology
Make table look like XML documents
Well integrated into PostgreSQL, Oracle, and other DBMSs
XML data type is much smarter than a CLOB
Must look elsewhere for XML to tables
Standards
SQL/XML publishing functions added in SQL/XML:2003
XMLExists, XMLQuery, and XMLTable added in SQL/XML:2006
SQL/XML standardization faster than overall SQL standardization
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 72 / 73

77. Additional Information
Web Sites
Good SQL/XML tutorial
www.stylusstudio.com/sqlxml_tutorial.html
Advancements in SQL/XMLwww.sigmod.org/sigmod/record/
issues/0409/11.JimMelton.pdf
Jim Melton in SIGMOD Record
PostgreSQL and XMLhttp:
//www.slideshare.net/petereisentraut/postgresql-and-xml
Slightly outdated, but good
Oracle's XML Technology Center
www.oracle.com/technology/tech/xml/index.html
Get off to a fast start with DB2 9 pureXML
http://www.ibm.com/developerworks/data/library/
techarticle/dm-0603saracco2/
Part of series of papers on XML support on DB2
Kristian Torp (Aalborg University) SQL/XML on Oracle November 26, 2015 73 / 73