sql/xml for postgres developers
Post on 12-Nov-2014
6.920 Views
Preview:
DESCRIPTION
TRANSCRIPT
SQL/XML for Developers
Lewis CunninghamSenior Solutions ArchitectEnterpriseDBLewis.cunningham@enterprisedb.co
m1
Postgres Rocks!
Agenda
What is XML?
What is the XML data type?
What is SQL/XML?
What should(n’t) I do with XML?
2
What is XML?
<?XML?>
3
What is XML?
Semi-Structured Hierarchical Not relational Not freeform
Markup “Language” Tags Identify Data <NAME>Lewis</NAME>
Human AND machine readable
4
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
5
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
6
What is XML?
Simple Example
<rootnode><childnode1>Some Data</childnode1><childnode2>Some more
<additional>data</additional></childnode2><emptytag />
</rootnode>
Element/Tag
ClosingTag
Childnode1Child to rootnode
EmptyTag
Childnode1 isSibling tochildnode2
7
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
8
What is XML?
Simple Example
<rootnode xmlns:abc='http://abc.org/xml' ><abc:xml:childnode1>Some Data</abc:childnode1><abc:childnode2>Some more
<additional>data</additional> </abc:childnode2>
<emptytag /></rootnode>
NamespaceIdentifier
NamespaceUsage
NamespaceName
NamespaceURI
Default Usage
9
What is XML?
Describing XML
DTD – Document Type Description
XSD – XML Schema
Relax NG – REgular LAnguage for Xml Next Generation
10
What is XML?
DTD
A list of valid elements and attributesMay be inline or externalOriginal descriptive languageLimited and mostly obsoleteNo data type definitionsNo support for Namespaces
11
DTD Example
<!DOCTYPE note [ <!ELEMENT note (to,from,heading,body)> <!ELEMENT to (#PCDATA)> <!ELEMENT from (#PCDATA)> <!ELEMENT heading (#PCDATA)> <!ELEMENT body (#PCDATA)> ]>
12
What is XML?
XSD
The XML Schema is the W3C replacement to DTDs
XSD supports data types an namespacesXML Schemas are defined as XMLAllows you to define ordering/number of
elementsAllows you to define mandatory elementsXML Schemas are extensible
13
XSD Example
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <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>
14
What is XML?
RELAX NG
XML and non-XML formatsSimpler than XSDMore like a speaking dialect than an XML
dialectSupports data types and namespacesNot as robust as XSD (fewer data types, not as
many rules (defaults and such)Not as widely utilized as XSD
15
RELAX NG (XML) Example
<element name="patron" xmnln="http://relaxng.org/ns/structure/1.0"> <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>
16
RELAX NG (Compact) Example
element patron { element name { text } & element id-num { text } & element book { (attribute isbn { text } | attribute title { text } ) }* }
17
What is the XML data type?
Postgres 8.3 Data Type
Text based
Well formed check
Non-validating
18
What is the XML data type?
XML Document <onlyoneroot>
data goes here</onlyoneroot>
XML Content (fragment)<name>Lewis</name><sex>yes</sex>
19
What is the XML data type?
Declare a column as XMLcreate table xml_tab (
id integer,data XML );
Declare a variable as XMLDECLARE v_xml XML;BEGIN……
20
What is SQL/XML?
SQL/XML
21
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: www.sqlx.org
22
What is SQL/XML?
SQL/XML defines a set of mappings and a set of functions
Based on XQuery and XPath
Postgres 8.3 implements the core functions of SQL/XML
XQuery is not yet supported in Postgres
23
What is SQL/XML?
SQL/X Functions XMLParse XMLSerialize XMLElement XMLForest XMLAgg XMLAttributes XML Comment XMLConcat Xpath
24
What is SQL/XML?
Sample data
CREATE TABLE EMP( LAST_NAME text, EMP_ID integer NOT NULL, FIRST_NAME text, DEPT_ID integer, SALARY integer, CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID))WITH (OIDS=FALSE);
25
What is SQL/XML?
Sample tableINSERT INTO EMP(
LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY)VALUES ('Blow', 1, 'Joe', 1, 10000), ('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) ;
26
What is SQL/XML? - XML Parse
XMLParse turns text into XML
vXMLVar := XMLParse(CONTENT ‘<root>data</root>’);
vXMLVar := XMLParse(DOCUMENT ‘<root>data</root>’);
27
What is SQL/XML? - XMLSerialize
XMLSerialize turns XML into text
vString := XMLSerialize( DOCUMENT v_xml AS TEXT);
vString := XMLSerialize( CONTENT v_xml AS TEXT);
28
What is SQL/XML? - XMLElement
SELECT XMLElement(name main, last_name) from emp;
<main>Blow</main>
<main>Head</main>
<main>Jack</main>
<main>Hard</main>
<main>First</main>
29
What is SQL/XML? – XMLElement Cont’d
SELECT XMLElement(name main, last_name), XMLElement(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>
30
What is SQL/XML? - XMLForest
SELECT XMLForest(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>
31
What is SQL/XML? – XMLForest Cont’d
SELECT XMLElement(name main, XMLForest(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>
32
What is SQL/XML? – XMLAgg
SELECT XMLAgg( XMLForest(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>…
33
What is SQL/XML? – XMLAgg Cont'd
SELECT XMLElement(name main, XMLAgg(XMLForest(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>…
34
What is SQL/XML? - XMLAttributes
SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum) )
FROM emp;
<main rownum="1"/>
<main rownum="2"/>
<main rownum="3"/>
<main rownum="4"/>
35
What is SQL/XML? – XMLAttributes Cont’d
CREATE TEMP SEQUENCE t_seq;
SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum),
XMLForest(last_name, first_name) )FROM emp;
DROP SEQUENCE t_seq;
36
What is SQL/XML? – XMLAttributes Cont’d
<main rownum="1"> <last_name>Blow</last_name>
<first_name>Joe</first_name>
</main>
<main rownum="2">
<last_name>Head</last_name>
<first_name>Helmut</first_name>
</main>
37
What is SQL/XML? – Concatenating Columns
SELECT XMLElement(name main, XMLForest(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>
38
What is SQL/XML? – Concat and Attributes
SELECT XMLElement(name main, XMLElement(name fullname, XMLAttributes(dept_id), last_name || ',' || first_name
), XMLForest(salary) ) FROM emp;
<main>
<fullname dept_id="1">
Blow,Joe</fullname>
<salary>10000</salary>
</main>
39
What is SQL/XML? - XMLComment
SELECT XMLElement(name main, XMLComment('Comment goes here'),
XMLForest(last_name, first_name))FROM emp;<main>
<!—Comment goes here-->
<last_name>Blow</last_name>
<first_name>Joe</first_name>
</main>
40
What is SQL/XML? - XMLConcat
SELECT XMLElement(name lastname, last_name), XMLElement(name firstname, first_name)FROM emp;
<lastname>Blow</lastname> | <firstname>Joe</firstname>
<lastname>Head</lastname> | <firstname>Helmut</firstname>
41
What is SQL/XML? – XMLConcat Cont'd
SELECT XMLConcat( XMLElement(name lastname, last_name), XMLElement(name firstname, first_name) )FROM emp;
<lastname>Blow</lastname><firstname>Joe</firstname>
<lastname>Head</lastname><firstname>Helmut</firstname>
42
What is SQL/XML? – XMLConcat Cont'd
SELECT XMLElement(name main, XMLConcat( XMLElement(name lastname, last_name), XMLElement(name firstname, first_name) ) )FROM emp;<main>
<lastname>Blow</lastname>
<firstname>Joe</firstname>
</main>
43
What is SQL/XML? – XML Tables
CREATE TABLE xmltab (col1 XML);
INSERT INTO xmltab ( SELECT XMLElement(name main,
XMLConcat( XMLElement(name lastname, last_name), XMLElement(name firstname, first_name) )
) FROM emp);
44
What is SQL/XML? – XML Table
SELECT * FROM xmltab;
<main><lastname>Blow</lastname><firstname>Joe</firstname></main>
<main><lastname>Head</lastname><firstname>Helmut</firstname></main>
<main><lastname>Jack</lastname><firstname>Noe</firstname></main>
<main><lastname>Hard</lastname><firstname>Blow</firstname></main>
45
What is SQL/XML? - XPath
XPath is a language for navigating through nodes in an XML document
XPath is hierarchicalThink of XPath like navigating directories in a
file systemAn XPath expression may point to
A Node (like a directory) Data (like a file) Functions (like file properties or file values)
46
What is SQL/XML? – Xpath Cont'd
SELECT Xpath('/main/firstname/text()', col1) FROM xmltab;
xpath
-----------
{Joe}
{Helmut}
{Noe}
{Blow}
{Hugo}
47
What is SQL/XML? – Xpath Cont'd
SELECT textcol[1] FROM ( SELECT xpath('/main/firstname/text()', col1) AS
textcol FROM xmltab ) AS xmlsource;
textcol
---------
Joe
Helmut
Noe
Blow
Hugo
48
What is SQL/XML? – XML from a Query
select query_to_xml('select * from emp', TRUE, TRUE, '');
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<last_name>Blow</last_name>
<emp_id>1</emp_id>
<first_name>Joe</first_name>
<dept_id>1</dept_id>
<salary>10000</salary>
</row>
49
What is SQL/XML? – XML from a Table
select table_to_xml('emp', TRUE, TRUE, '');
<emp xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<last_name>Blow</last_name>
<emp_id>1</emp_id>
<first_name>Joe</first_name>
<dept_id>1</dept_id>
<salary>10000</salary>
</emp>
50
What should(n’t) I do with XML?
XML
51
What should(n’t) I do with XML?
PostgreSQL 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
52
What should(n’t) I do with XML? Cont'd
Convert your XML to Relations by Shredding Map your relational schema to the XML by its Schema
or DTD Use XPath to extract columnar data
Use SQL/XML to recreate the original XML document
53
What should(n’t) I do with XML? Cont'd
Relational data is much easier to Index Update Manipulate
XML data is better for Use by some programming languages CMS systems Very unstructured data For reporting
54
What should(n’t) I do with XML? Cont'd
XML is great for Public stored procedure interfaces
Publish the XML spec Include a version element or attribute Change parameters and let users adapt over time
Web based processing Many web apps support XML XML + XSLT = Web Happiness
Data interfaces Platform independent Current parsers are fast Validation and versioning built-in Public Specifications
55
SQL/XML for Developers
Lewis CunninghamSenior Solutions ArchitectEnterpriseDBLewis.cunningham@enterprisedb.co
m56
top related