DB2 XQuery学习笔记

 
DB2 9有一个新特性就是增加XML类型数据存储,操作这种数据最理想的方式就是通过XQuery来进行,XQuery是XML数据检索的标准,可以在W3C网站上查看其规范。但是,XQuery需要数据的支持才能操作XML数据。说来麻烦,看看就明白了。
 
操作环境:
DB2 V9.1命令行
 
为了实践,首先从命令行连接到DB2数据库
db2 connect to dbname user username using password
 
然后设置语句的终止符为“~”
db2 -td~
 
下面就开始照着代码操作了。
db2 -td~ 

CREATE  DATABASE xmltut USING CODESET GBK TERRITORY US~ 

CREATE  TABLE Customer (Cid  BIGINT  NOT  NULL  PRIMARY  KEY, Info XML)~ 

CREATE  UNIQUE  INDEX cust_cid_xmlidx  ON Customer(Info) 
GENERATE  KEY USING XMLPATTERN 
'declare default element namespace "http://posample.org"~ /customerinfo/@Cid' 
AS SQL  DOUBLE


INSERT  INTO Customer (Cid, Info)  VALUES (1000, 
'<customerinfo xmlns="http://posample.org" Cid="1000"> 
< name>Kathy Smith</ name
<addr country="Canada"> 
<street>5 Rosewood</street> 
<city>Toronto</city> 
<prov-state>Ontario</prov-state> 
<pcode-zip>M6W 1E6</pcode-zip> 
</addr> 
<phone type=" work">416-555-1358</phone> 
</customerinfo>')~ 

INSERT  INTO Customer (Cid, Info)  VALUES (1002, 
'<customerinfo xmlns="http://posample.org" Cid="1002"> 
< name>Jim Noodle</ name
<addr country="Canada"> 
<street>25 EastCreek</street> 
<city>Markham</city> 
<prov-state>Ontario</prov-state> 
<pcode-zip>N9C 3T6</pcode-zip> 
</addr> 
<phone type=" work">905-555-7258</phone> 
</customerinfo>')~ 

INSERT  INTO Customer (Cid, Info)  VALUES (1003, 
'<customerinfo xmlns="http://posample.org" Cid="1003"> 
< name>Robert Shoemaker</ name
<addr country="Canada"> 
<street>1596 Baseline</street> 
<city>Aurora</city> 
<prov-state>Ontario</prov-state> 
<pcode-zip>N8X 7F8</pcode-zip> 
</addr> 
<phone type=" work">905-555-2937</phone> 
</customerinfo>')~ 


SELECT *  from Customer~ 


UPDATE customer  SET info = 
'<customerinfo xmlns="http://posample.org" Cid="1002"> 
< name>Jim Noodle</ name
<addr country="Canada"> 
<street>1150 Maple Drive</street> 
<city>Newtown</city> 
<prov-state>Ontario</prov-state> 
<pcode-zip>Z9Z 2P2</pcode-zip> 
</addr> 
<phone type=" work">905-555-7258</phone> 
</customerinfo>' 
WHERE XMLEXISTS ( 
' declare  default element namespace "http://posample.org"; 
$doc/customerinfo[@Cid = 1002]' 
passing INFO  as "doc")~ 


DELETE  FROM Customer 
WHERE XMLEXISTS ( 
' declare  default element namespace "http://posample.org"; 
$doc/customerinfo[@Cid = 1003]' 
passing INFO  as "doc")~ 

SELECT  count(*)  from Customer~ 

SELECT XMLQUERY ( 
' declare  default element namespace "http://posample.org"; 
for $d  in $doc/customerinfo 
return <out>{$d/ name}</out>' 
passing INFO  as "doc") 
FROM Customer  as c 
WHERE XMLEXISTS (' declare  default element namespace "http://posample.org"; 
$i/customerinfo/addr[city="Toronto"]' passing c.INFO  as "i")~ 

UPDATE COMMAND OPTIONS USING i  ON

-- 不使用SQL的情况下检索INFO列中所有的XML文档 
XQUERY db2-fn:xmlcolumn ( 'CUSTOMER.INFO')~ 
-- 相当于 
SELECT Info  FROM Customer~ 

-- 全查询 
XQUERY db2-fn:sqlquery ( 'SELECT Info FROM Customer')~ 

-- 检索和过滤XML值 
XQUERY  declare  default element namespace "http://posample.org"; 
for $d  in db2-fn:xmlcolumn( 'CUSTOMER.INFO')/customerinfo 
where $d/addr/city="Toronto" 
return <out>{$d/ name}</out>~ 

XQUERY  declare  default element namespace "http://posample.org"; 
for $d  in db2-fn:sqlquery( 
' SELECT INFO 
FROM CUSTOMER 
WHERE Cid < 2000')/customerinfo 
where $d/addr/city="Toronto" 
return <out>{$d/ name}</out>~ 

-- XML模式注册 
REGISTER XMLSCHEMA  'http://posample.org' 
FROM  'file:///<C:/>customer.xsd'  AS posample.customer COMPLETE~ 

REGISTER XMLSCHEMA [url]http://posample.org/product.xsd[/url]  FROM product.xsd \ 
AS myschema.product 
COMPLETE XMLSCHEMA myschema.product~ 

REGISTER XMLSCHEMA [url]http://posample.org/product.xsd[/url]  FROM product.xsd \ 
AS myschema.product COMPLETE~ 

customer.xsd 
<?xml version="1.0"?> 
<xs: schema targetNamespace="http://podemo.org" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> 
  <xs:element  name="customerinfo"> 
    <xs:complexType> 
      <xs:sequence> 
        <xs:element  name=" name" type="xs:string" minOccurs="1" /> 
        <xs:element  name="addr" minOccurs="1" maxOccurs="unbounded"> 
          <xs:complexType> 
            <xs:sequence> 
              <xs:element  name="street" type="xs:string" minOccurs="1" /> 
              <xs:element  name="city" type="xs:string" minOccurs="1" /> 
              <xs:element  name="prov-state" type="xs:string" minOccurs="1" /> 
              <xs:element  name="pcode-zip" type="xs:string" minOccurs="1" /> 
            </xs:sequence> 
            <xs:attribute  name="country" type="xs:string" /> 
          </xs:complexType> 
        </xs:element> 
        <xs:element  name="phone" nillable="true" minOccurs="0" maxOccurs="unbounded"> 
          <xs:complexType> 
            <xs:simpleContent> 
              <xs:extension base="xs:string"> 
                <xs:attribute  name="type" form="unqualified" type="xs:string" /> 
              </xs:extension> 
            </xs:simpleContent> 
          </xs:complexType> 
        </xs:element> 
        <xs:element  name="assistant" minOccurs="0" maxOccurs="unbounded"> 
          <xs:complexType> 
            <xs:sequence> 
              <xs:element  name=" name" type="xs:string" minOccurs="0" /> 
              <xs:element  name="phone" nillable="true" minOccurs="0" maxOccurs="unbounded"> 
                <xs:complexType> 
                  <xs:simpleContent  > 
                    <xs:extension base="xs:string"> 
                      <xs:attribute  name="type" type="xs:string" /> 
                    </xs:extension> 
                  </xs:simpleContent> 
                </xs:complexType> 
              </xs:element> 
            </xs:sequence> 
          </xs:complexType> 
        </xs:element> 
      </xs:sequence> 
      <xs:attribute  name="Cid" type="xs:string" /> 
    </xs:complexType> 
  </xs:element> 
</xs: schema


INSERT  INTO Customer(Cid, Info)  VALUES (1003, XMLVALIDATE (XMLPARSE (DOCUMENT 
'<customerinfo xmlns="http://posample.org" Cid="1003"> 
< name>Robert Shoemaker</ name
<addr country="Canada"> 
<street>1596 Baseline</street> 
<city>Aurora</city> 
<prov-state>Ontario</prov-state> 
<pcode-zip>N8X 7F8</pcode-zip> 
</addr> 
<phone type=" work">905-555-7258</phone> 
<phone type="home">416-555-2937</phone> 
<phone type="cell">905-555-8743</phone> 
<phone type="cottage">613-555-3278</phone> 
</customerinfo>' PRESERVE WHITESPACE ) 
ACCORDING  TO XMLSCHEMA ID posample.customer ))~ 

CREATE  TABLE Customer (Cid  BIGINT  NOT  NULL  PRIMARY  KEY
Info XML, 
History XML) 

CREATE  TABLE MyCustomer  LIKE Customer; 
ALTER  TABLE MyCustomer  ADD  COLUMN Preferences XML; 

JDBC插入XML 
PreparedStatement insertStmt =  null
String sqls =  null
int cid = 1015; 
sqls = " INSERT  INTO MyCustomer (Cid, Info)  VALUES (?, ?)"; 
insertStmt = conn.prepareStatement(sqls); 
insertStmt.setInt(1, cid); 
File  file = new  File("c6.xml"); 
insertStmt.setBinaryStream(2, new FileInputStream( file), ( int) file.length()); 
insertStmt.executeUpdate(); 

JDBC更新XML 
PreparedStatement updateStmt =  null
String sqls =  null
int cid = 1004; 
sqls = " UPDATE MyCustomer  SET Info=?  WHERE Cid=?"; 
updateStmt = conn.prepareStatement(sqls); 
updateStmt.setInt(1, cid); 
File  file = new  File("c7.xml"); 
updateStmt.setBinaryStream(2, new FileInputStream( file), ( int) file.length()); 
updateStmt.executeUpdate(); 

-- 删除数据 
DELETE  FROM MyCustomer 
WHERE XMLEXISTS (' declare  default element namespace "http://posample.org"; 
$d//addr[city="Markham"]' passing INFO  as "d")~ 

-- XML列创建触发器  
CREATE  TRIGGER UPDAFTR 
AFTER  UPDATE  OF Info 
ON MyCustomer 
REFERENCING NEW  AS N 
FOR EACH ROW 
BEGIN ATOMIC 
INSERT  INTO CustLog  VALUES(N.CID,  CURRENT  TIMESTAMP'Update'); 
END

CREATE  TRIGGER INSAFTR 
AFTER  INSERT  ON Customer 
REFERENCING NEW  AS N 
FOR EACH ROW 
BEGIN ATOMIC 
INSERT  INTO CustLog  VALUES(N.CID,  CURRENT  TIMESTAMP'Insert'); 
END

-- XML解析 
INSERT  INTO MyCustomer (Cid, Info) 
VALUES (?, xmlparse(document  cast(?  as clob(1k)) preserve whitespace))~ 

-- 在XQuery中,查询可以调用下列函数之一来获取DB2数据库中的输入XML数据:db2-fn:sqlquery 和 db2-fn:xmlcolumn。db2-fn:xmlcolumn函数将检索整个XML列,而db2-fn:sqlquery将检索基于SQL查询的XML值。 

db2-fn:xmlcolumn( 'BUSINESS.ORDERS.PURCHASE_ORDER')/shipping_address/city 

db2-fn:sqlquery(" 
SELECT purchase_order  FROM business.orders 
WHERE ship_date =  '2005-06-15' ")/shipping_address/city 

SQL是一种不区分大小写的语言 
XQuery是一种区分大小写的语言 

函数 
XMLQUERY 
XMLTABLE 
谓词 
XMLEXISTS 

SELECT XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d/customerinfo/phone' passing INFO  as "d") 
FROM CUSTOMER~ 

VALUES (XMLQUERY (' declare  default element namespace "http://posample.org"; 
db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo/phone'))~ 

SELECT Cid, XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d//addr[city="Aurora"]' passing INFO  as "d")  AS ADDRESS 
FROM CUSTOMER~ 


SELECT Cid, XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d/customerinfo/addr' passing c.INFO  as "d") 
FROM Customer  as c 
WHERE XMLEXISTS (' declare  default element namespace "http://posample.org"; 
$d//addr[city="Aurora"]' passing c.INFO  as "d")~ 

SELECT R.Pid 
FROM PURCHASEORDER P, PRODUCT R 
WHERE R. NAME = 
XMLCAST( XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d/PurchaseOrder/itemlist/item/product/ name
PASSING P.PORDER  AS "d")  AS  VARCHAR(128))~ 


SELECT Pid 
FROM PRODUCT 
ORDER  BY XMLCAST(XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d/product/description/ name
PASSING DESCRIPTION  AS "d")  AS  VARCHAR(128))~ 

SELECT X.* 
FROM XMLTABLE (xmlnamespaces ( DEFAULT "http://posample.org"), 
'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo' 
COLUMNS "CUSTNAME"  CHAR(30) PATH  'name'
"PHONENUM" XML PATH  'phone'
as X~ 

SELECT X.* 
FROM XMLTABLE (xmlnamespaces ( DEFAULT "http://posample.org"), 
'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo' 
COLUMNS "CUSTNAME"  CHAR(30) PATH  'name'
"PHONENUM" XML PATH  'phone'
as X 
ORDER  BY X.CUSTNAME~ 

INSERT  INTO CUSTPHONE 
SELECT X.* 
FROM XMLTABLE (XMLNAMESPACES ( DEFAULT  'http://posample.org'), 
'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo' 
COLUMNS 
"CUSTNAME"  CHAR(30) PATH  'name'
"PHONENUM" XML PATH  'document{<allphones>{phone}</allphones>}' 
) as X~ 

SELECT X.* 
FROM CUSTOMER C, XMLTABLE (xmlnamespaces ( DEFAULT  'http://posample.org'), 
'$cust/customerinfo/phone' PASSING C.INFO  as "cust" 
COLUMNS "CUSTNAME"  CHAR(30) PATH  '../name'
"PHONETYPE"  CHAR(30) PATH  '@type'
"PHONENUM"  CHAR(15) PATH  '.' 
) as X~ 

SELECT X.*  FROM CUSTOMER C, XMLTABLE (xmlnamespaces ( DEFAULT  'http://posample.org'), 
'$cust/customerinfo/phone' PASSING C.INFO  as "cust" 
COLUMNS "CUSTNAME"  CHAR(30) PATH  '../name'
"PHONETYPE"  CHAR(30) PATH  '@type'
"PHONENUM" XML PATH  '.' )  as X~ 


SELECT Cid 
FROM CUSTOMER 
WHERE XMLEXISTS (' declare  default element namespace "http://posample.org"; 
$d//addr[city="Toronto"]' passing INFO  as "d")~ 

SELECT * 
FROM CUSTOMER 
WHERE XMLEXISTS (' declare  default element namespace "http://posample.org"; 
$d/customerinfo[@Cid=1000]' passing INFO  as "d")~ 

SELECT * 
FROM CUSTOMER 
WHERE XMLEXISTS (' declare  default element namespace "http://posample.org"; 
$d/customerinfo/@Cid=1000' passing INFO  as "d")~ 


CREATE  TABLE mytable (id  BIGINT, xmlcol XML)~ 

CREATE  INDEX myidx  ON mytable(xmlcol) 
GENERATE  KEY USING XMLPATTERN  '//text()'  AS SQL  VARCHAR(255)~ 

SELECT xmlcol  FROM mytable 
WHERE XMLEXISTS( '$doc/CUSTOMER/ORDERS/ORDERKEY/text()="A512" ' 
PASSING xmlcol  AS "doc")~ 

SELECT xmlcol  FROM mytable 
WHERE XMLEXISTS( '$doc/CUSTOMER[ORDERS/ORDERKEY/text()="A512"] ' 
PASSING xmlcol  AS "doc")~ 

-- XML数据类型转换 
SELECT XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d/customerinfo/addr' passing c.INFO  as "d") 
FROM Customer  as c 
WHERE XMLEXISTS(' declare  default element namespace "http://posample.org"; 
$d//addr[city=$cityName]' 
passing c.INFO  as "d", 
'Aurora'  AS "cityName")~ 

SELECT XMLQUERY (' declare  default element namespace "http://posample.org"; 
$d/customerinfo/addr' passing c.INFO  as "d") 
FROM Customer  as c 
WHERE XMLEXISTS(' declare  default element namespace "http://posample.org"; 
$d//addr[city=$cityName]' 
passing c.INFO  as "d", 
CAST (?  AS  VARCHAR(128))  AS "cityName")~ 

SELECT companydocs  FROM companyinfo 
WHERE XMLEXISTS( '$x/company/emp[@salary > 35000]' 
PASSING companydocs  AS "x")~ 

CREATE  INDEX empindex  on companyinfo(companydocs) 
GENERATE  KEY USING XMLPATTERN  '//@salary'  AS SQL  DOUBLE

CREATE  INDEX empindex  on companyinfo(companydocs) 
GENERATE  KEY USING XMLPATTERN  '/company/emp/@salary' 
AS SQL  DOUBLE

SELECT companydocs  FROM companyinfo 
WHERE XMLEXISTS( '$x/company/emp[@id="31664"]' 
PASSING companydocs  AS "x")~ 

SELECT companydocs  FROM companyinfo 
WHERE XMLEXISTS('$x/company/emp/dept[@id="K55"] 
PASSING companydocs  AS "x")~
 
时间很紧,学习周期为90分钟,大部分代码没有注释,运行下看看,再参考DB2官方文档看明白应该不成问题 。
 
 
SELECT X.*  FROM WSYW_QYDJ  AS A, 
    XMLTABLE( '$nr/MainBody/DJ_ZT' passing A.XMLNR  as "nr" columns 
  "BS"  VARCHAR(24) PATH  'BS'
  "MC"  VARCHAR(240) PATH  'MC'
  "ZH"  VARCHAR(100) PATH  'ZH'
  "XZQH_DM"  VARCHAR(24) PATH  'XZQH_DM'
  "FDDBR"  VARCHAR(100) PATH  'FDDBR'
  "JYDZ"  VARCHAR(240) PATH  'JYDZ'
  "QYLX_ZL"  VARCHAR(4) PATH  'QYLX_ZL'
  "QYLX_XL"  VARCHAR(4) PATH  'QYLX_XL'
  "CJRQ"  VARCHAR(64) PATH  'CJRQ'AS X 
WHERE A.UHID = ?  AND A.ZT = ?
 
 
 

本文转自 leizhimin 51CTO博客,原文链接:http://blog.51cto.com/lavasoft/66553,如需转载请自行联系原作者
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值