XML with RDBMS

1
XML with RDBMS

• coping with the paradigm issue

2
XML Storage options

• Incoming XML can be treated in different ways,
  depending on what your application needs
• Store XML in OS File with a pointer
• Bring the file,as is, into a table (CLOB)
• Read the file, and programmatically break it up
  and put into relational storage
• Store in a XMLDB repository

3
Oracle XML DB White Paper
http//otn.oracle.com/tech/xml/xmldb/pdf/XMLDB_Tec
hnical_Whitepaper.pdf
4
(No Transcript)
5
Tamino

• "Storing XML documents in a true native XML data
  store integrated in an XML server is the key for
  to keeping pace with steadily increasing business
  dynamics. And it is the best way to respond to
  demands for maximum performance and scalability,
  with the lowest operational and administration
  costs"

http//www1.softwareag.com/Corporate/products/tami
no/prod_info/default.asp
"Here's why you too should choose XML technology"
6
Tamino architecture
http//www1.softwareag.com/Corporate/products/tami
no/prod_info/architecture/default.asp
7
The Oracle approach
8
Generic approach
http//www.research.att.com/sihem/shrex/shrex-wid
m2004.pdf
9
Doing XML in Oracle

• XML_DB
• Getting XML out from Oracle
• Getting XML into Oracle
• Procedural approach
• SQLLDR
• Oracle XML Developer's Kit 10g

10
XML_DB

• Oracle XML DB is fully compliant with the W3C XML
  data model
• provides new standard access methods for XML
  navigation and query.
• Not a Separate Database Server
• Technical white paper
• http//download-uk.oracle.com/technology/tech/xml/
  xmldb/current/twp.pdf

11
XML_DB features

• XMLType
• is a native datatype
• extend CLOB datatype
• used to store and manage XML documents
• Columns
• CREATE TABLE NewCDInfo (
• DateTime_Recd Date DEFAULT SYSDATE,
• SourceFileName Varchar2(60),
• XMLContent XMLType)
• or Tables
• CREATE TABLE CDXML of XMLType

12
XML_DB features

• REGISTERING AN XML SCHEMA
• XML Schema registered by calling the PL/SQL
  procedure dbms_xmlschema.register_schema()
• An XML Schema registration caused a default table
  to be created for each element in the XML Schema.
• When an instance document is loaded in the Oracle
  XML_DB repository, the content of the document
  will be stored in the default table.
• NOTE tables created by this process are XMLType
  tables
• Object tables where each row is represented as an
  instance of the XMLType datatype

13
XML_DB benefits

• The ability to store and manage both structured
  and unstructured data under the same standard W3C
  XML data model (XML Schema).
• Complete transparency and interchangeability
  between the XML and SQL data views.
• FTP, and WebDAV protocol support.
• Store XML in the database and render it queryable
  and at the same time access it through popular
  desktop tools.
• Claimed by XML_DB manual
• (http//oracledocs.shu.ac.uk/oracle/10.1/GenericV1
  /appdev.101/b10790/xdb01int.htmi1047230)

14
XML_DB uses include

• Business-to-Business (B2B) and Application-to-Appl
  ication (A2A) integration
• Content-management applications
• Web Services
• A typical Oracle XML DB application has one or
  more of the following requirements and
  characteristics
• Large numbers of XML documents must be ingested
  or generated
• Large XML documents need to be processed or
  generated
• High performance searching, both within a
  document and across a large collections of
  documents
• High Levels of security. Fine grained control of
  security
• Uses languages such as Java that support open
  standards such as SQL, XML, XPath, and XSLT
• Accesses information using standard Internet
  protocols such as FTP, HTTP/WebDAV, or JDBC
• Full queriability from SQL and integration with
  analytic capabilities
• Validation of XML documents is critical

http//oracledocs.shu.ac.uk/oracle/10.1/GenericV1/
appdev.101/b10790/xdb02rep.htmi1034512
15
XML_DB systems design factors

• Data
• Structured?
• Schema based?
• Access
• How do applications get to the data?
• security
• Application Language
• What are your organisations skillsets?
• Performance issues

http//oracledocs.shu.ac.uk/oracle/10.1/GenericV1/
appdev.101/b10790/xdb02rep.htmi1034512
16
Getting XML Out using XML SQL Utility (XSU).

• XSU Java Servlet released Oracle8i
• In Oracle9i improved functionality and PL/SQL
  APIs
• Two key packages
• DBMS_XMLQuery
• getXML
• getDTD
• DBMS_XMLSave

17
Getting XML Out using XML SQL Utility (XSU).

• The basic stages of the procedure are
• Assign the Select that generates the data we want
  to the context called Qry
• Use the DBMS_XMLQuery procedure called getXML to
  return the result set, and put the resultant CLOB
  into the pre-defined variable Members_xml.
• Contexts need to be closed
• Return the generated CLOB as a result

18
From Table to XML

• CREATE OR REPLACE FUNCTION membernames2xml RETURN
  CLOB IS
• members_xml CLOB
• Qry DBMS_XMLQuery.ctxType
• BEGIN
• Qry DBMS_XMLQuery.newContext('SELECT FROM
  member')
• Members_xml DBMS_XMLQuery.getXML(Qry)
• DBMS_XMLQuery.closeContext(Qry)
• RETURN members_xml
• END
• /

19
Outputing XML with XML_DB SQL extentions

• set pagesize 0
• set long 10000
• select 'lt?xml version"1.0"?gt' from dual
• select 'ltROWSETgt' from dual
• SELECT
• xmlelement("CD", xmlforest(a.composer,
  b.title, b.cdid))
• FROM composers a, cds b
• where a.composeridb.composerid
• and a.composerid lt 10
• select 'lt/ROWSETgt' from dual

20
IVY

• To carry out the server-side procedures in the
  tutorial you need to have access to a folder that
  the Oracle Server can access
• Log in to Ivy using Telnet and run
  /usr/local/bin/oracle_dir
• This process creates a Database Directory Object
  which maps to the user's home directory on ivy
  and the F drive /homedir/oracle_work
• The Directory object has the following name
• USERNAME_ORACLE_WORK
• For example CMSPL4_ORACLE_WORK

21
Inserting to a table from XML

• We can insert XML directly into a table of type
  XMLType
• drop table CDXML
• CREATE TABLE CDXML of XMLType
• Declare
• this_clob CLOB
• Begin
• this_clobgetclobfromfile('CMSPL4_ORACLE_WORK','N
  ewCD1471.xml')
• INSERT INTO CDXML
• VALUES ( xmltype ( this_clob )
  )
• END
• /
• set long 1000
• select from CDXML

22
Inserting to a table from XML

• There are more steps involved than there were for
  generating XML from SQL. We need to
• read the XML file from the OS or URL
• PARSE it (make some sense of it)
• Put it in a memory structure that we can
  manipulate it (a DOM document).
• Decide how we will handle the mapping to fields
• And then insert the data
• See example in tutorial