Mapping and Storing XML within Relational Tables

1
Mapping and Storing XML within Relational Tables

• Carol Payne
• David Knight

2
Overview

• Importance of XML integration into relational
  databases
• Current generalized methods for integration
• Solutions available from two main database
  vendors (Oracle and IBM)

3
Why is integration important?

• XML is being used as the main means of storing
  and transporting non-proprietary data
• XML and object-oriented databases have not hit
  critical mass
• Everybody uses relational databases

4
What happens during the Integration process?
Flight Database
XML message
ltFlight_Reservationgt ltFlight_Reservation_Nogt0045lt
/Flight_Reservation_Nogt ltPassenger_namegtDavid
Knightlt/Passenger_namegt ltDestinationgtSan
Francisco, CAlt/Destinationgt ltCostgt299.95lt/Costgt
ltCredit_Cardgt4444 5555 6666 7777lt/Credit_Cardgt lt/
Flight_Reservationgt
5
More detailed Illustration of Integration
Flight Table
XML message
ltFlight_Reservationgt ltFlight_Reservation_Nogt0045lt
/Flight_Reservation_Nogt ltPassenger_namegtDavid
Knightlt/Passenger_namegt ltDestinationgtSan
Francisco, CAlt/Destinationgt ltCostgt299.95lt/Costgt
ltCredit_Cardgt4444 5555 6666 7777lt/Credit_Cardgt lt/
Flight_Reservationgt
6
Seems Easy, Right?
XML message
ltFlight_Reservation date11/25/03gt ltFlight_Rese
rvation_Nogt0045lt/Flight_Reservation_Nogt ltPassenge
rgt ltP_namegtDavid Knightlt/P_namegt
ltP_addressgt111 North St.lt/P_addressgt
ltP_citygtChapel Hilllt/P_citygt
ltP_stategtNClt/P_stategt ltP_zipgt27517lt/P_zip
gt lt/Passengergt ltDestinationgtSan Francisco,
CAlt/Destinationgt ltCostgt299.95lt/Costgt ltCredit_Ca
rdgt ltCCNgt4444 5555 6666 7777lt/CCNgt
ltexp_dategt1105lt/exp_dategt lt/Credit_Cardgt lt/Flight
_Reservationgt
Flight Database
?
7
Table-based Mapping
Flight Table
Flight Reservation
XML message
ltFlight_Reservationgt ltFlight_Reservation_Nogt0045lt
/Flight_Reservation_Nogt ltPassenger_namegtDavid
Knightlt/Passenger_namegt ltDestinationgtSan
Francisco, CAlt/Destinationgt ltCostgt299.95lt/Costgt
ltCredit_Cardgt4444 5555 6666 7777lt/Credit_Cardgt lt/
Flight_Reservationgt
8
Table-based Mapping
PROs
CONs

• Simple and easy to execute. Easy to set up the
  mappings to the relational table.

• Doesnt take advantage of XMLs complexity
• The creator of the XML must constrain it to a
  pre-defined, simple structure
• Doesnt allow for the use of attributes

9
Object-based Mapping
XML message
ltFlight_Reservation date11/25/03gt ltFlight_Rese
rvation_Nogt0045lt/Flight_Reservation_Nogt ltPassenge
rgt ltP_namegtDavid Knightlt/P_namegt
ltP_addressgt111 North St.lt/P_addressgt
ltP_citygtChapel Hilllt/P_citygt
ltP_stategtNClt/P_stategt ltP_zipgt27517lt/P_zip
gt lt/Passengergt ltDestinationgtSan Francisco,
CAlt/Destinationgt ltCostgt299.95lt/Costgt ltCredit_Ca
rdgt ltCCNgt4444 5555 6666 7777lt/CCNgt
ltexp_dategt1105lt/exp_dategt lt/Credit_Cardgt lt/Flight
_Reservationgt
Class Flight Reservation String
Flight_Reservation_No Passenger P1 String
Destination String Cost CCI cci_p1 String
date Class Passenger String
P_name String P_address String
P_city String P_state Int P_zip Class CCI
int CCN String exp_date
10
Object-based Mapping
Class Flight Reservation String
Flight_Reservation_No Passenger P1 String
Destination String Cost CCI cci_p1 String
date Class Passenger String
P_name String P_address String
P_city String P_state Int P_zip Class CCI
int CCN String exp_date
Flight Reservation Table
Passenger Table
Credit Card Table
11
Object-based Mapping
PROs
CONs

• Handles complex-type mapping
• Preserves relationships between entities
• XML creation is not constrained

• More difficult to execute
• More difficult to develop

12
Oracle Strategy

• Element level processing instructions included
  inside the XML Schema

Flight Reservation Table
Flight Reservations Database Oracle 9i
ltXMLgt Flight Reservation XML Messages lt/XMLgt
Passenger Table
Credit Card Information Table
Flight Reservation XML Schema
13
IBM Strategy

• XML Extender, DADs and the XML Collection method

Flight Reservation Table
Flight Reservations Database DB2
ltXMLgt Flight Reservation XML Messages lt/XMLgt
Passenger Table
Credit Card Information Table
XML Extender
Flight Reservation DTD schema
Flight Reservation DAD schema
14
Who Will This Affect?

• Database users
• General public
• - wont affect as much as a result of integration
  being more of a behind-the-scenes process
• Database administrators
• Database developers
• - lots of headaches!

15
What We Found

• XML can offer too much complexity for straight
  integration into a relational table
• Two types of mapping Table-based and
  Object-based
• In order to carry out this mapping, most database
  vendors offer applications to create mapping
  rules as well as processing technology to insert
  data based on these rules

16
Future of RDBMS XML

• Main database vendors will continue to offer
  efficient components for storing XML data inside
  of relational tables.
• No move to go to purely native XML databases
  amongst the main vendors