SQL/XML for Developers - Database Wisdom

1 SQL/XML for Developers Lewis Cunningham Data Architect, JP Morgan Chase 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 The Database Geek Cloud Computing Info SQL/XML for Developers Agenda What is XML? What is the XMLType data type? What is SQL/XML ? What should(n t) I do with XML? 3 What is XML? <?XML?> 4 What is XML? Semi-Structured Hierarchical Not relational Not freeform Markup Language Tags Identify Data <NAME>Lewis</NAME> Human AND machine readable 5 What is XML? Elements Root Element Child Elements Elements are nodes Some nodes contain elements Some nodes contain character data A node can contain both Namespaces 6 What is XML? 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 7 What is XML?

2 Simple Example <rootnode> <childnode1>Some Data</childnode1> <childnode2>Some more <additional>data</additional> </childnode2> <emptytag /> </rootnode> Element/Tag Closing Tag Childnode1 Child to rootnode Empty Tag Childnode1 is Sibling to childnode2 8 What is XML? Namespaces A namespace prevents naming collisions A namespace provides clarity A namespace allows multiple documents to be combined into a single document 9 What is XML? Simple Example <rootnode xmlns:abc=' ' > <abc:xml:childnode1>Some Data</abc:childnode1> <abc:childnode2>Some more <additional>data</additional> </abc:childnode2> <emptytag /> </rootnode> Namespace Identifier Namespace Usage Namespace Name Namespace URI Default Usage 10 What is XML? Describing XML DTD Document Type Description XSD XML Schema Relax NG REgular LAnguage for Xml Next Generation 11 What is XML? 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 12 DTD Example <!

3 DOCTYPE note [ < !ELEMENT note (to,from,heading,body)> <!ELEMENT to (#PCDATA)> <!ELEMENT from (#PCDATA)> <!ELEMENT heading (#PCDATA)> <!ELEMENT body (#PCDATA)> ]> 13 What is XML? 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 14 XSD Example <xsd:schema xmlns:xsd=" "> <xsd:element name="product" type="ProductType"/> <xsd:complexType name="ProductType"> <xsd:sequence> <xsd:element name="number" type="xsd:integer"/> <xsd:element name="size" type="SizeType"/> </xsd:sequence> <xsd:attribute name="effDate" type="xsd:date"/> </xsd:complexType> <xsd:simpleType name="SizeType"> <xsd:restriction base="xsd:integer"> <xsd:minInclusive value="2"/> <xsd:maxInclusive value="18"/> </xsd:restriction> </xsd:simpleType> </xsd:schema> 15 What is XML?

4 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 16 RELAX NG (XML) Example <element name="patron" xmnln=" "> <interleave> <element name="name"> <text/> </element> <element name="id-num"> <text/> </element> <zeroOrMore> <element name="book"> <choice> <attribute name="isbn"/> <attribute name="title"/> </choice> </element> </zeroOrMore> </interleave> </element> 17 RELAX NG (Compact) Example element patron { element name { text } & element id-num { text } & element book { (attribute isbn { text } | attribute title { text } ) }* } 18 What is the XMLType data type?)

5 Object Data Type Clob based Well formed check Validating and Non-Validating May contain XML fragments 19 XML Document <onlyoneroot> data goes here </onlyoneroot> XML Content (fragment) <name>Lewis</name> <sex>yes</sex> 20 What is the XMLType data type? Declare a column as XML create table xml_tab ( id integer, data XMLType ); Declare a variable as XML DECLARE v_xml XMLType; BEGIN .. 21 What is the XMLType data type? What is SQL/XML ? SQL/ XML 22 What is SQL/XML ? 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): 23 What is SQL/XML ? 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 24 What is SQL/XML ?

