Bridging Relational Technology and XML

1
Bridging Relational Technologyand XML

• Jayavel Shanmugasundaram

University of Wisconsin IBM Almaden Research
Center
2
Business to Business Interactions
Cars R Us
Tires R Us
3
Shift in Application Developers Conceptual Data
Model
XML
4
Are XML Database Systemsthe Answer?
Cars R Us
Tires R Us
Purchasing Application
XML DatabaseSystem
5
Why use Relational Database Systems?

• Highly reliable, scalable, optimized for
  performance, advanced functionality
• Result of 30 years of Research Development
• XML database systems are not industrial
  strength and not expected to be in the
  foreseeable future
• Existing data and applications
• XML applications have to inter-operate with
  existing relational data and applications
• Not enough incentive to move all existing
  business applications to XML database systems
• Remember object-oriented database systems?

6
A Solution
Cars R Us
Tires R Us
eXtensible Markup Language (XML)
Internet
Order Fulfillment Application
Purchasing Application
Relational DatabaseSystem
Relational DatabaseSystem
7
XML Translation Layer(Contributions)

• Store and query XML documents
• Harnesses relational database technology for this
  purpose VLDB99
• Publish existing relational data as XML documents
• Materialize relational data as XML documents
  VLDB00
• View and query relational data as XML documents
  VLDB01

8
Bridging Relational Technologyand XML
XML
9
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

10
Relational Data
PurchaseOrder
Id
Customer
Year
Month
Day
200I
Cars R Us
10
June
1999
300I
Bikes R Us
null
July
1999
Item
Payment
Name
Cost
Quantity
Installment
Percentage
Pid
Pid
1
200I
200I
Firestone Tire
50
2000.00
40
200I
300I
Schwinn Tire
100
60
2
2500.00
300I
300I
Trek Tire
20
100
1
400.00
200I
Goodyear Tire
200
8000.00
11
SQL Query
Find all the items bought by Cars R Us in the
year 1999
Select it.name From PurchaseOrder po, Item
it Where po.customer Cars R Us and
po.year 1999 and po.id it.pid
Predicates
Join
12
XML Document
ltPurchaseOrder id200I customerCars R Usgt
ltDategt ltDaygt 10 lt/Daygt
ltMonthgt June lt/Monthgt ltYeargt 1999
lt/Yeargt lt/Dategt ltItem nameFirestone
Tire cost2000.00gt ltQuantitygt 50
lt/Quantitygt lt/Itemgt ltItem
nameGoodyear Tire cost8000.00gt
ltQuantitygt 200 lt/Quantitygt lt/Itemgt
ltPaymentgt 40 lt/Paymentgt ltPaymentgt 60
lt/Paymentgt lt/PurchaseOrdergt
13
XML Document
ltPurchaseOrder id200I customerCars R Usgt
ltDategt ltDaygt 10 lt/Daygt
ltMonthgt June lt/Monthgt ltYeargt 1999
lt/Yeargt lt/Dategt ltItem nameFirestone
Tire cost2000.00gt ltQuantitygt 50
lt/Quantitygt lt/Itemgt ltItem
nameGoodyear Tire cost8000.00gt
ltQuantitygt 200 lt/Quantitygt lt/Itemgt
ltPaymentgt 40 lt/Paymentgt ltPaymentgt 60
lt/Paymentgt lt/PurchaseOrdergt
Self-describing tags
Nested structure
14
XML Document
ltPurchaseOrder id200I customerCars R Usgt
ltDategt ltDaygt 10 lt/Daygt
ltMonthgt June lt/Monthgt ltYeargt 1999
lt/Yeargt lt/Dategt ltItem nameFirestone
Tire cost2000.00gt ltQuantitygt 50
lt/Quantitygt lt/Itemgt ltItem
nameGoodyear Tire cost8000.00gt
ltQuantitygt 200 lt/Quantitygt lt/Itemgt
ltPaymentgt 40 lt/Paymentgt ltPaymentgt 60
lt/Paymentgt lt/PurchaseOrdergt
Self-describing tags
Nested structure
Nested sets
15
XML Document
ltPurchaseOrder id200I customerCars R Usgt
ltDategt ltDaygt 10 lt/Daygt
ltMonthgt June lt/Monthgt ltYeargt 1999
lt/Yeargt lt/Dategt ltItem nameFirestone
Tire cost2000.00gt ltQuantitygt 50
lt/Quantitygt lt/Itemgt ltItem
nameGoodyear Tire cost8000.00gt
ltQuantitygt 200 lt/Quantitygt lt/Itemgt
ltPaymentgt 40 lt/Paymentgt ltPaymentgt 60
lt/Paymentgt lt/PurchaseOrdergt
Self-describing tags
Nested structure
Nested sets
Order
16
XML Schema
ltPurchaseOrder idinteger customerstringgt
Date (Item) (Payment)lt/PurchaseOrdergt
PurchaseOrder
Date
ltDategt Day? Month Yearlt/Dategt
Day
ltDaygt integer lt/Daygt
Month
ltMonthgt string lt/Monthgt
Year
ltYeargt integer lt/Yeargt
Item
ltItem namestring costfloatgt
Quantitylt/Itemgt
and so on
17
XML Schema (contd.)
ltPurchaseOrder idinteger customerstringgt
Date? (Item Payment)lt/PurchaseOrdergt
PurchaseOrder
ltPurchaseOrder idinteger customerstringgt
(Date Payment) (Item (Item Item)
Payment)lt/PurchaseOrdergt
PurchaseOrder
ltPurchaseOrder idinteger customerstringgt
Date Item (PurchaseOrder) Paymentlt/PurchaseO
rdergt
PurchaseOrder
18
XML Query
Find all the items bought by Cars R Us in 1999
For po in /PurchaseOrderWhere
po/_at_customer Cars R Us and po/date/year
1999 Return po/Item
19
XML Query (contd.)
//Item
//Item5
//Item Before //Payment
20
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

