db2资料db2_xquery

1、IBM Software GroupIBM Internal Use Only | January 2008 2008 IBM CorporationDB2 9 pureXML Training深圳IBM Software Group | Information Management Software创建DB2 9 XML数据库db2 create database testdb automatic storage yesusing codeset utf-8 territory cnIBM Data Servers | Simplifying Data Management 2008 IBM

2、 Corporation将服务器的DB catalog到本地客户端第一步：db2 catalog tcpip node remote server 第二步：db2 catalog database testdb as at node DB2命令行连接数据库db2 connect to testdb user db2admin using 连接成功如下：= DB2/NT 9.5.0= DB2ADMIN= TESTDB数据库服务器SQL 授权标识本地数据库别名数据库连接信息创建测试表db2 create table (i int, doc xml) DB20000ISQL命令成功完成。以下 简称为

3、 T C:db2回车进入db2命令行处理器 db2=插入数据insert into T values(1,xxx) DB20000ISQL命令成功完成。选择数据select i, doc from T1 条记录已选择。1 xxxDOCI更新数据update T set doc=yyy 1 yyy 使用XQuery为主要查询语言:FLWORxquery for $d in db2-fn:xmlcolumn(T.DOC) return $d 1 yyy 注意：xquery中的db2-fn函数对表名和字段名大小写敏感使用XQuery为主要查询语言:FLWOR插入新的数据(movie.txt共2条)：

4、insert into T values(2, movieChicagoRenee ZellwegerRichard GereCatherine Zeta-Jonesyear=2002ChicagoRenee ZellwegerRichardGereCatherine Zeta-Jones)xquery for $d in db2-fn:xmlcolumn (T.DOC) let $a:=$d/title return $aChicagoTransFormerxquery for $d in db2-fn:xmlcolumn (T.DOC) let $a:=$d/title returnChi

5、cago TransFormer$a/text()使用XQuery为主要查询语言:FLWORxqueryfor $d in db2-fn:xmlcolumn (T.DOC) let $a:=$d/title,$b:=$d/actorwhere $a/text()=Chicago order by $b/text()return $axqueryfor $d in db2-fn:xmlcolumn (T.DOC) let $a:=$d/titleorder by $d/movie/year return $a Chicago TransFormerChicago使用XQuery为主要查询语言:s

6、qlquery()xquery for $d in db2-fn:sqlquery(select doc from t) where$d/movie/actor/text()=爵士 return $d/movie/title/text() TransFormer 使用SQL/XML为主要查询语言:xmlquery()1select i, xmlquery(for $d in $doc/moviewhere $d/year=2007 or $d/year=2008 return $d/title passing doc as doc)2 Chicago3 TransFormerfrom T wh

7、ere i 1使用SQL/XML为主要查询语言:xmlquery()2select i, xmlquery(for $d in $doc/movie where $d/year=2007return $d/title passing doc as doc) from Twhere i 123 TransFormer注意此处的结果在sql中符合条件的有两个记录， 但对于XML列来说只有一个记录符合条件使用SQL/XML为主要查询语言:xmlquery()3select i, xmlquery(for $d in $doc/moviewhere $d/year=2007 and $d/year=2

8、008 return $d/title passing doc as doc)from T23where i 1在select子句中的xquery 的条件会对XML列的结果产生影响，而不影响SQL条件使用SQL/XML为主要查询语言:xmlquery()4select i,xmlquery($doc/movie/title passing doc as doc) from Twhere i 12 Chicago3 TransFormer在DB2 9.5中同一个查询语句中没有同名字段（即使是跨表）可以使用简写，用大写XML字段名字代替变量名select i,xmlquery($DOC/movie

9、/title) from T where i 1使用SQL/XML为主要查询语言:xmlexists()select i,xmlquery($DOC/movie/title/text() from Twhere xmlexists($DOC/movieyear=2008 or year=2007)2 Chicago3 TransFormer在where子句中的xquery 的条件会对XML和SQL 列的结果产生影响使用SQL/XML为主要查询语言: /、./、./、*/select i,xmlquery($DOC/movie/title/text() from Twhere xmlexists

10、($DOC/movie/title/text()= 爵士 ) 3TransFormer select i,xmlquery($DOC/movie/title/text() from Twhere xmlexists($DOC/movie/title./text()= “TransFormer )orwhere xmlexists($DOC/movie/titletext()= “TransFormer ) 3 TransFormer使用SQL/XML为主要查询语言: /、./、./、*/select i,xmlquery($DOC/movie/title/text() from Twhere

11、xmlexists($DOC/movie/title/./actortext()= 爵士 ) 3TransFormer select i,xmlquery($DOC/movie/title/text() from Twhere xmlexists($DOC/*/* text()= 爵士 ) 3ransFormer使用SQL/XML为主要查询语言:contains()select i,xmlquery($DOC/movie/title/text() from Twhere xmlexists($DOC/movie/title/text() contains(., Chi ) )orwhere x

12、mlexists($DOC/movie/title contains(text(), Chi ) ) 2Chicago Contains的第二个参数是大小写敏感的使用SQL/XML为主要查询语言:xmltable()1Select x.* fromxmltable(db2-fn:xmlcolumn(T.DOC)/movie columnsname varchar(30) path title, year varchar(30) path year) as x20082007ChicagoTransFormerYEARNAMEIBM Software Group | Information Ma

13、nagement SoftwareIBM Data Servers | Simplifying Data Management 2008 IBM CorporationTransFormer2007Chicago200820082007ChicagoTransFormerXMLDOCYEARNAME使用SQL/XML为主要查询语言:xmltable()2Select x.* from T, xmltable($DOC/movie columnsname varchar(30) path title,year varchar(30) path year, xmldoc xml pathtitle

14、/text()data(year) as xIBM Software Group | Information Management Software使用SQL/XML为主要查询语言:xmlcast()SELECT * FROM TWHERE XMLCAST(XMLQUERY ($DOC/movie/title) as char(100) =ChicagoChicagoRenee2DOCIIBM Data Servers | Simplifying Data Management 2008 IBM Corporation使用SQL/XML为主要查询语言:xmlrow()SELECT xmlrow

15、(i, doc as xmldoc) as row FROM TWHERE i1ROW23TransFormer使用SQL/XML为主要查询语言:xmlgroup()SELECT xmlgroup(i, doc) as ROWSET FROM TWHERE i1ROWSET2Chicago3TransFormer 使用SQL/XML为主要查询语言:xmlforest()、xmlelement()SELECT XMLElement (name moviedoc, xmlforest(i as id, doc as doc)FROM T WHERE i1DOC2Chicago 3TransForm

16、er XML数据更新：节点文本更新update Tset doc = xmlquery( copy $new := $DOCmodify do replace value of $new/b with newtext return$new )where i=1 newtext XML数据更新：节点删除update Tset doc = xmlquery( copy $new := $DOCmodify do delete $new/movie/title return$new )where i=1 ReneeZellwegerXML数据更新：节点属性修改update Tset doc = xmlquery( copy $new := $DOCmodify do replace value of $new/movie/year with 2006 return$new )where i=1 ReneeZellwegerXML数据更新：节点增加update Tset doc = xm