PureXML using DB2 Express-C 9.7

/*
　　1.測試 DB2 Express-C 9.7 pureXML
　　2.兩個 xml 資料之間的 join
　　3.一個 xml document 裡有多筆資料時的資料呈現
　　p.s　程式中＜＞及縮排的空白為全形，使用前須先轉為single-byte
*/

-- 建立地區xsd,有地區代碼與地區英文名稱

＜?xml version="1.0" encoding="UTF-8"?＞
＜xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"＞
＜xs:element name="region"＞
　＜xs:complexType＞
　　＜xs:sequence＞
　　　＜xs:element name="regno" type="xs:string"/＞
　　　＜xs:element name="regename" type="xs:string"/＞
　　＜/xs:sequence＞
　＜/xs:complexType＞
＜/xs:element＞
＜/xs:schema＞

-- 註冊地區xsd

register xmlschema 'http://localhost/region/region.xsd' from c:\region.xsd as xsd_region complete
DB20000I REGISTER XMLSCHEMA 指令已順利完成。

-- p.s 從register中移除xsd的語法如下

drop xsrobject xsd_region


-- 由xsrobjects看註冊資料是否完整
select substr(targetnamespace,1,30),substr(schemalocation,1,30)
from syscat.xsrobjects where objectname = 'XSD_REGION'

1　　　　　　　　　　　　　　　　　2
------------------------------ ------------------------------
http://localhost/region　　　　　http://localhost/region/region

　　已選取 1 個記錄。

-- 建立地區table,　regioninfo為xml格式

create table region
(
　id integer,
　regioninfo xml
)
DB20000I SQL 指令已順利完成。

-- 將地區xml資料寫成.sql檔,名稱為region.sql.格式必須符合xsd的定義

