/*
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 個記錄。
