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

9CKE"0d'VDz70,kHDAZ 447 3D=< E, :yw;BD#fE"#

^)fyw

KD5|, IBM DyP(E"#|ZmI-iPa),R\f((D#$#>vfoP|,DE";|(TNNz7D #$,Ra)DNNod<;h*gKbM#

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"#

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

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

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

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

ADD XMLSCHEMA DOCUMENT .....431

COMPLETE XMLSCHEMA ........432

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

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=#

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`#

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

(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#

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/

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#

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#

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#

O#;v

;vrPIT|,Zc"-S5r_GZcM-S5DNbiO#}g,BfPmP D?vu?<G;vrP:

MG;v-S5r;vZc#

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

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#

<name>Fleece jacket</name> <price>19.99</price>

</description> </product> <product pid="11">

<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

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

,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

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

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

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 9CBD“4(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 Development 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

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{#

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 Publisher 'VJmz4F""<f"Z XML PPD XML D5#

I9C Q 4FZ}]b.d4F XML D5,2I9CB~"<+D54FA&CL

PX*|(f"Z XML PPD XML D5D}]bhC Q 4FMB~"<D|`E

",kND WebSphere Replication Server M WebSphere Data Event Publisher D5P D“XML

}]`M”08wb#

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~qwD5PD“9C6

":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*#

}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#

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#

*4( XML }]w},k"vTBod:

+ 445 hidden pages

IBM DB2 9.7 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual