• 1. DB2 9 pureXML Training深圳
  • 2. 创建DB2 9 XML数据库db2 create database testdb automatic storage yes using codeset utf-8 territory cn
  • 3. 将服务器的DB catalog到本地客户端第一步:db2 catalog tcpip node <命名node名> remote <服务器IP> server <服务器端口默认50000> 第二步:db2 catalog database testdb as <别名> at node <命名node名>
  • 4. DB2命令行连接数据库db2 connect to testdb user db2admin using 连接成功如下: 数据库连接信息 数据库服务器 = DB2/NT 9.5.0 SQL 授权标识 = DB2ADMIN 本地数据库别名 = TESTDB
  • 5. 创建测试表db2 create table <你的名字T> (i int, doc xml) 以下 <你的名字T> 简称为 T C:\db2回车进入db2命令行处理器 DB20000I SQL命令成功完成。db2=>
  • 6. 插入数据insert into T values(1,'xxx')DB20000I SQL命令成功完成。
  • 7. 选择数据select i, doc from T I DOC ----------- ----------------------- 1 xxx 1 条记录已选择。
  • 8. 更新数据update T set doc='yyy' 1 yyy
  • 9. 使用XQuery为主要查询语言:FLWORxquery for $d in db2-fn:xmlcolumn('T.DOC') return $d 注意:xquery中的db2-fn函数对表名和字段名大小写敏感 1 yyy
  • 10. 使用XQuery为主要查询语言:FLWOR插入新的数据(movie.txt共2条): insert into T values(2, 'ChicagoRenee ZellwegerRichard GereCatherine Zeta-Jones') xquery for $d in db2-fn:xmlcolumn ('T.DOC') let $a:=$d//title return $a xquery for $d in db2-fn:xmlcolumn ('T.DOC') let $a:=$d//title return $a/text() Chicago Renee Zellweger Richard Gere Catherine Zeta-Jones Chicago TransFormerChicago TransFormer
  • 11. 使用XQuery为主要查询语言:FLWOR xquery for $d in db2-fn:xmlcolumn ('T.DOC') let $a:=$d//title,$b:=$d/actor where $a/text()='Chicago' order by $b/text() return $a xquery for $d in db2-fn:xmlcolumn ('T.DOC') let $a:=$d//title order by $d/movie/@year return $a ChicagoTransFormer Chicago
  • 12. 使用XQuery为主要查询语言:sqlquery() xquery for $d in db2-fn:sqlquery('select doc from t') where $d//movie/actor/text()='爵士' return $d//movie/title/text()TransFormer
  • 13. 使用SQL/XML为主要查询语言:xmlquery()1 select i, xmlquery(' for $d in $doc//movie where $d/@year=2007 or $d/@year=2008 return $d/title' passing doc as "doc") from T where i <>12 Chicago 3 TransFormer
  • 14. 使用SQL/XML为主要查询语言:xmlquery()2 select i, xmlquery(' for $d in $doc//movie where $d/@year=2007 return $d/title' passing doc as "doc") from T where i <>12 3 TransFormer注意此处的结果在sql中符合条件的有两个记录,但对于XML列来说只有一个记录符合条件
  • 15. 使用SQL/XML为主要查询语言:xmlquery()3 select i, xmlquery(' for $d in $doc//movie where $d/@year=2007 and $d/@year=2008 return $d/title' passing doc as "doc") from T where i <>12 3在select子句中的xquery的条件会对XML列的结果产生影响,而不影响SQL条件
  • 16. 使用SQL/XML为主要查询语言:xmlquery()4 select i, xmlquery('$doc//movie/title' passing doc as "doc") from T where i <>1 在DB2 9.5中同一个查询语句中没有同名字段(即使是跨表)可以使用简写,用大写XML字段名字代替变量名 select i,xmlquery('$DOC//movie/title') from T where i <>12 Chicago 3 TransFormer
  • 17. 使用SQL/XML为主要查询语言:xmlexists() select i, xmlquery('$DOC//movie/title/text()') from T where xmlexists('$DOC//movie[@year=2008 or @year=2007]')2 Chicago 3 TransFormer在where子句中的xquery的条件会对XML和SQL列的结果产生影响
  • 18. 使用SQL/XML为主要查询语言: //、./、../、*/ select i, xmlquery(‘$DOC//movie/title/text()') from T where xmlexists('$DOC//movie/title[//text()= "爵士"] ') select i, xmlquery('$DOC//movie/title/text()') from T where xmlexists('$DOC//movie/title[./text()= “TransFormer"] ') or where xmlexists('$DOC//movie/title[text()= “TransFormer"] ')3 TransFormer3 TransFormer
  • 19. 使用SQL/XML为主要查询语言: //、./、../、*/ select i, xmlquery('$DOC//movie/title/text()') from T where xmlexists('$DOC//movie/title/../actor[text()= "爵士"] ') select i, xmlquery('$DOC//movie/title/text()') from T where xmlexists('$DOC//*/* [text()= "爵士"] ') 3 TransFormer3 ransFormer
  • 20. 使用SQL/XML为主要查询语言:contains() select i, xmlquery('$DOC//movie/title/text()') from T where xmlexists('$DOC//movie/title/text() [contains(., "Chi ")] ') or where xmlexists('$DOC//movie/title [contains(text(), "Chi ")] ') Contains的第二个参数是大小写敏感的 2 Chicago
  • 21. 使用SQL/XML为主要查询语言:xmltable()1Select x.* from xmltable('db2-fn:xmlcolumn("T.DOC")//movie ' columns name varchar(30) path 'title', year varchar(30) path '@year' ) as xNAME YEAR ------------------------------ ---- Chicago 2008 TransFormer 2007
  • 22. 使用SQL/XML为主要查询语言:xmltable()2Select x.* from T, xmltable('$DOC//movie' columns name varchar(30) path 'title', year varchar(30) path '@year', xmldoc xml path '{title/text()}{data(@year)}' ) as xNAME YEAR XMLDOC ------------------------------ -------- ----------------------------------- Chicago 2008 Chicago2008 TransFormer 2007 TransFormer2007
  • 23. 使用SQL/XML为主要查询语言:xmlcast()SELECT * FROM T WHERE XMLCAST( XMLQUERY ('$DOC/movie/title') as char(100) ) = 'Chicago'I DOC ------------------------------ -------- ----------------------------------- 2 ChicagoRenee
  • 24. 使用SQL/XML为主要查询语言:xmlrow()SELECT xmlrow(i, doc as xmldoc) as row FROM T WHERE i<>1ROW ------------------------------ -------- ----------------------------------- 23TransFormer…</li><li data-id="25">25. 使用SQL/XML为主要查询语言:xmlgroup()SELECT xmlgroup(i, doc) as ROWSET FROM T WHERE i<>1ROWSET ------------------------------ -------- ----------------------------------- <rowset><row><I>2</I><DOC><movie year="2008"><title>Chicago…<row><I>3</I><DOC><movie year="2007"><title>TransFormer… </rowset></li><li data-id="26">26. 使用SQL/XML为主要查询语言:xmlforest()、xmlelement()SELECT XMLElement (name "moviedoc", xmlforest(i as "id", doc as "doc")) FROM T WHERE i<>1DOC ------------------------------ -------- ----------------------------------- <moviedoc><id>2</id><doc><movie year="2008"><title>Chicago… </ moviedoc > <moviedoc><id>3</id><doc><movie year="2007"><title>TransFormer… </ moviedoc > </li><li data-id="27">27. XML数据更新:节点文本更新update T set doc = xmlquery( ' copy $new := $DOC modify do replace value of $new/b with "newtext" return $new' ) where i=1<b>newtext</b></li><li data-id="28">28. XML数据更新:节点删除update T set doc = xmlquery( ' copy $new := $DOC modify do delete $new/movie/title return $new' ) where i=1<movie year=“2008”><actor>Renee Zellweger</actor>…</li><li data-id="29">29. XML数据更新:节点属性修改update T set doc = xmlquery( ' copy $new := $DOC modify do replace value of $new/movie/@year with "2006" return $new' ) where i=1<movie year=“2006”><actor>Renee Zellweger</actor>…</li><li data-id="30">30. XML数据更新:节点增加update T set doc = xmlquery( ' copy $new := $DOC modify do insert <title>Chicago as first into $new/movie return $new' ) where i=1ChicagoRenee Zellweger
  • 31. XML数据更新:节点增加update T set doc = xmlquery( ' copy $new := $DOC modify do insert 2 before $new/movie/actor return $new' ) where i=12ChicagoRenee Zellweger