2009 11 21 11 00 11 50 B 1
PostgreSQL "Let's Postgres" 2
PostgreSQL XML URL http://lets.postgresql.jp/documents/technical/tutorial/xml 3
4
Storage Query Language XML Schemas Programming Interface 5
XML A RDB 1. 2. B XML 6
RDB ID ID 12345678 ayoshida@xxx.co.jp 98765432 mabe@xxx.co.jp 8383838383 ghibiki@xxx.co.jp 7
RDB ID ID XML 12345678 98765432 8383838383 8
XML ID 9
XML A) RDB 1. 2. B) XML 10
RDB ID ID XML 12345678 98765432 8383838383 11
2008 2 8.3 XML 8.4 2001 XMLPGSQL 2002 2 7.2 XML 2005 1 8.0 XML2 Xpath XSLT 12
XML XML Xpath RDB XML SQL/XML 13
XML xml CREATE TABLE meibo(id integer, detail ); id ID ( integer ) 12345678 98765432 8383838383 detail ( xml ) <name> </mame> <email>ayoshida@ </email> <name> </mame> <email>mabe@ </email> <name> </mame> <email>ghibiki@ </email> 14
XML Xpath SELECT xpath( /employee[name/text()= ], ditail) FROM meibo; id ID ( integer ) 12345678 98765432 8383838383 detail ( xml ) <name> </mame> <email>ayoshida@ </email> <name> </mame> <email>mabe@ </email> <name> </mame> <email>ghibiki@ </email> 15
RDB XML SQL/XML Id ID Employee email 12345678 ayoshida@xxx.co.jp 98765432 mabe@xxx.co.jp 8383838383 ghibiki@xxx.co.jp select xmlelement (name email,email)from meibo; <email>ayoshida@xxx.co.jp</email> <email>mabe@xxx.co.jp</email> <email>ghibiki@xxx.co.jp</email> 16
4. XML SQL/XML SQL/XML XPath 1. XPath XML 3. xml2 XSLT XSLT GIN 2. GIN 17
PostgreSQL 8.4.1 OS Windows XP <work-record> <period> <start>1970-04-01</start> <end>1980-03-31</end> </period> <content> </content> <role> </role> </career> <period> <start>1980-04-01</start> <end>1985-03-31</end> </period> <content> Web </content> <role> </role> </career> <period> <start>1985-04-01</start> <end/> </period> <content>it </content> <role> </role> </career> </work-record> 18
psql PostgreSQL Id name department age email Work_history 1031 IT 45 ikuta@abcd <work-record>.com <period> 1088 41 maeda@abc d.com 1101 33 suzuki@abc d.com 1103 32 tanaka@abc d.com 1076 23 ishikawa@a bcd.com <work-record> <period> <work-record> <period> <work-record> <period> <work-record> <period>.csv CSV 19
20 <work-record> <period> <start>1970-04-01</start> <end>1980-03-31</end> </period> <content> </content> <role> </role> </career> <period> <start>1980-04-01</start> <end>1985-03-31</end> </period> <content> Web </content> <role> </role> </career> <period> <start>1985-04-01</start> <end/> </period> <content>it </content> <role> </role> </career> </work-record> child descendant or self descendant parent ancestor ancestor or self following following sibling preceding preceding sibling self /work record/career[role/text()= ]
id,name,<role> Id name department age email Work_history 1031 IT 45 ikuta@abcd.com <work-record> <period> <start>1970-04-01</start> <end>1980-03-31</end> </period> <content> </content> <role> </role> </career> <period> <start>1980-04-01</start> <end>1985-03-31</end> </period> <content> Web </content> <role> </role> </career> <period> <start>1985-04-01</start> <end/> </period> <content>it </content> <role> </role> </career> </work-record> XML = 21
B-Tree XML GIN XPath xpath( /work-record/career/role/text(),work_history) xpath -------------------------------------- {,, } {, } {, } { } { } 22
Id name department age email Work_history 1 2 3 299 300 XPath INDEX <work-record> <period> <start>1970-04-01</start> <end>1980-03-31</end> </period> <content> </content> <role> </role> </career> <period> <start>1980-04-01</start> <end>1985-03-31</end> </period> <content> Web </content> <role> </role> </career> <period> <start>1985-04-01</start> <end/> </period> <content>it </content> <role> </role> </career> </work-record> 1. Meibo 300 2. 3. 4. 5. 23
XML XSLT XML HTML 24
Id name departm ent age email Work_history 1031 IT 45 ikuta@abcd. com <work-record> <period> <start>1970-04-01</start> <end>1980-03-31</end> </period> <content> </content> <role> </role> </career> <period> <start>1980-04-01</start> <end>1985-03-31</end> </period> <content> Web </content> <role> </role> </career> <period> <start>1985-04-01</start> <end/> </period> <content>it </content> <role> </role> </career> </work-record> 25
<?xml version="1.0" encoding= Shift_JIS"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/xsl/transform"> <xsl:output method="html"/> <xsl:template match="/"> <html ><xsl:apply-templates/></html> </xsl:template> <xsl:template match="work-record"> XSLT <h1> </h1> <table frame="border" width="50%"> <tr><th> </th><th> </th><th> </th><th> </th></tr> <xsl:for-each select="career"> <tr> <td><xsl:value-of select="period/start"/></td> <td><xsl:value-of select="period/end"/></td> <td><xsl:value-of select="content"/></td> <td><xsl:value-of select="role"/></td> </tr> </xsl:for-each> </table> </xsl:template> </xsl:stylesheet> I d na me depart ment 1031 IT Work_history <work-record> <period> <start>1970-04-01</start> <end>1980-03-31</end> </period> <content> </content> <role> </role> </career> <period> <start>1980-04-01</start> 1. 2. xslt_process xslt_process() WHERE name = ' ' 3. 4. 26
xmlelement() xmlforest() xmlattributes() xmlcomment() xmlconcat() xmlpi() xmlroot() xmlagg() xmlparse() xmlserialize() XML XML XML XML XML XML XML XML XML xmlconcat XML XML (PostgreSQL 8.4.1 8.13.1 9.14.1 ) 27
declaration name root person email mail-list name person email XML SQL/XML id name email work_history 1031 ikuta@abcd.com <work record> 28
XML SELECT select xmlroot(xmlelement(name mail_list, (select xmlagg( xmlelement(name person, xmlelement(name name, name), xmlelement(name email, email) )) from meibo)),version '1.0',standalone yes); 29
XPath XSLT XQL/XML XML PostgreSQL XML 6 Postgresql XML 30
Storage Query Language XML Programming Interface 31
Schemas Web Service 32
XML Xerces 33
XML 34
XML XML Xpath RDB XML SQL/XML 35
XML 36