6 SQL/X Functions XMLP arse XMLS erialize XMLS equence XMLT able XMLE lement XMLF orest XMLAgg XML Comment XMLC oncat More 25 What is SQL/XML ? 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) ) 26 What is SQL/XML ? Sample sample data: INSERT INTO EMP( LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY) VALUES ('Blow', 1, 'Joe', 1, 10000); Also ('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) ; 27 What is SQL/XML ? - XML Parse The XMLType constructor, or the SQL/XML function XMLP arse, will convert text (or clobs) into XML vXMLVar XMLType; vXMLVar := XMLType( <root>data</root> ); vXMLVar := XMLP arse(DOCUMENT <root>data</root> ); 28 What is SQL/XML ?

7 - XMLS erialize XMLS erialize turns XML into text (you can also use ) vString := XMLS erialize( DOCUMENT v_xml AS VARCHAR2); vString := XMLS erialize( CONTENT v_xml AS CLOB); 29 What is SQL/XML ? - XMLE lement SELECT XMLE lement(name main, last_name) from emp; <main>Blow</main> <main>Head</main> <main>Jack</main> <main>Hard</main> <main>First</main> 30 What is SQL/XML ? XMLE lement Cont d SELECT XMLE lement(name main, last_name), XMLE lement(name main, first_name) FROM emp; <MAIN>Blow</MAIN> | <MAIN>Joe</MAIN> <MAIN>Head</MAIN> | <MAIN>Helmut</MAIN> <MAIN>Jack</MAIN> | <MAIN>Noe</MAIN> <main>Hard</main> | <main>Blow</main> 31 What is SQL/XML ? - XMLF orest SELECT XMLF orest(last_name, first_name) FROM emp; <LAST_NAME>Blow</LAST_NAME> <FIRST_NAME>Joe</FIRST_NAME> <LAST_NAME>Head</LAST_NAME> <FIRST_NAME>Helmut</FIRST_NAME> 32 What is SQL/XML ?

8 XMLF orest Cont d SELECT XMLE lement(name main, XMLF orest(last_name, first_name) ) FROM emp; <MAIN> <LAST_NAME>Blow</LAST_NAME> <FIRST_NAME>Joe</FIRST_NAME> </MAIN> <MAIN> <LAST_NAME>Head</LAST_NAME> <FIRST_NAME>Helmut</FIRST_NAME> </MAIN> 33 What is SQL/XML ? XMLAgg SELECT XMLAgg( XMLF orest(last_name, first_name) ) FROM emp; <LAST_NAME>Blow</LAST_NAME> <FIRST_NAME>Joe</FIRST_NAME> <LAST_NAME>Head</LAST_NAME> <FIRST_NAME>Helmut</FIRST_NAME> <LAST_NAME>Jack</LAST_NAME> <FIRST_NAME>Noe</FIRST_NAME>.. 34 What is SQL/XML ? XMLAgg Cont'd SELECT XMLE lement(name main, XMLAgg(XMLF orest(last_name, first_name) )) FROM emp; <MAIN> <LAST_NAME>Blow</LAST_NAME> <FIRST_NAME>Joe</FIRST_NAME> <LAST_NAME>Head</LAST_NAME> <FIRST_NAME>Helmut</FIRST_NAME> <LAST_NAME>Jack</LAST_NAME> <FIRST_NAME>Noe</FIRST_NAME>.. </MAIN> 35 What is SQL/XML ?

9 Concatenating Columns SELECT XMLE lement(name main, XMLF orest(last_name || ',' || first_name AS fullname, salary) ) FROM emp; <MAIN> <FULLNAME>Blow,Joe</FULLNAME> <SALARY>10000</SALARY> </MAIN> <MAIN> <FULLNAME>Head,Helmut</FULLNAME> <SALARY>12000</SALARY> </MAIN> 36 What is SQL/XML ? Concat and Attributes SELECT XMLE lement(name main, XMLE lement(name fullname, XMLA ttributes(dept_id), last_name || ',' || first_name ), XMLF orest(salary) ) FROM emp; <MAIN> <FULLNAME DEPT_ID="1">Blow,Joe</FULLNAME> <SALARY>10000</SALARY> </MAIN> 37 What is SQL/XML ? - XMLC omment SELECT XMLE lement(name main, XMLC omment('Comment goes here'), XMLF orest(last_name, first_name)) FROM emp; <MAIN> <!--Comment goes here--> <LAST_NAME>Blow</LAST_NAME> <FIRST_NAME>Joe</FIRST_NAME> </MAIN> 38 What is SQL/XML ? - XMLC oncat SELECT XMLE lement(name lastname, last_name), XMLE lement(name firstname, first_name) FROM emp; <LASTNAME>Blow</LASTNAME> <FIRSTNAME>Joe</FIRSTNAME> <LASTNAME>Head</LASTNAME> <FIRSTNAME>Helmut</FIRSTNAME> 39 What is SQL/XML ?

10 XMLC oncat Cont'd SELECT XMLC oncat( XMLE lement(name lastname, last_name), XMLE lement(name firstname, first_name) ) FROM emp; <LASTNAME>Blow</LASTNAME> <FIRSTNAME>Joe</FIRSTNAME> <LASTNAME>Head</LASTNAME> <FIRSTNAME>Helmut</FIRSTNAME> 40 What is SQL/XML ? XMLC oncat Cont'd SELECT XMLE lement(name main, XMLC oncat( XMLE lement(name lastname, last_name), XMLE lement(name firstname, first_name) ) ) FROM emp; <MAIN> <LASTNAME>Blow</LASTNAME> <FIRSTNAME>Joe</FIRSTNAME> </MAIN> <MAIN> <LASTNAME>Head</LASTNAME> <FIRSTNAME>Helmut</FIRSTNAME> </MAIN> 41 What should(n t) I do with XML? XML 42 What should(n t) I do with XML? 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 43 What should I do with XML?