insert into region values
(1,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Northern＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(2,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞2＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Central＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(3,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞3＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Eastern＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(4,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞4＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Western＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));

insert into region values
(5,xmlvalidate(xmlparse(document '＜region＞
　　　　　　　　　　　　　　　　　　　＜regno＞5＜/regno＞
　　　　　　　　　　　　　　　　　　　＜regename＞Southern＜/regename＞
　　　　　　　　　　　　　　　　　　　＜/region＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_REGION));


-- 執行region.sql將資料新增進table region

C:\>db2 -tf region.sql
DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

-- 使用xml function查看結果,並以table方式呈現

select　regno,regename
from　　region,
　　　　xmltable('for $reg in $d/region return $reg'
　　　　　　　　　passing region.regioninfo as "d"
　　　　　　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　　　　　　　　　　regename varchar(20) path 'regename/text()') t

REGNO　REGENAME
-----　--------------------
1　　　Northern
2　　　Central
3　　　Eastern
4　　　Western
5　　　Southern

　　已選取 5 個記錄。

-- 建立銷售記錄xsd,有地區代碼、銷售日期、產品名稱、價格與數量

＜?xml version="1.0" encoding="UTF-8"?＞
＜xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"＞
＜xs:element name="sales"＞
　＜xs:complexType＞
　　＜xs:sequence＞
　　　＜xs:element name="regno" type="xs:string"/＞
　　　＜xs:element name="dt" type="xs:integer"/＞
　　　＜xs:element name="product" type="xs:string"/＞
　　　＜xs:element name="prize" type="xs:decimal"/＞
　　　＜xs:element name="quantity" type="xs:integer"/＞
　　＜/xs:sequence＞
　＜/xs:complexType＞
＜/xs:element＞
＜/xs:schema＞


-- 註冊銷售記錄xsd

register xmlschema 'http://localhost/sales/sales.xsd' from c:\sales.xsd as xsd_sales complete
DB20000I REGISTER XMLSCHEMA 指令已順利完成。

-- 建立銷售記錄table,　salesrpt為xml格式

create table sales
(
　　sno integer,
　　salesrpt xml
)
DB20000I SQL 指令已順利完成。

-- 將銷售記錄xml資料寫成.sql檔,名稱為sales.sql

insert into sales values
(1,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091011＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞12.55＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞10＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(2,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞3＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091011＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞bangles＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞1000＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞1＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(3,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞2＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091015＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring33＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞50＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞6＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(4,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091105＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞13＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞10＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(5,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞1＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091109＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞13.52＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞10＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));

insert into sales values
(6,xmlvalidate(xmlparse(document '＜sales＞
　　　　　　　　　　　　　　　　　　　＜regno＞3＜/regno＞
　　　　　　　　　　　　　　　　　　　＜dt＞20091110＜/dt＞
　　　　　　　　　　　　　　　　　　　＜product＞ring99＜/product＞
　　　　　　　　　　　　　　　　　　　＜prize＞13.5＜/prize＞
　　　　　　　　　　　　　　　　　　　＜quantity＞5＜/quantity＞
　　　　　　　　　　　　　　　　　　　＜/sales＞'
　　　　　　　　　　　　) according to xmlschema ID XSD_SALES));


-- 執行sales.sql將資料新增進table sales

C:\>db2 -tf sales.sql
DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

DB20000I SQL 指令已順利完成。

-- 使用xml function查看結果,並以table方式呈現

select　regno,dt,product,prize,quantity
from　　sales,
　　　　xmltable('for $sal in $d/sales return $sal'
　　　　passing sales.salesrpt as "d"
　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　dt integer path 'dt/text()',
　　　　product varchar(10) path 'product/text()',
　　　　prize decimal(10,2) path 'prize/text()',
　　　　quantity integer path 'quantity/text()') t2

REGNO DT　　　　　PRODUCT　　　　PRIZE　　　　QUANTITY
----- ----------- ---------- ------------ -----------
1　　　　20091011 ring99　　　　　　12.55　　　　　　　10
3　　　　20091011 bangles　　　　 1000.00　　　　　　　 1
2　　　　20091015 ring33　　　　　　50.00　　　　　 　　6
1　　　　20091105 ring99　　　　　　13.00　　　　　　　10
1　　　　20091109 ring99　　　　　　13.52　　　　　　　10
3　　　　20091110 ring99　　　　　　13.50　　　　　 　　5

　　已選取 6 個記錄。


-- 地區xml資料與銷售記錄xml資料相互join，混用sql語法

select t3.regno,regename,dt,product,prize,quantity
from
(select　regno,regename
from　　region,
　　　　xmltable('for $reg in $d/region return $reg'
　　　　　　　　　passing region.regioninfo as "d"
　　　　　　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　　　　　　　　　　regename varchar(20) path 'regename/text()') t) t1,
(select　regno,dt,product,prize,quantity
from　　sales,
　　　　xmltable('for $sal in $d/sales return $sal'
　　　　　　　　　passing sales.salesrpt as "d"
　　　　　　　　　COLUMNS regno varchar(5) path 'regno/text()',
　　　　　　　　　　　　　dt integer path 'dt/text()',
　　　　　　　　　　　　　product varchar(10) path 'product/text()',
　　　　　　　　　　　　　prize decimal(10,2) path 'prize/text()',
　　　　　　　　　　　　　quantity integer path 'quantity/text()') t2) t3
where t1.regno = t3.regno

REGNO　REGENAME　　　　　　DT　　　　　PRODUCT　　　　PRIZE　　　　　QUANTITY
-----　------------------- ----------- ---------- ------------ -----------
1　　　Northern　　　　　　　　20091011 ring99　　　　　　12.55　　　　　　　10
3　　　Eastern　　　　　　　　 20091011 bangles　　　　 1000.00　　　　　　　 1
2　　　Central　　　　　　　　 20091015 ring33　　　　　　50.00　　　　　 　　6
1　　　Northern　　　　　　　　20091105 ring99　　　　　　13.00　　　　　　　10
1　　　Northern　　　　　　　　20091109 ring99　　　　　　13.52　　　　　　　10
3　　　Eastern　　　　　　　　 20091110 ring99　　　　　　13.50　　　　　　　 5

　　已選取 6 個記錄。

-- 另外追加測試xml含有多筆資料時的狀況
-- 建立table City,欄位為城市代碼,城市名稱,行政區資料

create table City
(
　cityid　　char(2),
　cityname　varchar(32),
　cityinfo　xml
) in userspace1;

insert into city values
('01','台北市',
'＜city＞
　＜county＞
　　＜id＞0101＜/id＞
　　＜name＞松山區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0102＜/id＞
　　＜name＞大安區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0109＜/id＞
　　＜name＞大同區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0110＜/id＞
　　＜name＞中山區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0111＜/id＞
　　＜name＞內湖區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0112＜/id＞
　　＜name＞南港區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0113＜/id＞
　　＜name＞木柵區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0114＜/id＞
　　＜name＞景美區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0115＜/id＞
　　＜name＞士林區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0116＜/id＞
　　＜name＞北投區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0117＜/id＞
　　＜name＞信義區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0118＜/id＞
　　＜name＞中正區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0119＜/id＞
　　＜name＞萬華區＜/name＞
　＜/county＞
　＜county＞
　　＜id＞0120＜/id＞
　　＜name＞文山區＜/name＞
　＜/county＞
＜/city＞');

-- 行政區xml欄位原為多筆資料時的資料呈現

select　cityid,cityname,id,name
from　　city,xmltable('for $cty in $d/city/county return $cty'
　　　　　　　　　　　passing city.cityinfo as "d"
　　　　　　　　　　　columns id varchar(4) path 'id/text()',
　　　　　　　　　　　name varchar(16) path 'name/text()') t

CITYID　CITYNAME　　　　　　　　　　　　ID　　NAME
------　--------------------------- ----　----------------
01　　　台北市　　　　　　　　　　　　　0101　松山區
01　　　台北市　　　　　　　　　　　　　0102　大安區
01　　　台北市　　　　　　　　　　　　　0109　大同區
01　　　台北市　　　　　　　　　　　　　0110　中山區
01　　　台北市　　　　　　　　　　　　　0111　內湖區
01　　　台北市　　　　　　　　　　　　　0112　南港區
01　　　台北市　　　　　　　　　　　　　0113　木柵區
01　　　台北市　　　　　　　　　　　　　0114　景美區
01　　　台北市　　　　　　　　　　　　　0115　士林區
01　　　台北市　　　　　　　　　　　　　0116　北投區
01　　　台北市　　　　　　　　　　　　　0117　信義區
01　　　台北市　　　　　　　　　　　　　0118　中正區
01　　　台北市　　　　　　　　　　　　　0119　萬華區
01　　　台北市　　　　　　　　　　　　　0120　文山區

　　已選取 14 個記錄。


Equivalent of Oracle's Ref Cursor in DB2

/*
以 DB2 的語法來實現像 Oracle Ref cursor的做法:
　宣告：　　DECLARE cursorname CURSOR [WITH HOLD] WITH RETURN FOR CUR1;
　程式主體：PREPARE CUR1 FROM dynamic string;
　開啟：　　cursor　OPEN cursorname;

My scenario:
1.　建立一個測試 table, 存放日期,年,月,日
2.　運用Session global variable存放年份,做為dynamic string裡的年度條件
3.　DB2 procedure 裡傳回單一resultset及cursor的語法
4.　測試結果
*/
/*　　建立測試table及運用CTE與Recursive SQL新增日期資料　　*/

CREATE TABLE T3
(
　　DT　　　INTEGER,
　　YEAR_N　INTEGER,
　　MONTH_N　INTEGER,
　　DAY_N　　INTEGER
) IN USERSPACE1;

INSERT INTO T3
WITH N(COL1)
AS
(SELECT DATE('2000-01-01') FROM SYSIBM.DUAL
UNION ALL
SELECT COL1 + 1 DAY FROM N
WHERE YEAR(COL1 + 1 DAY) <= 2010
)
SELECT INT(COL1),YEAR(COL1),MONTH(COL1),DAY(COL1) FROM N

/*　　建立session global variable　　*/

CREATE VARIABLE ADMINISTRATROR.YEAR_SET INTEGER

-- 給定值為2009
SET ADMINISTRATROR.YEAR_SET = 2009


/*　　附帶說明：只有相同 session 才看得到 session global variable 設定值　　
　　　測試再另使用db2cmd去看這個year_set
*/

C:\>db2 connect to orion

　　資料庫連線資訊

　資料庫伺服器　　　　　= DB2/NT 9.7.0
　SQL 授權 ID　　　　　　= ADMINIST...
　本端資料庫別名　　　　= ORION

C:\>db2 values administrator.year_set

1
-----------
　　　　　-

　　已選取 1 個記錄。

/*　　建立 procedure sp_test 傳回一個 result　　*/

CREATE PROCEDURE SP_TEST
RESULT SETS 1
LANGUAGE SQL
BEGIN
　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　DECLARE retcode INTEGER DEFAULT 0;
　　DECLARE szSql VARCHAR(128);

　　DECLARE TIMECUR CURSOR WITH HOLD WITH RETURN FOR CUR1;

　　DECLARE CONTINUE HANDLER FOR　SQLEXCEPTION,
　　　　　　　　　　　　　　　　　SQLWARNING,
　　　　　　　　　　　　　　　　　NOT FOUND
　　SET retcode = SQLCODE;

　　SET szSql = 'SELECT DT FROM T3 WHERE YEAR_N = YEAR_SET';

　　PREPARE CUR1 FROM szSql;
　　OPEN TIMECUR;
END

/*　　測試(需傳回所有2009年的日期)　　*/

C:\>db2 call sp_test

　結果集 1
　--------------

　DT
　-----------
　　　20090101
　　　20090102
　　　20090103
　　　20090104
　　　20090105
　　　20090106
　　　20090107
　　　20090108
　　　20090109
　　　20090110
　　　20090111
　　　.
　　　.
　　　.