21
Naïve Approach
PurchaseOrder
Id (200I)
Customer (Cars R Us)
Payment (40)
Date
Item
Item

Day (10)
Month (June)
Year (1999)
Element Node
Attribute Node
22
Naïve Approach (Contd.)
Graph
Id
Name
ParentId
Type
Value
Ordinal
0
PurchaseOrder
null
Element
null
null
1
Attribute
Id
200I
0
0
2
Attribute
Customer
Cars R Us
1
0
3
Element
Date
null
2
0
4
Element
Day
10
0
3
5
Element
Month
June
1
3
6
Element
Year
1999
2
3






Problem 1 Many joins for queries (one per hop)
eg. PurchaseOrder/Date/Year
Problem 2 Data types
23
Storing and Querying XML DocumentsShanmugasundar
am et al., VLDB99
XML Translation Layer
Relational Database System
24
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Relational Schema Design and XML Storage
• Query Mapping and Result Construction
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

25
XML Schema
ltPurchaseOrder idinteger customerstringgt
(Date (Payment)) (Item (Item Item)
Payment)lt/PurchaseOrdergt
PurchaseOrder
26
Desired Properties of Generated Relational Schema
R

• All XML documents conforming to XML schema should
  be mappable to tuples in R
• All queries over XML documents should be
  mappable to SQL queries over R
• Not Required Ability to re-generate XML schema
  from R

27
Simplifying XML Schemas

• XML schemas can be simplified for translation
  purposes

ltPurchaseOrder idinteger customerstringgt
(Date (Payment)) (Item (Item Item)
Payment)lt/PurchaseOrdergt
PurchaseOrder
ltPurchaseOrder idinteger customerstringgt
Date? (Item) (Payment)lt/PurchaseOrdergt
PurchaseOrder

• All without undermining storage and query
  functionality!

