IBM DB2 9.7 User Manual

IBM DB2 9.7 Linux f"UNIX fM Windows f
򔻐򗗠򙳰
pureXML 8O
S151-1180-00
IBM DB2 9.7 Linux f"UNIX fM Windows f
򔻐򗗠򙳰
pureXML 8O
S151-1180-00
"b
9CKE"0d'VDz70,kHDAZ 447 3D=< E, :yw;BD#fE"#
^)fyw
zIZ_r(}1XD IBM zm&): IBM vfo#
v *Z_):
vfo,k*A IBM vfoPD,x7*:www.ibm.com/shop/publications/order
v *iR1XD IBM zm&,k*A IBM +r*5K?<,x7*:www.ibm.com/planetwide
*S@zrSCsD DB2 P!Mz[?): DB2 vfo,kBg 1-800-IBM-4YOU(426-4968)#
1zr IBM "ME"1,4,bZh IBM @;^~D(&T
|O*J1R;aTzlINN0lD==9CrV"C
E"#
© Copyright International Business Machines Corporation 2006, 2009.
?<
XZ>i ..............vii
Z 1 B pureXML Ev - DB2 w*
XML }]b .............1
XML }]`M..............3
XML dkMdvEv ...........3
HO XML #MMX5#M .........7
XQuery M XPath }]#M .........8
rPMn...............8
-S5 ...............9
ZccNa9 .............10
ZctT ..............11
ZcV` ..............12
ZcDD53r ............14
Zcj6 ..............14
ZcD`M5MV{.5 .........15
'V XML D$_ ............15
pureXML D*O'V ...........17
pureXML D4F
XZ XML 'VDDB...........17
MB~"<'V .......17
Z 2 B pureXML LL ........19
NL 1:4(ITf" XML }]D DB2 }]bM
m .................20
NL 2:4(yZ XML }]Dw} ......20
NL 3:+ XML D5ek= XML `MPP ...21 NL 4:|Bf"Z XML PPD XML D5 ...22
NL 5:>} XML }] ..........23
NL 6:i/ XML }] ..........24
NL 7:kT XML #=i$ XML D
NL 8:9C XSLT y=mxPd; ......29
5 ....27
Z 3 B XML f"w .........33
XML f"Ts .............33
XML y>mPf"w ...........33
XML D5Df"*s ...........34
Z 4 B ek XML }] .......37
4(_P XML PDm...........37
+ XML PmSAVPm ..........38
ek= XML PP ............38
XML bv ...............39
XML }]j{T ............43
XML PDli<x ...........43
XML }]D%"w&m .........45
XML i$ ..............46
XSR_GET_PARSING_DIAGNOSTICS f"}L ..49
T>j8D XML bvMi$ms ......51
v?Dms{"'VD ErrorLog XML #=(e 53
ZG Unicode }]bP9C
XML.......56
Z 5 B i/ XML }] .......61
XQuery ri ..............61
9C XQuery /}lw DB2 }] .......62
9C SQL i/ XML }]ri ........63
XQuery k SQL DHO ..........64
HOCZi/ XML }]D=(........64
8( XML {FUd ...........65
>}:|D*XD{FUd0: ........67
XMLQUERY /}Ev ...........69
XMLQUERY 5XDGUrP .......69
XMLQUERY 5XDUrP ........70
+ XMLQUERY a{D}]`M*;*
`M ................71
}]`M.dD?F*; .........72
XMLQUERY.............79
XMLTABLE /}Ev ...........82
>}:ek XMLTABLE P5XD5 .....83
>}:9C XMLTABLE T3nD?v5}5X;
P ................84
>}:9C XMLTABLE &m XML D5P`v
wy|,D*X ............85
>}:9C XMLTABLE &mVc}] ....87
XMLTABLE.............89
i/ XML }]1D XMLEXISTS =J
XMLEXISTS =JC> ..........93
XMLEXISTS =J ...........94
Z SQL odk XQuery mo=.d+]N} ...96
+]A XMLEXISTS M XMLQUERY D#?MN
}jG ...............96
9C XMLEXISTS"XMLQUERY r XMLTABLE
+]Dr%P{ ............97
+N}S XQuery +]A SQL.......99
9C XQuery lw}]...........99
CZ%dw}ki/D<rEv .......101
w}(eD^F............102
8( text() Zc1
Vf5D}]`M ...........105
,S=J*; ............105
#}i/s5 ............107
XML D5PD+D>Qw .........108
+ XML PPD}]lwAOgf>D DB2 M'z 108
CZ9l XML 5D SQL/XML "</} ....109
"< XML 5D>} ..........110
9C XSLT y=mxPd; .......114
SQL/XML "</}PDXbV{&
XML rP/.............123
f"Mlws XML D5PDnp .....125
i5 XML D5D}]`M ........126
D"bBn .......104
G XML
.....92
m ....122
Z 6 B * XML }]("w} ....127
w} XML #=mo= ..........128
© Copyright IBM Corp. 2006, 2009 iii
XML {FUdyw ...........129
kw} XML #=mo=X*D}]`M ....130
yZ XML }]Dw}D}]`M*; .....131
^' XML 5 ............132
D5b\r CREATE INDEX od'\ ....134
XZ*;*w} XML }]`MD\am ...134
XML #=Mw}|zI ..........135
UNIQUE X|Voe ...........136
k* XML }]("w}`
yZ XML }]D_-w}Momw} ....137
k XML PX*Dd{}]bTs .....138
XB4(yZ XML }]Dw} ......139
CREATE INDEX ............139
kTyZ XML }]Dw}Dy>i/ .....156
TyZ XML }]Dw}D^F .......158
#{ XML ("w}Jb .........160
oO INSERT r UPDATE od"
SQL20305N {" ...........160
oO CREATE INDEX odTndDm"vD
SQL20306N {" ...........162
X*D}]bTs ...137
vD
Z 7 B |B XML }] .......165
Zd;mo=P9C|BYw ........166
9Cd{mPDE"|B XML D5 ......169
SmP>} XML }] ..........170
Z 8 B XML #=f"b.......171
XSR Ts ..............171
XSR Ts"a .............171
(}f"}L"a XSR Ts .......172
(}|nP&mw"a XSR Ts ......173
T XML #="aM>}D Java 'V ....174
Dd"aD XSR Ts ..........176
]x XML #= ...........176
]x XML #=Df]T*s .......176
=8:]x XML #= .........183
i! XML #=E"D>} .........185
P>Qr XSR
lwQr XSR "aD XML #=DyPiI?
V ................185
lw XML D5D XML #= .......186
"aD XML #= .....185
Z 9 B XML }]F/ .......187
PXF/ XML }]DX*"bBn ......188
i/M XPath }]#M ..........189
<kM<v1D LOB M XML D~P* ....189
XML }]5w{ ............190
<v XML }] ............191
<k XML }] ............193
0k XML }] ............194
bv0k XML }]1"zD("w}ms . . 195
Z 10 B &CLr`LoT'V ....203
CLI.................204
CLI &CLrPD XML }]&m - Ev . . 204
CLI &CLrPD XML PekM|B ....205
Z CLI &CLrPlw XML }] .....206
|D CLI &CLrPD1! XML `M&m . . 206
6k= SQL..............207
Z6k= SQL &CLrPyw XML wd? 207 >}:}C6k= SQL &CLrPD XML w
d? ...............208
4P6k=
XZ9C XML M XQuery *"6k= SQL &
CLrD(i ............211
j6 SQLDA PD XML 5 .......211
Java ................211
JDBC...............211
SQLJ...............219
PHP ................223
IBM }]~qwD PHP &CLr*" ....223
9C PHP &CLrlw XML }] .....224
PHP BXM`XJ4 ..........224
Perl .................224
pureXML M Perl ...........224
Perl PD}]b,S ..........227
Perl ^F ..............228
}L ................228
SQL }L..............228
SQL /}..............230
b?}L ..............232
}LDT\ .............246
y>&CLr .............251
pureXML y> ............251
pureXML - \my> .........252
pureXML - &CLr*"y> ......254
SQL &CLrPD XQuery mo= 209
Z 11 B XML T\ .........261
pureXML &\?~M}]i/=8 ......261
ZVx}]b73P9C XQuery d;D>} ...261
+QywY1mk XML }]dO9C .....263
+E/<rk XML }]M XQuery mo=dO9
C .................265
9C XML }]DE/<rD>} .....266
CZ pureXML }]f"T\D DMS mUdDW!
n .................271
Z 12 B XML }]`k .......273
Z?`kD XML }] ..........273
f"r+] XML }]1D`k"bBn ....274
+ XML }]dk}]b1D`k"bBn . . 274
S}]bPlw XML }]1D`k"bBn 274
Z}LN}P+] XML }]1D`k"bBn 275 JDBC"SQLJ M .NET &CLrPD XML }]
`k"bBn
XML `kMrP/TZ}]*;D0l.....276
`kiv:+Z?`kD XML }]dk=}]
bP ...............276
`kiv:+b?`kD XML }]dk=}]
bP ...............277
`kiv:(}~=DrP/Yw4lw XML
}] ...............279
............275
iv pureXML 8O
`kiv:9CT=D XMLSERIALIZE 4lw
XML }] .............281
3dZ?`kD XML }]M CCSID .....283
+`k{3dAQf"D XML }]DP'
CCSID ...............283
+ CCSID 3dArP/ XML dv}]D`k
{ ................295
Z 13 B x"MD XML #=Vb ...301
x"MD XML #=VbDEc .......301
9Cx"MD XML #=4Vb XML D5 ...301
"a XML #="TdtCVb ......302
`v XML D5Vb>} ........303
x"MD XML #=VbM]i XML D5 . . 303
{Cx"MD XML #=Vb ........308
CZx"MD#=VbD xdbDecompXML }L . . 309
DECOMPOSEXMLDOCUMENT.......312
x"MD#=Vb
XDB_DECOMP_XML_FROM_QUERY f"}L . . 313
XML Vb"M .............316
XML Vb"M - f6MwCr ......316
XML Vb"M - \a .........317
db2-xdb:defaultSQLSchema Vb"M .....318
db2-xdb:rowSet Vb"M .........319
db2-xdb:table Vb"M .........323
db2-xdb:column Vb"M ........326
db2-xdb:locationPath Vb"M .......328
db2-xdb:expression Vb"M........331
db2-xdb:condition Vb"M ........334
db2-xdb:contentHandling Vb"M ......337
db2-xdb:normalization Vb"M .......341
db2-xdb:order Vb"M .........343
db2-xdb:truncate Vb"M ........345
db2-xdb:rowSetMapping V
db2-xdb:rowSetOperationOrder Vb"M ....350
x"MD XML #=VbDX|V .....351
x"MD XML #=VbPgNNIVba{ ...352
T XML Vba{xPi$DwC .....353
x"MD XML #=VbPT CDATA ?VD&
m ................353
x"MD XML #=VbPDU5MUV{. 354
CZx"MD XML #=V
*xPx"MD XML #=VbxTIzD4S
`MmSD"M............355
Vb&\D XML #=9l(i ......358
x"MD XML #=VbPD3d>} .....359
x"MD XML #=VbPDP/ .....359
Vb"M>}:3dA XML P ......362
Vb"M>}:;v53dA%v Vb"M>}:;v53dA%vmazz`vP 364
Vb"M>}:;v53dA`vm .....366
Vb"M>}:+3dA%vmD`v5xPVi 367 Vb"M>}:+;,OBDPD`v53dA%
vm ...............369
x"MD#=VbD
x"MD XML #=VbD^F .......374
D
b"M ......347
bDKTm .....355
mazz%vP 363
XML #== SQL `Mf]T 370
x"MD XML #=VbDJOoO"bBn ...376
XML Vb"MD#=...........377
Z 14 B T pureXML D^F ....379
T pureXML &\D^F..........379
=< A. `k3d ..........383
+`k{3dAQf"D XML }]DP' CCSID 383 + CCSID 3dArP/ XML dv}]D`k{ 394
=< B. SQL/XML "</} .....399
XMLAGG...............399
XMLATTRIBUTES............400
XMLCOMMENT ............401
XMLCONCAT.............402
XMLDOCUMENT............403
XMLELEMENT .............404
XMLFOREST .............408
XMLGROUP..............411
XMLNAMESPACES ...........413
XMLPI ...............415
XMLROW..............416
XMLTEXT ..............418
XSLTRANSFORM ............419
=< C. XSR f"}LM|n .....423
XSR f"}L .............423
XSR_REGISTER ...........423
XSR_ADDSCHEMADOC.........424
XSR_COMPLETE ...........425
XSR_DTD .............426
XSR_EXTENTITY ...........427
XSR_UPDATE ............428
XSR |n ..............430
REGISTER XMLSCHEMA ........430
ADD XMLSCHEMA DOCUMENT .....431
COMPLETE XMLSCHEMA ........432
REGISTER XSROBJECT .........433
UPDATE XMLSCHEMA .........435
=< D. DB2 <uE"Ev ......437
2=4r PDF q=D DB2 <ub ......437
):!"fD DB2 i. ..........440
S|nP&mwT> SQL 4,oz ......441
CJ;,f>D DB2 E"PD .......441
Z DB2 E"PDPTzDW!oTT>wb ...441 |B20ZzDFczrZ?x~qwOD DB2 E
"PD................442
V/|B20Z
DB2 E"PD .............443
DB2 LL...............444
DB2 JOoOE"............445
unMu~ ..............445
zDFczrZ?x~qwOD
=< E. yw ............447
?< v
w} ...............451
vi
pureXML 8O
XZ>i
pureXML™8OhvgNZ DB2®}]bP9C XML }]#|+f_zPX XML } ]`MM XML f"wD*6,gN9C SQL M XQuery oT49C XML }], T0gN*T\("w} XML }]#d{wb|( pureXML &CLr"9,}]F
/MQ XML }]Vb*`Xq=#
© Copyright IBM Corp. 2006, 2009 vii
viii pureXML 8O
Z 1 B pureXML Ev - DB2 w* XML }]b
pureXML &\?~Jmz+q=<CD XML D5f"Z_P XML }]`MD}]
bmPP#(}+ XML }]f"Z XML PP,}]I#Vd>zVca9N=,x ;G+dw*D>f"r3d*d{}]#M#
r* pureXML }]f"Qj+/I,yTI{CVP DB2
M\mf"D XML }]#
+ XML }]Td>zVca9N=4f"IT9 XML DQw"lwM|B'J| _#XQuery"SQL rOv~_DiOICZi/M|B XML }]#5X XML }] rIC XML Td?D SQL /}(F* SQL/XML /})9\y]
549lr"< XML }]#
}]b~qw&\4CJ
S}]bPlwD
i/M|B
I9CBP=(i/M|Bf"Z XML PPD XML D5:
XQuery
XQuery GCZbM"lwM^Di/ XML }]D(CoT#DB2 }]b~ qwJm1SwC XQuery rS SQL wC XQuery#r* XML }]f"Z DB2 mMS<P,yTa)K;)/},CZ(}1S|{mrS<r(} ( SQL i/S8(DmMS<Pi! XML }]#XQuery 'VwVCZ&m XML }]"|B*XMtT.`D XML TsM9lB XML TsDmo=# XQuery D`LSZa)K`FZ SQL D&\,CZ4Pi/"lwa{#
SQL odM SQL/XML /}
m` SQL od'V XML }
{}]bYw,}g,4(_P XML PDm"+ XML PmSAVPm"4 (yZ XML PDw}"T_P XML PDm4(%"wT0ek"|Br>
} XML D5#DB2 }]b~qw'VD;i SQL/XML /}"mo= 6{C XML }]`M#
]`M#b9Cz\;T XML }]4Pm`#
8
Mf
ITS SQL i/PwC XQuery#ZbVivB,SQL i/IT+}]Ts(
d?DN=+]A XQuery#
&CLr*"
m``LoT(} SQL Mb?}La)KT&CLr*"D'V:
`LoT'V
TBD pureXML &\D&CLr*"'V9C&CLr\;iO XML MX
5}]DCJMf"#BP`LoT'V XML }]`M:
v C r C++(6k= SQL r DB2 CLI)
v COBOL
v Java
v C# M Visual Basic(IBM
v PHP
© Copyright IBM Corp. 2006, 2009 1
(JDBC r SQLJ)
®
}]~qw .NET a)Lr)
v Perl
SQL Mb?}L
(}Z CREATE PROCEDURE N}Xw{P|, XML }]`MN},I+ XML }]+]A SQL }LMb?}L#VPD}L&\'V'F SQL od
5V}L_-w,|Gzzr{C XML 5T0d?PD XML }]5Y1f "w#
\m
pureXML &\a)CZ\m XML D5D URI @5X5Df"b"JmCZ}]b\ mD XML }]F/:
XML #=f"b(XSR)
XML #=f"b(XSR)GCZfEZ&m XML PPf"D XML 5}D 51yhDyP XML $~ Df"b#|f" XML D5P}CD XML #
="DTD Mb?5e#
<k"<vM0k5CLr
<k"<vM0k5CLrQ|B*'V>z XML }]`M#b)5CLr s&m LOB }];y4&m XML }]:b=V`MD}]<f"Z5JD m.b#Q|BD db2Import"db2Export M db2Load API 9a)KCZ<k" <vM0k XML }
f"Z XML PPD XML D5}],b`FZTX5}]D}]F/'V#
]D&CLr*"'V#b)Q|BD5CLrJmF/
T\
9Cf"Z XML PPD XML D51,I9Ca_T\D&\?~:
yZ XML }]Dw}
Tf"Z XML PPD}]a)Kw}'V#9CyZ XML }]Dw}Ia
_kT XML D5"vDi/D'J#kX5w}`F,XML }]w}TP( "w}# +G,
w};GT?VP("w}#(}8( XML #=(|G\^D XPath mo =),I8w*T XML PDD)?V("w}#
E/w |BKE/w,T'VkT XML }]MX5}]T SQL /}"XQuery
M6kK XQuery D SQL/XML /}xPs5#E/wIC(} XML }]T
04TyZ XML }]Dw}D}]yU/D3FE"zIP'Di/4PF .#
5wM Visual Explain
Q|B5wh)M Visual Explain GUI $_,Tc'VCZi/ XML }]D SQL v?&\"'V XQuery mo=# _Db)|BJmzlYi4 DB2 }]b~qwkT XML }]Ti/od
xPs5D==#
|GDxpZZX5w}GT{vP("w},xXML }]
T5wh)M Visual Explain GUI $
$_
/}
2 pureXML 8O
;)$_'V XML }]`M,b)$_|(XFPD"|nP&mw"IBM Data Stu- dio M DB2 Development Add-In for Microsoft
®
Visual Studio .NET#
XML }]`M
x"MD XML #=Vb
pureXML 9z\;+ICVcq=D XML }]w* XML f"MCJ,+P1I\
h*+ XML }]w*X5}]4CJ#x"MD XML #=Vby] XML #=P 8(D"M4VbD5#
K}]`MCZ(emPf" XML 5DP,b)PPf"DyP XML 5XkGq= <CD XML D5#}kK>z XML }]`M\;+q=<CD XML D5f"Z
}]bPd{X5}]T_D>zVcq=P#
9CZ?m>4&m XML 5,Z?m>;GV
O#(}9C XMLSERIALIZE /}r+ XML 5s(A`M* XML"V{.r~x FD&CLrd?,IT+ XML 5d;Im> XML D5DQrP/V{.5#,
y,(}9C XMLPARSE /}r+&CLrV{."~xFr XML &CLr (A XML 5,IT+m> XML D5DV{.5d;* XML 5#Zf0 XML P
D SQL }]|Dod(g INSERT)P,(}9CQekD XMLPARSE /}+m> XML D5DV{.r~xF5d;I XML 5#k&CLrV{.M~xF}]`M
;;1,IT~=bvrrP/
Za9OT}]bPD XML 5Ds!;P^F#+G,k"b,k DB2 }]b~q w;;DQrP/ XML }]Ds!^F* 2GB#
I9C SQL }]Ywodek"|BM>} XML D5#XML #=f"b(XSR)
'V(#Zekr|BZdkT XML
KCZ9lMi/ XML 5T0<vM<k XML }]DzF#ITT XML P(e XML }]w},SxDF XML }]DQwT\#IT(}wV&CLrSZ+mr S<PPD XML }]w*QrP/DV{.}]lw
XML dkMdvEv
DB2 }]b~qwCZ\mX5}]M XML } ] , |a ) K w V=(4 d k M dv XML D5#
{.,"R;\1SkV{.5xPH
`Ms
XML 5#
#=i$ XML D5#DB2 }]b539a)
#
XML D5f"Z(e* XML }]`MDPP#;v XML PPD?;P<f"%v q=<CD XML D5#f"DD5Vc#f,"R#tK XML }]#M;D54f
"*D>
ITZ|,d{`MDP(b)P#fX5}])DmP(e XML P;IT*%vm (e`v XML P#
r3dAm;}]#M#
dk
Z 4 3D< 1 T>KICZ+ XML }]Ek}]b53PDwV=(#
Z 1 B pureXML Ev 3
4 pureXML 8O
<1.CZdk
XML
}]D=(
9CDdk=(!vZ*jIDNq:
ekr|B
I9C INSERT SQL od+q=<CDD5ek= XML PP#g{\;I &VvD5,G45wD5Dq=<C#Z4Pekr|BYw ZdGqi$
XML D5G I!D#g{ 4Pi$,G 4XkWHr XML #=f"b (XSR)"a XML #=#D5G9C UPDATE SQL odr9C XQuery |
Bmo=|BD#
x"MD XML #=Vb
I9Cx"MD XML #=Vb4Vb XML D5PD}],r_+C}]f "ZX5PM XML
PP#Vbay]mSA XML #=D5D"M+}]f
"ZPP#b)"M+ XML D5PD}]3dAmP#
Vb&\y}CD XML #=D5f"Z XML #=f"b(XSR)P#
<k I9C<k5CLr+ XML D5<k= XML PP#Z<k XML D51G
qxP
i$GI!D #g{4 Pi$,G4XkWHr XML #=f"b
(XSR)"ai$D51y9CD XML #=#
XML #=f"b(XSR)"a
XML #=f"b(XSR)f"CZi$rVb XML D5D XML #=#* T@5Zb)#=D XML D54Pd{Nq,(#h*H"
a XML #=#
XML #=G9Cf"}Lr|nr XSR "aD#
dv
Z 6 3D< 2 T>KICZS}]b53Plw XML }]DwV=(#
Z 1 B pureXML Ev 5
6 pureXML 8O
<2.CZdv
XML
}]D=(
9CDdv=(!vZ*jIDNq:
XQuery
XQuery G;VICZZ XML D5ZxPi/DoT#|zcKi/a9d/ G#sD XML }]DX(hs,ki/a9I$bDX5}]G;,D#
XQuery ITT:wCT:,2IT(} XQuery /} db2-fn:xmlcolumn M db2- fn:sqlquery w C
SQL 4 i/ f"Z DB2 } ]b PD XML#db2-
fn:xmlcolumn +lw{v XML P,x db2-fn:sqlquery +lwyZ SQL +i /D XML 5#
SQL 19C SQL +i/4i/ XML }]1,+ZP6pxPi/#rK,Ci
/;\5X{v XML D5;vv9C SQL G;I\5X XML D5|,D
,ND#
*Z XML D5ZxPi/,Xk9C XQuery#IT9C SQL/
XML /} XMLQUERY r XMLTABLE r_9C XMLEXISTS =J"(} wC SQL 4wC XQuery#XMLQUERY /}+ XQuery mo=Da{T XML
rPN=5X#XMLTABLE /}+ XQuery mo=Da{TmDN=5X# XMLEXISTS SQL =J7( XQuery mo=Gqa5XGUrP#
9I9CtI"</}(}f"Z DB2 }]b~qwPD XML }]49l XML 5#9Cb)"</}9lD XML 5;XGq=<CD XML D5#
<v I9C<v5CLrS XML PP<v XML D5#Q<vD XML }]kw
}]D~P|,DQ<vDX5}]f"Z;,;C#PX?vQ<vD XML
D5Dj8E";P1Sf"ZQ<vDw}]D~P#Zw}]D ~P,b )j8E"GI XML }
HO XML #MMX5#M
hF}]b1,h*7(}]|JO XML #M9GX5#M# hFI{C DB2 } ]bDlOXc,4,,1Z;v}]bP'VX5}]M XML }]D\;#
d;>V[bMKb=v#M.dDw*xpT0JCZ?v#MDrX,+9P\
`rXIozz!qnOJD5V#+>V[w*;v<r,KbI\a0lX(5 VDyPrX#
XML }]kX5}].dDw*xp
XML }]GVc}];X5}]C_-X5#Mm>
XML D5TcNa9N=|,PX}]n.dDX5DE"#TZX5#M,
IT(eD(;X5`MG8mMStmX5#
]5w{(XDS)m>D#
XML }]\;Thv;xX5}];\
XML D5;v|,}]
TP;,`MD}]#TZX5#M,}]DZ]Id P(e(e#PPDy P}]XkG`,`MD}] #
XML }]_PL(Er;xX5}];P
TZ XML D5,Y(8(D}]n =(48(D5ZD3r#TZX5}],}GT;Pr`P8( ORDER BY
Sd,qr;\#$PD3r#
,9|,PXCZ5w}]DEnDjG#%vD5I
3rGD5P}]D3r#(#;Pd{
0l}]#M!qDrX
f"D}]V`Iozz7(f"==#}g,g{}]lzGVcRThvD,G 4IT+|f"* XML }]#+G,d{rX2I\a0lzv(*9CD#M#
h*nsinT1
X5mq-G#OqD#M#}g,+;vmf6/* f6/*;vmI\G#'Q#g{-#|D}]hF,G4+|m>* XML
}]GOCD!q#}g,XML #=If1d]x#
}]lwh*nCT\1
rP/MbM XML }]azz;)*z#g{T\HinT| 5}]I\GOCD!q#
m`mr+m`m4r
X*,G4X
}]Tsw*X5}]&m1
g{T}]Dsx&m!vZ*f"ZX5}]bPD}] ,G49CVb+
?V}]w*X5}]f"I\OOJ#+*zVv&m(OLAP)&CZ}
Z 1 B pureXML Ev 7
]VbPD}]MGby;v>}#Kb,g{h*+T XML D5Dd{& mw*;v{e,G4ZbVivB,OOJD=(G+? V}]w*X5}
]f""f"{v XML D5#
}]i~ZcNa9b_Pbe1
}]>mI\PLPDVcq=
)%I\|,?~E#nC+xP?~EDI:)%T XML D5DN=m># +G,?v?~E<P;vk|X*D?~hv#nC +?~hv|(ZX5 mP,r*?~EM?~hv.dDX %^X#
}]tTJCZyP}],rvJCZ;!?V}]1
;)}]/I\Ps?tT,+;P;!?VtTJCZN NX(}]5#} g,Zc[?<P,I\Pm`}]tT,}g,s! "
y="/("g4hsr<Ohs#TZC?<PDN Nx(n,;P;?V tT`X:g4hsTZgbPbe,+TZ:;Pbe#\QCX 5#Mm >MQwbV`MD}],+9C XML #M4m>MQwM`T*]
}]4STk]?.HO_1
Zm`ivB,Y?}]P|,K_Ha9/DE"# 9CX5#Mm>C} ]+f04SDGM#=,ZC#=P?v,m,SAm` v,m,"RdP
s`}m;PY?P#m>K}]D;V m,"RTCm4(S<,?vS<m>;v,#
,+Si~;h*8i~a)5#}g,I:
5Z_-Ok9Cb)?~EDI:)
OC=(G9C_P XML PD%v
U+"X?"DO"
W;)#
h*}Cj{T1
;\+ XML P(e*}C<xD;?V#rK,g{ XML D5PD5h* Nk}C<x,G4&+}]w*X5}]f"#
h*-#|B}]1
\(}f;{vD54|B XML PPD XML }]#g{h*51|BG
; #sDD5PO!,NZ|,Ds?P,G4+}]f"ZG XML PPI\ a'J|_#+G,g{}Z|BDD5\!"R;N;| BY?D5,G4
w* XML }]f"'J
XQuery M XPath }]#M
XQuery mo=T XQuery M XPath }]#M(XDM)D5}xPKc"5X}]# MD5}#XDM GT;vr`v XML D5r,NDis m>#}]#Ma(e XQuery PDmo=DJm5,|(PdFcZd9CD5#
+ XML }]bv* XDM,"Z XQuery &m}].0k
zI}]#MZd,+bvdk XML D5,"+|*;* XDM D5}#ZbvD5 1,ITxPi$,2IT;xPi$#
XDM G4U-S5MZcrP4xPhvD#
2a\_#
T#=4i$b)}]#Z
rPMn
8 pureXML 8O
XQuery M XPath }]#M(XDM)D5}*rP#
n
O#;v
;vrPIT|,Zc"-S5r_GZcM-S5DNbiO#}g,BfPmP D?vu?<G;vrP:
MG;v-S5r;vZc#
rP
G 0 vnr`vnDPr/
v 36 v <dog/> v (2, 3, 4) v (36, <dog/>, "cat") v ()
}PmPDu?.b,f"Z DB2 }]bD XML PPD XML D5G;vrP#
>}PC4m>rPDm>(,kC49l XQuery PDrPDo(G;BD:
v rPPD?n.dC:EVt#
v {vrPGC2(E(p4D#
v ;TUD2(Em>;vUrP#
v g{;vnZ|TmO=vV,G4`1Z;v;|,;nDrP#
}g,rP (36) k-S5 36 ;Pxp#
;\TrPxP6W#1iO=vrP1,qCDa{<UGZcM-S5D=Lr P#}g
*v;avV6WrP,yT,iOb)rP1";azI (3, 5, 6, (2, 3))#
;|,NNnDrPF*
,+rP (2, 3) 7SArP (3, 5, 6) 1+zI%vrP (3, 5, 6, 2, 3)#r
UrP
#I9CUrP4m>1YDE"r4*E"#
-S5
-S5
String"Integer"Decimal"Date Md{-S`M#b)`M<;hv*-S`M,-r G|G^(Y8VK#
kZc;,DG,-S5;Pj6#-S5D?v5}(}g, ?v5}<j+`,#
BP>}G;)zI-S5D=(:
v (};vF*“-S/D}LSZcPi!#?1h* -S5rP1,mo=Ma
v 8(*}VrV{.DV#XQuery a+DVbM*-
v I9l/}FcqC#}g,TB9l/}+y]V{.“2005-01-01”49(`M*
v IZC/} fn:true() M fn:false() 5X#b)/}+5X<{5 true M false#b
v I`Vmo=(}g,cumo=M_-mo=)5X#
GI XML #=(eDdP;VZC-S}]`MD5}#b)}]`M|(
{} 7)kC5Dd{
9C-S/#
S5#}g,BPDVMa;
bM*-S5:
– “this is a string”(`M* xs:string)
–45(`M* xs:integer)
– 1.44(`M* xs:decimal)
xs:date D5:
xs:date("2005-01-01")
)5;\m>*DV#
Z 1 B pureXML Ev 9
ZccNa9
iI;vr`v
cNa 9rw
DZcrP,b)cNa9rwI;vyZcMISC
yZc1SrdSCJDyPZciI#?vZc;tZ;vcNa9,x?vcN
a9;P;vyZc#DB2 'VTB 6 VZc:D5"*X"tT"D>"&m8> E"M"M#
TB XML D5 products.xml |(;vy*X products,Cy*XV|,;) prod- uct *X#?v product *X<P;v{* pid(z7j6)DtTT0;v{* description DS*X#description *X|,{* name M price DS*X#
<products>
<product pid="10">
<description>
<name>Fleece jacket</name> <price>19.99</price>
</description> </product> <product pid="11">
<description>
<name>Nylon pants</name> <price>9.99</price>
</description> </product>
</products>
Z 11 3D< 3 T> products.xml D}]#MDr/m>#C<P|(D5Zc(D)" *XZc(E)"tTZc(A)MD>Zc(T)#
10 pureXML 8O
<
3. products.xml
D5D}]#M<
g>}Py>,;vZcI+d{Zcw*Sz,SxiI;vr`v
9
DSz#name M price *X<G description *XDSz#5* Fleece Jacket D D>ZcG name *XDSz,xD>Zc 19.99 G price *XDSz#
ZctT
?vZc<_P;) |(:ZcD{F"Sz"8z"tTT0C4hvCZcDd{E"#ZcV`7
(*X(Zca)KD)tT#
;vZcIT_PBfD;vr`vtT:
node-name
8z G10ZcD8zDZc#
ZccNa
#ZC>}P,product *XG products DSz#description *X4G product
XT
,b)XTC4hvCZcDXw#}g,ZcDXTI\
ZcD{F,m>*
QName#
Z 1 B pureXML Ev 11
type-name
Sz G10ZcDSzDZcrP#
tT tZ10ZcD;itTZc#
string-value
typed-value
{FUdwCr
content
ZcV`
DB2 'VTB 6 VZc:D5"*X"tT"D>"&m8>E"M"M#
D5Zc
D5Zc|, XML D5#
ZcD/,(KP1)`M(2F*
ITSZcPi!DV{.5#
ITSZcPi!DIcvr`v-S5iIDrP#
kZc`X*DwCrZ{FUd#
ZcDZ]#
`M"M
)#
D5ZcIT_P`vSz,2IT;PSz# SzITG*XZc" &m8nZ c""MZcMD>Zc#
D5ZcDV{.5HZ+|DyPszD>ZcDZ]4D53rxP"CDa {#V{.5D`M
* xdt:untypedAtomic#
D5Zc_PBPZctT:
v children(I\GUD)
v string-value
v typed-value
ITZ XQuery mo= P 9C - }F c qCD9 l/ } 49 lD 5 Zc # db2- fn:xmlcolumn /}9IT5X;5PD5Zc#
* xs:string#D5ZcD`M5kV{.5`,,+`M5D`M
*XZc
*XZc|, XML *X#
;v*XIT_P;v8z,2IT;P8z;,1,|IT_P`vSz,2IT ;PSz#SzITG*XZc"&m8nZc""MZcMD>Zc#D5ZcM
tTZcv;aG*XZcDSz#+G, z#*XZcDtTXk_P(;D QName#
ITO**XZcG|T:DtTD8
12 pureXML 8O
*XZc_PBPZctT:
v node-name
v parent(I\GUD)
v type-name
v children(I\GUD)
v attributes(I\GUD)
v string-value
v typed-value
v in-scope-namespaces
ITZ XQuery mo=P9C1S9l/}r_-}FcqCD9l/}49l*XZ c#
*XZcD type-name tT8>|D`M5kV{.5.dDX5#}g,g{;v* XZc_P type-name tT xs:decimal M g{*XZcD type-name tTG xdt:untyped,G4*XD`M5HZdV{.5,"
R`M* xdt:untypedAtomic#
V{.5“47.5”,G4`M5MG!}5 47.5#
tTZc
tTZcm> XML tT#
tTZcIT_P;v8z,2IT;P8z#I+5PtTD*XZcO*G|D 8z,!\tTZc;G|D8*XDSz#
tTZc_PBPZctT:
v node-name
v parent(I\GUD)
v type-name
v string-value
v typed-value
ITZ XQuery mo
c#
tTZcD type-name tT8>|D`M5kV{.5.dDX5#}g,g{;vt TZc_P type-name tT xs:decimal MV{.5“47.5”,G4|D`M5MG!}5
47.5#
=P9C1S9l/}r_-}FcqCD9l/}49ltTZ
D>Zc
D>Zc|, XML V{Z]#
D>ZcIT_P;v8z,2IT;P8z#w*D5Zcr*XZcDSzDD
>Zcv;aw*`Z,zvV#19lD5Zcr*XZc1,NN`ZDD>Z c,z<a;O"I%vD>Zc#g{
D>Zc_PBPZctT:
v content(I\GUD)
v parent(I\GUD)
ITZ XQuery mo=P9C-}FcqCD9l/}49lD>Zc,2IT(}1 S*X9l/}DYw49lD>Zc#
qCDD>ZcGUD,G4a+|Oz#
Z 1 B pureXML Ev 13
&m8nZc
&m8nZcab0 XML &m8n#
&m8nZcIT_P;v8z,2IT;P8z#&m8nDZ];\|,V{. ?>#&m8nD?jXkG;v NCName#C?jC4j6*+8>E""MxD&C
Lr#
&m8nZc_PBPZctT
v target
v content
v parent(I\GUD)
ITZ XQuery mo=P9C1S9l/}r_-}FcqCD9l/}49l&m8
nZc#
:
"MZc
"MZc|, XML "M#
"MZcIT_P;v8z,2IT;P8z #"MZcDZ];\|(V{.“--” (=v,V{),"Rns;vV{;\G,V{(-)#
"MZc_PBPZctT:
v content
v parent(I\GUD)
ITZ XQuery mo=P
c#
9C1S9l/}r_-}FcqCD9l/}49l"MZ
ZcDD53r
;vcNa9PDyPZc<*qS3;3r(4, c<+ZdSz0fvV#g{ZccNa9GCQrP/D XML m>D,G4D5 3rkZcDvV3r`T&#
cNa9PDZc4TB3rvV:
v yZcGZ;vZc#
v *XZcZ|GDSz0fv
v tTZctzZk|G`X*D*XZcsfvV#tTZcD`T3rITGN
bD,+GZ&mi/ZdK3r;aDd#
v ,zD`T3rI|GZZccNa9PD3r47(#
v ;vZcDSzMsz+ZCZcsfD
Zcj6
?vZc<P;v(;j6#49=vZcD{FM5<`,,2IT+|GxV *#;x,-S5;Pj6#
Zcj6k ID-type tT;`,#XML D5PD*XIID5w_x( ID-type tT#
;x,Zcj6GI53T/*?vZc8(D,C
V#
D53r
,z0fvV#
'^(1S4{Zcj6#
)#4UC3r,?vZ
14 pureXML 8O
Zcj6CZ&mBP`MDmo=:
v ZcHO#is Kc{9CZcj647(=vZcGq_P`,j6#
v 76mo=#76mo=9CZcj64{}X4DZc#
v rPmo=#union"intersect r except Kc{9CZcj64{}X4D
ZcD`M5MV{.5
Zc#
?vZc<,1P
`M5MV{.5
#b=vZctTCZ3) XQuery Yw(}g,
-S/)M/}(}g,fn:data"fn:string M fn:deep-equal)D(eP#
m1.ZcDV{.5M`M5
ZcV` V{.5 `M5
D5 xs:string }]`MD5},|G+|DyPszD>
ZcDZ]4D53rxP"CDa{#
XML D5P D*X
XML D5P DtT
D> w* xs:string }]`MD5}DZ]# w* xdt:untypedAtomic }]`MD5}DZ]#
"M w* xs:string }]`MD5}DZ]# w* xs:string }]`MD5}DZ]#
&m8n w* xs:string }]`MD5}DZ]# w*
xs:string }]`MD5
szDZ]4D53rxP"CDa{#
xs:string }]`MD5},|m>-< XML D5P DtT5#
},|G+|DyPD>Zc
xdt:untypedAtomic }]`MD5},|G+|DyP szD>ZcDZ]4D53rxP"CDa{#
xdt:untypedAtomic }]`MD5},|G+|DyP D>ZcszDZ]4D53rxP"CDa{#
xdt:untypedAtomic } XML D5PDtT5#
xs:string }]`MD5}DZ]#
]`MD5},| m>-<
'V XML D$_
IBM MZ}=$_'V9C pureXML &\?~#BP$_Gf DB2 }]b~qwa )Dr_GI%@S IBM BXD:
IBM Data Studio:T XML D'V|(TB8n:
v f"}L:IT4(MKP|, XML }]`Mw*dkMdvN}Df"}L#
v }]dv:ITw
v SQL `-w: I4(,19CX5}]M XML }]D SQL odM XQuery m
o=#
v XML #=:IT\m XML #=f"b(XSR)PD#=D5,|("aM>}#
=T0`-#=D5#
v XML D5i$:ITkT
v C'(eD SQL /}: I4("KP9C XML N}DC'(eD SQL /}#
MrD>N=i4|,Z XML PPDD5#
Z XSR P"aD#=i$ XML D5#
DB2 XFPD:DB2 XFPD'VT|Dm`\m&\9C>z XML }]`M#K
|B9}]b\m1\;Z;v GUI $_P&m XML }]MX5
}]#
\'VD\mNqD>}|(:
v 4(xP XML PDm
v 9CBD4(w}r<44( XML Pw}
v i4f"Z XML PPD XML D5DZ]
v 9C&m XML D5yhD XML #="DTD Mb?5e
v U/|,K XML PDmD3FE"
Z 1 B pureXML Ev 15
|nP&mw:tIv DB2 |n'V>zf" XML }]#ITS DB2 |nP&m
w(CLP)9CX5}]T_D XML }]#ITS CLP 4PDNqD>}|(:
v (}T XQuery odSO XQUERY X|V0:4"v XQuery od#
v <kM<v XML }]#
v T XML PU/3
FE"#
v 9C XML }]`MD IN"OUT r INOUT N}4wCf"}L#
v 9C&m XML D5yhD XML #="DTD Mb?5e
v XiyZ XML }]Dw}M|, XML PDm#
v Vb XML D5#
DB2 Development Add-In for Microsoft Visual Studio .NET:IT9 C Devel­opment Add-In for Microsoft Visual Studio .NET 44(_P XML PDmMyZ XML
}]Dw}#
ZK$_Pq4(NNd{P;y4( XML P#;h*+}]`M8(
* XML#IT(}9CK$_PD“XML w}hFw”44(w}#;Xg9C CRE- ATE INDEX o(4(yZ XML }]Dw}1y*sDGyV/8( XML #=mo
=#`4,ITSQ
"aD XML #=DwMm>"XML PPDD5r>XD~PD
XML #=T<N==!qk*("w}D XML Zc#C$_+*zzI XML #= mo=#r_,ITV/8( XML #=mo=#8(KyPd{w}tT.s,C$
_+*zzIw}#
EXPLAIN:ITZ XQuery odM SQL/XML odP"v EXPLAIN od,TclY i4b)odDCJ=8,|( DB2 }]b~qwGq9Cw}#*"v XQuery o dD EXPLAIN od,I9C XQuery X|V,szC%}Er+}E}p4D XQuery od,gTB>}y>:
EXPLAIN PLAN SELECTION FOR XQUERY 'for $c in db2-fn:xmlcolumn("XISCANTABLE.XMLCOL" )/a[@x="1"]/b[@y="2"] return $c'
DB2 +6q EXPLAIN mPDCJ=8E"#yP XML PDZ{rPs!<f"Z EXPLAIN_STREAM m D SEQUENCE_SIZES P P# zI \ 9a" b = EXPLAIN_PREDICATE mPfZ3)}],|GG;)z^(6pD=J#b)=J
I DB2 }]b~qwZ EXPLAIN YwZdTw}(hP9CD XPath mo=xP
51zI#z;h*@@K=JE"#b)=J;GE/w=8D;?V,rKZ
s
PREDICATE_ID M FILTER_FACTOR PPD5* -1#
r_,(}9C Visual Explain $_4i4b)CJ=8D<Nhv,IT\bV/b M EXPLAIN m#BPZcT>Z<NPT5w XML Yw:
IXAND
> DB2 }]b~qwQ+ AND =J&CZ`vw}(hDa{#
8
XISCAN
8> DB2 }]b~qwQ9CyZ XML }]Dw}4CJ}]#
XSCAN
8> DB2 }]b~qwQT XPath mo=s5,"RQS XML D5Pi! XML
D5,N#
16 pureXML 8O
XANDOR
8> DB2 }]b~qwQ+ AND M OR
=J&CZ`vw}(hDa{#
XTQ
8> DB2 }]b~qw9CKXbmSP(TQ)++V XML rPPD?n"M =d-<}]bVx,y]CnD@@S XML D5lw XML }],;s+ XML }][/=dvrPP#
pureXML D*O'V
Z*O73P,I9C|, XML PPf"D XML D5D6L}]4#Ii/"&m 6L XML }],|(+ XML D5Vb*6Lm#
h*H*|, XML P(dPf"K*9CDD5)D6Lm4(GF,E\9C6L XML }]#
PXhC|( XML }]4D L XML }]#
pureXML D4FMB~"<'V
XML }]`MD WebSphere®Replication Server M WebSphere®Data Event Pub­lisher 'VJmz4F""<f"Z XML PPD XML D5#
I9C Q 4FZ}]b.d4F XML D5,2I9CB~"<+D54FA&CL
r#
PX*|(f"Z XML PPD XML D5D}]bhC Q 4FMB~"<D|`E
",kND WebSphere Replication Server M WebSphere Data Event Publisher D5P D“XML
}]`M08wb#
XZ XML 'VDDB
(} developerWorks®E"\mIqCXZ{C XML 'VDd{DB#b)DBf0 c:Dwb,|((FM}]F/"(CEv"p=LLM9C XML }]DnQv
(#
IZBfDx7PR=b)DB:www.ibm.com/developerworks/db2/zones/xml/#
*O53D|`E",kND*O~qwD5PD9C6
":developerWorks "G DB2 E"PDD;?V#K4S+r* DB2 E DZ]#
Z 1 B pureXML Ev 17
"PDTb
18 pureXML 8O
Z 2 B pureXML LL
I9C pureXML XML }]`M4(emP,TczIZ?PPf"%vq=<CD XML D5#>LL5wgNhC DB2 }]bTf" XML }]T0gN9C pureXML
&\?~4Py>Yw#
ZjI>LL.s,z+\;4PBPNq:
v 4(If" XML }]D DB2 }]
v 4(yZ XML }]Dw}
v + XML D5ek= XML `MDPP
v |Bf"Z XML PPD XML D5
v y] XML D5DZ]>}P
v i/ XML }]
v kT XML #=i$ XML D5
v 9C XSLT y=md; XML D5
bMm
C++"Java M PHP .`D&CLr`LoT'V XML }]`M 4+ XML }]f"Z DB2 }]bmP,SmPlw}]rwC_P XML N}Df
"}LrC'(eD/}#
>LLG*%;Vx}]b73`4D,+z2IZVx}]b73P9C pureXML & \?~#
#IT`4&CLr
Hvu~
Z DB2 |n0ZP,(}"v db2 -td~ |n(xP -td~ !nD db2 |n)4t/ DB2 |nP&mw#
-td !na+&/t{E(~)hC*odU9V{#8(1!VE(-t !n)TbDU 9V{I7#;amsbM9C{FUdywDodri/,-rG{FUdyw2 GCVEU9D#>LLPD>}9C ~ U9V{#
I+NLPD>
r_,I9C|n`-w4T;%=="v|nMod#
{FUd:LLP9CD XML D5|,{FUd#9C|,{FUdD XML D5 1,8({FUdDyPi/MX*Yw(g Z XML }]Dw}r9C XQuery mo=4i/ XML }])Xkyw,;{FU
dE\zIZ{Da{#9C|,{FUdD XML D51,yw{FUdGj<{F UdP*#
1
}T;%==dkr4F"3y= DB2 |nP&mwP#
(}9C CREATE INDEX od44(y
1. Z Windows Yw53O,H9C db2cmd |nt/ DB2 |n0Z,;s"v db2 -td~ |n#
© Copyright IBM Corp. 2006, 2009
19
NL 1:4(ITf" XML }]D DB2 }]bMm
>NLhvgN9C|, XML PDm4(}]b#
Z>LLP,z+ XML }]f"Z;vmP,xKm|,_P XML `MDPDm# kq-BP=h44(>LLP9CD}]bMm:
1. (}"vTB|n44(F* XMLTUT D}]b:
CREATE DATABASE xmltut~
1!ivB,+Z4(}]b1tCT/f"w#T/f"wITa_T\"R| ]W\m XML }],r*|+zIITy]h*xP)9D}]b \mUd
(DMS)mUd#
xR,Z1!ivB,}]b9C UTF-8(Unicode)zk/#g{!q+ ]f"Z9C UTF-8 TbDzk/D}]bP,G4nCT;xPzk3*;DN =(}g BIT DATA"BLOB r XML)ekK}]#*h9Z4P XML bvZ d9CV{}]`M,Sx@9I\"zDV{f;,k+ ENABLE_XMLCHAR
dCN}hC* NO#
2. k}]
3. 4(;v{* Customer "R|,F* INFO D XML PDm:
5XALL
b,S:
CONNECT TO xmltut~
CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML)~
f" XML }]r_T XML }]("w}1;h*9Cw|#
9IT9C ALTER TABLE SQL od+;vr`v XML PmSAm#
NL 2:4(yZ XML }]Dw}
>NLhvgN4( XML }]w}#yZ XML }]Dw}ITa_i/ XML P DT\#kTzZ=JPT0gD5xP,S1519CD XML *XrtT("w
}#
XML }
20 pureXML 8O
X5w}M XML }]w}<TP("w}#+G,X5w}GT{vP("w} XML }]w};GTPD3?V("w}#(}8( XML #=48w*T XML P
DD)?V("w},C#=G\^FD XPath mo=#9Xk8(Q("w}D5+ ICDV}]`MxPf"#(#,z!qD`M&Cki/P9CD`M`
kX5w};y,(izT=JM;fD5,SP519CD XML *XrtT("w }#
;\T%v XML P("w};;'ViOw}#+G,;v XML PITP`vw }#
";G CREATE INDEX odDyPSd<JCZyZ XML }]Dw}#PXj8
,kND CREATE INDEX od#
E"
*4( XML }]w},k"vTBod:
,x
,#
Loading...
+ 445 hidden pages