28
Why is Simplification Possible?

• Structure in XML schemas can be captured
• Partly in relational schema
• Partly as data values

ltPurchaseOrder idinteger customerstringgt
Date? (Item) (Payment)lt/PurchaseOrdergt
PurchaseOrder

• Order field to capture order among siblings
• Sufficient to answer ordered XML queries
• PurchaseOrder/Item5
• PurchaseOrder/Item AFTER PurchaseOrder/Payment
• Sufficient to reconstruct XML document

29
Simplification Desiderata

• Simplify structure, but preserve differences that
  matter in relational model
• Single occurrence (attribute)
• Zero or one occurrences (nullable attribute)
• Zero or more occurrences (relation)

ltPurchaseOrder idinteger customerstringgt
(Date (Payment)) (Item (Item Item)
Payment)lt/PurchaseOrdergt
PurchaseOrder
ltPurchaseOrder idinteger customerstringgt
Date? (Item) (Payment)lt/PurchaseOrdergt
PurchaseOrder
30
Translation Normal Form

• An XML schema production is either of the form

ltP attr1type1 attrmtypemgt a1 ap
ap1? aq? aq1 ar lt/Pgt
P
where ai ? aj

• or of the form

ltP attr1type1 attrmtypemgt
type lt/Pgt
P
31
Example Simplification Rules
(e1 e2)
e1? e2?
(Date (Payment)) (Item (Item Item) Payment)
Date? (Item)? (Item (Item Item) Payment)
e?
e
Date? (Payment)? (Item (Item Item) Payment)
Date? (Item) (Item (Item Item) Payment)
32
Simplified XML Schema
ltPurchaseOrder idinteger customerstringgt
Date (Item) (Payment)lt/PurchaseOrdergt
PurchaseOrder
Date
ltDategt Day? Month Yearlt/Dategt
Day
ltDaygt integer lt/Daygt
Month
ltMonthgt string lt/Monthgt
Year
ltYeargt integer lt/Yeargt
Item
ltItem namestring costfloatgt
Quantitylt/Itemgt
and so on
33
Relational Schema Generation
PurchaseOrder (id, customer)
1


Date
Item (name, cost)
Payment
?
1
1
1
Day
Month
Year
Quantity
Satisfy Fourth normal form
Minimize Number of joins for simple path
expressions (of form /a/b/c)
34
Generated Relational Schemaand Shredded XML
Document
PurchaseOrder
Id
Customer
Year
Month
Day
200I
Cars R Us
10
June
1999
Item
Payment
Name
Order
Value
Order
Cost
Quantity
Pid
Pid
2
200I
200I
Firestone Tire
2000.00
40
50
1
200I
200I
Goodyear Tire
200
8000.00
3
60
4
35
Recursive XML Schema
PurchaseOrder (id, customer)


Item (name)
1
Quantity
36
Relational Schema Generation and XML Document
Shredding (Completeness)

• Any XML Schema X can be mapped to a relational
  schema R, and
• Any XML document XD conforming to X can be
  converted to tuples in R
• Further, XD can be recovered from the tuples in R

37
Relational Schema Generation and XML Document
Shredding (Optimality)

• Minimizes number of joins
• For simple path expressions
• Given fourth normal form
• For more complex path expressions
• Trade-off between sharing and inlining
• Proposed shared and hybrid approaches
• Evaluated with 37 real DTDs

38
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Relational Schema Design and XML Storage
• Query Mapping and Result Construction
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

39
XML Query
Find all the items bought by Cars R Us in 1999
For po in /PurchaseOrderWhere
po/_at_customer Cars R Us and po/date/year
1999 Return po/Item
40
Path Expression Automata(Moore Machines)
PurchaseOrder
/PurchaseOrder/Item
Item

Item
//Item
41
XML Schema Automaton(Mealy Machine)
PurchaseOrder (id, customer)
Date
Item (name, cost)
Payment
Day
Month
Year
Quantity
42
Intersected Automaton
PurchaseOrder (customer)
Cars R Us
Date
Item (name, cost)
Year
Quantity
1999
43
Generated SQL Query
Select i.name, i.cost, i.quantity From
PurchaseOrder p, Item i Where p.customer Cars
R Us and p.year 1999 and
p.id i.pid
Predicates
Join condition
44
Recursive XML Query
PurchaseOrder (id, customer)
Find all items (directly or indirectly)under a
Cars R Us purchase order
Item (name)
For po in /PurchaseOrderWhere
po/_at_customer Cars R Us Return po//Item
Quantity
45
Recursive Automata Intersection
PurchaseOrder (customer)
PurchaseOrder (id, customer)
Cars R Us
Item (name)
Quantity
46
Recursive SQL Generation
ResultItems (id, name, quantity) as (
PurchaseOrder (customer)
Select it.id, it.name, it.quantityFrom
PurchaseOrder po, Item itWhere po.customer
Cars R Us and po.id it.pid
Cars R Us
47
SQL Generation for Path Expressions (Completeness)

• (Almost) all path expressions can be translated
  to SQL
• SQL does not support
• Nested recursion
• Meta-data querying
• Meta-data query capability provided in the XML
  translation layer

48
Constructing XML Results
ltitem name Firestone Tire cost2000.00gt
ltquantitygt 50 lt/quantitygt lt/itemgt ltitem
nameGoodyear Tire cost8000.00gt
ltquantitygt 200 lt/quantitygt lt/itemgt
(Firestone Tire, 2000.00, 50) (Goodyear
Tire, 8000.00, 200)
49
Complex XML Construction
PurchaseOrder (id, customer)
Cars R Us
Date
Item (name, cost)
Payment
Day
Month
Year
Quantity
1999
50
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

51
Relational Data
PurchaseOrder
Id
Customer
Year
Month
Day
200I
Cars R Us
10
June
1999
Item
Payment
Name
Cost
Quantity
Installment
Percentage
Pid
Pid
1
200I
200I
Firestone Tire
50
2000.00
40
200I
200I
Goodyear Tire
200
8000.00
60
2
52
XML Document
ltPurchaseOrder id200I customerCars R Usgt
ltDategt ltDaygt 10 lt/Daygt
ltMonthgt June lt/Monthgt ltYeargt 1999
lt/Yeargt lt/Dategt ltItem nameFirestone
Tire cost2000.00gt ltQuantitygt 50
lt/Quantitygt lt/Itemgt ltItem
nameGoodyear Tire cost8000.00gt
ltQuantitygt 200 lt/Quantitygt lt/Itemgt
ltPaymentgt 40 lt/Paymentgt ltPaymentgt 60
lt/Paymentgt lt/PurchaseOrdergt
53
Naïve Approach

• Issue many SQL queries that mirror the structure
  of the XML document to be constructed
• Tag nested structures as they are produced

(200I, Cars R Us, 10, June, 1999)
DBMS Engine
PurchaseOrder
Item
(40) (60)
(Firestone Tire, 2000.00, 50) (Goodyear Tire,
8000.00, 200)
Payment
Problem 1 Too many SQL queries
Problem 2 Fixed (nested loop) join strategy
54
Relations to XML IssuesShanmugasundaram et
al., VLDB00

• Two main differences
• Ordered nested structures
• Self-describing tags
• Space of alternatives

Late Tagging
Early Tagging
Early Structuring
Late Structuring
55
Naïve (Stored Procedure) Approach
Early Tagging, Early Structuring, Outside Engine

• Issue queries for sub-structures and tag them
• Could be a Stored Procedure to maximize
  performance

(200I, Cars R Us, 10, June, 1999)
DBMS Engine
PurchaseOrder
Item
(40) (60)
(Firestone Tire, 2000.00, 50) (Goodyear Tire,
8000.00, 200)
Payment
Problem 1 Too many SQL queries
Problem 2 Fixed (nested loop) join strategy
56
CLOB Approach
Early Tagging, Early Structuring, Inside Engine

• Push down computation inside engine
• Use extensibility features of SQL
• Scalar functions for tagging
• Aggregate functions for creating nested
  structures
• Character Large Objects (CLOBs) to hold
  intermediate tagged results

Problem CLOBs during query execution
(storage, size estimation, copying)
57
Late Tagging, Late Structuring

• XML document content produced without tags or
  structure (in arbitrary order)
• Tagger adds tags and structure as final step

Result XML Document
Tagging
Unstructured content
Relational QueryProcessing
58
Outer Union Approach
Late Tagging, Late Structuring
(200I, null , null, null , null ,
null , null , null, 1 ,
40, 2) (200I, Cars R Us, 10 , June, 1999,
null , null , null, null,
null, 0) (200I, null , null, null ,
null , null , null ,
null, 2 , 60, 2) (200I, null ,
null, null , null , Firestone Tire , 2000.00,
50 , null, null, 1) (200I, null ,
null, null , null , Goodyear Tire, 8000.00,
200, null, null, 1)
(200I, 2, 60) (200I, 1, 40)
(200I, Firestone Tire, 2000.00, 50) (200I,
Goodyear Tire, 8000.00, 200)
(200I, Cars R Us, 10, June, 1999)
Problem Wide tuples (having many columns)
59
Hash-based Tagger
Late Tagging, Late Structuring

• Outer union results not structured early
• In arbitrary order
• Tagger has to enforce nesting order during
  tagging
• Essentially a nested group-by operation
• Hash-based approach
• Hash on ancestor ids
• Group siblings together and tag them
• Inside/Outside engine tagger

Problem Requires memory for entire document
60
Late Tagging, Early Structuring

• Structured XML document content produced
• In document order
• Sorted Outer Union approach
• Tagger just adds tags
• In constant space
• Inside/outside engine

Result XML Document
Tagging
Structured content
Relational QueryProcessing
61
Sorted Outer Union Approach
Late Tagging, Late Structuring
(200I, null , null, null , null ,
null , null , null, 1 ,
40, 2) (200I, Cars R Us, 10 , June, 1999,
null , null , null, null,
null, 0) (200I, null , null, null ,
null , null , null ,
null, 2 , 60, 2) (200I, null ,
null, null , null , Firestone Tire , 2000.00,
50 , null, null, 1) (200I, null ,
null, null , null , Goodyear Tire, 8000.00,
200, null, null, 1)
(200I, 2, 60) (200I, 1, 40)
(200I, Firestone Tire, 2000.00, 50) (200I,
Goodyear Tire, 8000.00, 200)
(200I, Cars R Us, 10, June, 1999)
62
Sorted Outer Union Approach
Late Tagging, Early Structuring
(200I, Cars R Us, 10 , June, 1999,
null , null , null, null, null,
0) (200I, null , null, null , null
, Firestone Tire , 2000.00, 50 , null, null,
1) (200I, null , null, null , null
, Goodyear Tire, 8000.00, 200, null, null,
1) (200I, null , null, null , null
, null , null , null,
1 , 40, 2) (200I, null , null, null
, null , null , null ,
null, 2 , 60, 2)
(200I, 2, 60) (200I, 1, 40)
(200I, Firestone Tire, 2000.00, 50) (200I,
Goodyear Tire, 8000.00, 200)
(200I, Cars R Us, 10, June, 1999)
Problem Enforces Total Order
63
Performance Evaluation
400 MHz Pentium II, 256MB
Database Size 10MB 100MB
Buffer Pool Size 40 MB
Query Depth
Query Fan Out
64
Where Does Time Go?
overlap
Database Size 10MB, Query Fan Out 2, Query
Depth 2
65
Effect of Query Depth
Database Size 10MB, Query Fan Out 2
66
Memory Considerations

• Sufficient memory
• Unsorted outer union is method of choice
• Efficient hash-based tagger
• Limited memory (large documents)
• Sorted outer union is method of choice
• Relational sort is highly scalable

67
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

68
Relational Data
PurchaseOrder
Id
Customer
Year
Month
Day
200I
Cars R Us
10
June
1999
300I
Bikes R Us
null
July
1999





Item
Payment
Name
Cost
Quantity
Pid
Installment
Percentage
Pid
200I
Firestone Tire
50
2000.00
1
200I
40
300I
Schwinn Tire
100
2500.00
300I
200I
Trek Tire
20
60
2
400.00
300I
200I
Goodyear Tire
200
8000.00
100
1







69
XML Documents
ltPurchaseOrder id200I customerCars R Usgt
lt/PurchaseOrdergt ltPurchaseOrder id300I
customerTires R Usgt lt/PurchaseOrdergt
(many more)
70
Query XML View of Relational Data
For po in view(PurchaseOrder) Where
po/_at_customer Cars R Us Return po/Item
ltPurchaseOrder id200I customerCars R Usgt
lt/PurchaseOrdergt
71
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• XML View Specification
• Query Processing
• Conclusion

72
Guiding Principle

• Allow users to create and use XML views in pure
  XML terms
• Automatically provide default XML view of
  relational database system
• Can create more complex views using XML query
  language

73
Relational Data
PurchaseOrder
Id
Customer
Year
Month
Day
200I
Cars R Us
10
June
1999
300I
Bikes R Us
null
July
1999





Item
Payment
Name
Cost
Quantity
Pid
Installment
Percentage
Pid
200I
Firestone Tire
50
2000.00
1
200I
40
300I
Schwinn Tire
100
2500.00
300I
200I
Trek Tire
20
60
2
400.00
300I
200I
Goodyear Tire
200
8000.00
100
1







74
Default XML View
ltdbgt ltPurchaseOrdergt ltrowgt
ltIdgt 200I lt/Idgt ltCustomergt
Cars R Us lt/Customergt ltDaygt 10
lt/Daygt ltMonthgt June lt/Monthgt
ltYeargt 1999 lt/Yeargt lt/rowgt
lt/PurchaseOrdergt ltItemgt
lt/Itemgt ltPaymentgt
lt/Paymentgtlt/dbgt
75
Creating a User-Defined XML View
Create View PurchaseOrderXMLView As ( For
porder in view(default)/PurchaseOrder/row
Return ltPurchaseOrder idporder/Id
customerporder/Customergt
ltDategt ltDaygt
porder/Day lt/Daygt
ltMonthgt porder/Month lt/Monthgt
ltYeargt porder/Year lt/Yeargt
lt/Dategt



lt/PurchaseOrdergt)
For item in
view(default)/Item/row
Where item/Pid porder/Id
Return ltItem nameitem/Name
costitem/Costgt
ltQuantitygt item/Quantity
lt/Quantitygt
lt/Itemgt
For pay in view(default)/Payment/row Where
pay/Pid porder/IdReturn ltPaymentgt
pay/Percentage lt/PaymentgtSortby
(pay/Installment)
76
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• XML View Specification
• Query Processing
• Conclusion

77
Query Processing ArchitectureShanmugasundaram
et al., VLDB01
Query over XML View
XML Result
XML TranslationLayer
Query Parser
XQGM
View Composition
XML Tagging
XQGM
Computation Pushdown
Sorted Outer UnionSQL Query
Relational result
RDBMS
78
Outline

• Motivation High-level Solution
• Background (Relations, XML)
• Storing and Querying XML Documents
• Materializing Relational Data as XML Documents
• Querying XML Views of Relational Data
• Conclusion

79
Conclusion

• XML has emerged as the Internet data format
• But relational database systems will continue to
  be used for data management tasks
• Internet application developers currently have to
  explicitly bridge this data model gap
• Can we design a system that automatically bridges
  this gap for application developers?

80
For cust in /CustomerWhere cust/name
Jack Return cust
81
// First prepare all the SQL statements to be
executed and create cursors for them Exec SQL
Prepare CustStmt From select cust.id, cust.name
from Customer cust where cust.name Jack Exec
SQL Declare CustCursor Cursor For CustStmt Exec
SQL Prepare AcctStmt From select acct.id,
acct.acctnum from Account acct where acct.custId
? Exec SQL Declare AcctCursor Cursor For
AcctStmtExec SQL Prepare PorderStmt From select
porder.id, porder.acct, porder.date from
PurchOrder porder
where porder.custId
? Exec SQL Declare PorderCursor Cursor For
PorderStmtExec SQL Prepare ItemStmt From select
item.id, item.desc from Item item where item.poId
? Exec SQL Declare ItemCursor Cursor For
ItemStmtExec SQL Prepare PayStmt From select
pay.id, pay.desc from Payment pay where item.poId
? Exec SQL Declare PayCursor Cursor For
PayStmt// Now execute SQL statements in nested
order of XML document result. Start with
customer XMLresult Exec SQL Open
CustCursorwhile (CustCursor has more rows)
Exec SQL Fetch CustCursor Into custId,
custName XMLResult ltcustomer id
custId gtltnamegt custName
lt/namegtltaccountsgt // For each customer,
issue sub-query to get account information and
add to custAccts Exec SQL Open AcctCursor
Using custId while (AcctCursor has more
rows) Exec SQL Fetch AcctCursor
Into acctId, acctNum XMLResult
ltaccount id acctId gt acctNum
lt/accountgt XMLResult
lt/accountsgtltpordersgt // For each
customer, issue sub-query to get purchase order
information and add to custPorders Exec SQL
Open PorderCursor Using custId while
(PorderCursor has more rows) Exec
SQL Fetch PorderCursor Into poId, poAcct,
poDate XMLResult ltporder id
poId acctpoAcct gtltdategtpoDate
lt/dategtltitemsgt // For each
purchase order, issue a sub-query to get item
information and add to porderItems
Exec SQL Open ItemCursor Using poId
while (ItemCursor has more rows)
Exec SQL Fetch ItemCursor Into itemId,
itemDesc XMLResult ltitem
id itemId gt itemDesc lt/itemgt
XMLResult
lt/itemsgtltpaymentsgt // For each
purchase order, issue a sub-query to get payment
information and add to porderPays
Exec SQL Open PayCursor Using poId
while (PayCursor has more rows)
Exec SQL Fetch PayCursor Into payId, payDesc
XMLResult ltpayment id
payId gt payDesc lt/paymentgt
XMLResult lt/paymentsgtlt/pordergt
// End of looping over all purchase
orders associated with a customer
XMLResult lt/customergt Return
XMLResult as one result row reset XMLResult
// loop until all customers are tagged and
output
82
Conclusion (Contd.)

• Yes! XPERANTO is one such system
• Allows users to
• Store and query XML documents using a relational
  database system
• Publish existing relational data as XML documents
• using a high-level XML query language
• Also provides a dramatic improvement in
  performance

83
Relational Database System Vendors

• IBM, Microsoft, Oracle, Informix,
• SQL extensions for XML
• XML Translation Layer
• Pure XML philosophy provides high-level XML
  query interface
• SQL extensions for XML, while better than writing
  applications, is still low-level
• More powerful than XML-extended SQL
• SQL just not designed with nifty XML features in
  mind

84
Related Research

• Storing and querying schema-less XML documents
  using a relational database system
• STORED Deutsch et. al.
• Binary decomposition Florescu Kossmann
• XML Views of relational databases
• SilkRoute Fernandez et. al.
• Mediators

85
(No Transcript)