1SET @xml='<a aa1="aa1" aa2="aa2">a1<b ba1="ba1">b1<c>c1</c>b2</b>a2</a>'; 2SELECT extractValue(@xml,'/a'); 3extractValue(@xml,'/a') 4a1 a2 5SELECT extractValue(@xml,'/a/b'); 6extractValue(@xml,'/a/b') 7b1 b2 8SELECT extractValue(@xml,'/a/b/c'); 9extractValue(@xml,'/a/b/c') 10c1 11SELECT extractValue(@xml,'/a/@aa1'); 12extractValue(@xml,'/a/@aa1') 13aa1 14SELECT extractValue(@xml,'/a/@aa2'); 15extractValue(@xml,'/a/@aa2') 16aa2 17SELECT extractValue(@xml,'/a/@*'); 18extractValue(@xml,'/a/@*') 19aa1 aa2 20SELECT extractValue(@xml,'//@ba1'); 21extractValue(@xml,'//@ba1') 22ba1 23SELECT extractValue(@xml,'//a'); 24extractValue(@xml,'//a') 25a1 a2 26SELECT extractValue(@xml,'//b'); 27extractValue(@xml,'//b') 28b1 b2 29SELECT extractValue(@xml,'//c'); 30extractValue(@xml,'//c') 31c1 32SELECT extractValue(@xml,'/a//b'); 33extractValue(@xml,'/a//b') 34b1 b2 35SELECT extractValue(@xml,'/a//c'); 36extractValue(@xml,'/a//c') 37c1 38SELECT extractValue(@xml,'//*'); 39extractValue(@xml,'//*') 40a1 b1 c1 b2 a2 41SELECT extractValue(@xml,'/a//*'); 42extractValue(@xml,'/a//*') 43b1 c1 b2 44SELECT extractValue(@xml,'/./a'); 45extractValue(@xml,'/./a') 46a1 a2 47SELECT extractValue(@xml,'/a/b/.'); 48extractValue(@xml,'/a/b/.') 49b1 b2 50SELECT extractValue(@xml,'/a/b/..'); 51extractValue(@xml,'/a/b/..') 52a1 a2 53SELECT extractValue(@xml,'/a/b/../@aa1'); 54extractValue(@xml,'/a/b/../@aa1') 55aa1 56SELECT extractValue(@xml,'/*'); 57extractValue(@xml,'/*') 58a1 a2 59SELECT extractValue(@xml,'/*/*'); 60extractValue(@xml,'/*/*') 61b1 b2 62SELECT extractValue(@xml,'/*/*/*'); 63extractValue(@xml,'/*/*/*') 64c1 65SELECT extractValue(@xml,'/a/child::*'); 66extractValue(@xml,'/a/child::*') 67b1 b2 68SELECT extractValue(@xml,'/a/self::*'); 69extractValue(@xml,'/a/self::*') 70a1 a2 71SELECT extractValue(@xml,'/a/descendant::*'); 72extractValue(@xml,'/a/descendant::*') 73b1 c1 b2 74SELECT extractValue(@xml,'/a/descendant-or-self::*'); 75extractValue(@xml,'/a/descendant-or-self::*') 76a1 b1 c1 b2 a2 77SELECT extractValue(@xml,'/a/attribute::*'); 78extractValue(@xml,'/a/attribute::*') 79aa1 aa2 80SELECT extractValue(@xml,'/a/b/c/parent::*'); 81extractValue(@xml,'/a/b/c/parent::*') 82b1 b2 83SELECT extractValue(@xml,'/a/b/c/ancestor::*'); 84extractValue(@xml,'/a/b/c/ancestor::*') 85a1 b1 b2 a2 86SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*'); 87extractValue(@xml,'/a/b/c/ancestor-or-self::*') 88a1 b1 c1 b2 a2 89SELECT extractValue(@xml,'/descendant-or-self::*'); 90extractValue(@xml,'/descendant-or-self::*') 91a1 b1 c1 b2 a2 92SET @xml='<a>a11<b ba="ba11" ba="ba12">b11</b><b ba="ba21" ba="ba22">b21<c>c1</c>b22</b>a12</a>'; 93SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*'); 94extractValue(@xml,'/a/b/c/ancestor-or-self::*') 95a11 b21 c1 b22 a12 96SELECT extractValue(@xml,'//@ba'); 97extractValue(@xml,'//@ba') 98ba11 ba12 ba21 ba22 99SET @xml='<a><b>b</b><c>c</c></a>'; 100SELECT extractValue(@xml,'/a/b'); 101extractValue(@xml,'/a/b') 102b 103SELECT extractValue(@xml,'/a/c'); 104extractValue(@xml,'/a/c') 105c 106SELECT extractValue(@xml,'/a/child::b'); 107extractValue(@xml,'/a/child::b') 108b 109SELECT extractValue(@xml,'/a/child::c'); 110extractValue(@xml,'/a/child::c') 111c 112SET @xml='<a><b>b1</b><c>c1</c><b>b2</b><c>c2</c></a>'; 113SELECT extractValue(@xml,'/a/b[1]'); 114extractValue(@xml,'/a/b[1]') 115b1 116SELECT extractValue(@xml,'/a/b[2]'); 117extractValue(@xml,'/a/b[2]') 118b2 119SELECT extractValue(@xml,'/a/c[1]'); 120extractValue(@xml,'/a/c[1]') 121c1 122SELECT extractValue(@xml,'/a/c[2]'); 123extractValue(@xml,'/a/c[2]') 124c2 125SET @xml='<a><b x="xb1" x="xb2"/><c x="xc1" x="xc2"/></a>'; 126SELECT extractValue(@xml,'/a//@x'); 127extractValue(@xml,'/a//@x') 128xb1 xb2 xc1 xc2 129SELECT extractValue(@xml,'/a//@x[1]'); 130extractValue(@xml,'/a//@x[1]') 131xb1 xc1 132SELECT extractValue(@xml,'/a//@x[2]'); 133extractValue(@xml,'/a//@x[2]') 134xb2 xc2 135SET @xml='<a><b>b1</b><b>b2</b><c><b>c1b1</b><b>c1b2</b></c><c><b>c2b1</c></b></a>'; 136SELECT extractValue(@xml,'//b[1]'); 137extractValue(@xml,'//b[1]') 138b1 c1b1 c2b1 139SELECT extractValue(@xml,'/descendant::b[1]'); 140extractValue(@xml,'/descendant::b[1]') 141b1 142SET @xml='<a><b>b1</b><b>b2</b></a>'; 143SELECT extractValue(@xml,'/a/b[1+0]'); 144extractValue(@xml,'/a/b[1+0]') 145b1 146SELECT extractValue(@xml,'/a/b[1*1]'); 147extractValue(@xml,'/a/b[1*1]') 148b1 149SELECT extractValue(@xml,'/a/b[--1]'); 150extractValue(@xml,'/a/b[--1]') 151b1 152SELECT extractValue(@xml,'/a/b[2*1-1]'); 153extractValue(@xml,'/a/b[2*1-1]') 154b1 155SELECT extractValue(@xml,'/a/b[1+1]'); 156extractValue(@xml,'/a/b[1+1]') 157b2 158SELECT extractValue(@xml,'/a/b[1*2]'); 159extractValue(@xml,'/a/b[1*2]') 160b2 161SELECT extractValue(@xml,'/a/b[--2]'); 162extractValue(@xml,'/a/b[--2]') 163b2 164SELECT extractValue(@xml,'/a/b[1*(3-1)]'); 165extractValue(@xml,'/a/b[1*(3-1)]') 166b2 167SELECT extractValue(@xml,'//*[1=1]'); 168extractValue(@xml,'//*[1=1]') 169b1 b2 170SELECT extractValue(@xml,'//*[1!=1]'); 171extractValue(@xml,'//*[1!=1]') 172 173SELECT extractValue(@xml,'//*[1>1]'); 174extractValue(@xml,'//*[1>1]') 175 176SELECT extractValue(@xml,'//*[2>1]'); 177extractValue(@xml,'//*[2>1]') 178b1 b2 179SELECT extractValue(@xml,'//*[1>2]'); 180extractValue(@xml,'//*[1>2]') 181 182SELECT extractValue(@xml,'//*[1>=1]'); 183extractValue(@xml,'//*[1>=1]') 184b1 b2 185SELECT extractValue(@xml,'//*[2>=1]'); 186extractValue(@xml,'//*[2>=1]') 187b1 b2 188SELECT extractValue(@xml,'//*[1>=2]'); 189extractValue(@xml,'//*[1>=2]') 190 191SELECT extractValue(@xml,'//*[1<1]'); 192extractValue(@xml,'//*[1<1]') 193 194SELECT extractValue(@xml,'//*[2<1]'); 195extractValue(@xml,'//*[2<1]') 196 197SELECT extractValue(@xml,'//*[1<2]'); 198extractValue(@xml,'//*[1<2]') 199b1 b2 200SELECT extractValue(@xml,'//*[1<=1]'); 201extractValue(@xml,'//*[1<=1]') 202b1 b2 203SELECT extractValue(@xml,'//*[2<=1]'); 204extractValue(@xml,'//*[2<=1]') 205 206SELECT extractValue(@xml,'//*[1<=2]'); 207extractValue(@xml,'//*[1<=2]') 208b1 b2 209SET @xml='<a><b>b11<c>c11</c></b><b>b21<c>c21</c></b></a>'; 210SELECT extractValue(@xml,'/a/b[c="c11"]'); 211extractValue(@xml,'/a/b[c="c11"]') 212b11 213SELECT extractValue(@xml,'/a/b[c="c21"]'); 214extractValue(@xml,'/a/b[c="c21"]') 215b21 216SET @xml='<a><b c="c11">b11</b><b c="c21">b21</b></a>'; 217SELECT extractValue(@xml,'/a/b[@c="c11"]'); 218extractValue(@xml,'/a/b[@c="c11"]') 219b11 220SELECT extractValue(@xml,'/a/b[@c="c21"]'); 221extractValue(@xml,'/a/b[@c="c21"]') 222b21 223SET @xml='<a>a1<b c="c11">b11<d>d11</d></b><b c="c21">b21<d>d21</d></b></a>'; 224SELECT extractValue(@xml, '/a/b[@c="c11"]/d'); 225extractValue(@xml, '/a/b[@c="c11"]/d') 226d11 227SELECT extractValue(@xml, '/a/b[@c="c21"]/d'); 228extractValue(@xml, '/a/b[@c="c21"]/d') 229d21 230SELECT extractValue(@xml, '/a/b[d="d11"]/@c'); 231extractValue(@xml, '/a/b[d="d11"]/@c') 232c11 233SELECT extractValue(@xml, '/a/b[d="d21"]/@c'); 234extractValue(@xml, '/a/b[d="d21"]/@c') 235c21 236SELECT extractValue(@xml, '/a[b="b11"]'); 237extractValue(@xml, '/a[b="b11"]') 238a1 239SELECT extractValue(@xml, '/a[b/@c="c11"]'); 240extractValue(@xml, '/a[b/@c="c11"]') 241a1 242SELECT extractValue(@xml, '/a[b/d="d11"]'); 243extractValue(@xml, '/a[b/d="d11"]') 244a1 245SELECT extractValue(@xml, '/a[/a/b="b11"]'); 246extractValue(@xml, '/a[/a/b="b11"]') 247a1 248SELECT extractValue(@xml, '/a[/a/b/@c="c11"]'); 249extractValue(@xml, '/a[/a/b/@c="c11"]') 250a1 251SELECT extractValue(@xml, '/a[/a/b/d="d11"]'); 252extractValue(@xml, '/a[/a/b/d="d11"]') 253a1 254SELECT extractValue('<a>a</a>', '/a[false()]'); 255extractValue('<a>a</a>', '/a[false()]') 256 257SELECT extractValue('<a>a</a>', '/a[true()]'); 258extractValue('<a>a</a>', '/a[true()]') 259a 260SELECT extractValue('<a>a</a>', '/a[not(false())]'); 261extractValue('<a>a</a>', '/a[not(false())]') 262a 263SELECT extractValue('<a>a</a>', '/a[not(true())]'); 264extractValue('<a>a</a>', '/a[not(true())]') 265 266SELECT extractValue('<a>a</a>', '/a[true() and true()]'); 267extractValue('<a>a</a>', '/a[true() and true()]') 268a 269SELECT extractValue('<a>a</a>', '/a[true() and false()]'); 270extractValue('<a>a</a>', '/a[true() and false()]') 271 272SELECT extractValue('<a>a</a>', '/a[false()and false()]'); 273extractValue('<a>a</a>', '/a[false()and false()]') 274 275SELECT extractValue('<a>a</a>', '/a[false()and true()]'); 276extractValue('<a>a</a>', '/a[false()and true()]') 277 278SELECT extractValue('<a>a</a>', '/a[true() or true()]'); 279extractValue('<a>a</a>', '/a[true() or true()]') 280a 281SELECT extractValue('<a>a</a>', '/a[true() or false()]'); 282extractValue('<a>a</a>', '/a[true() or false()]') 283a 284SELECT extractValue('<a>a</a>', '/a[false()or false()]'); 285extractValue('<a>a</a>', '/a[false()or false()]') 286 287SELECT extractValue('<a>a</a>', '/a[false()or true()]'); 288extractValue('<a>a</a>', '/a[false()or true()]') 289a 290SET @xml='<a>ab<b c="c" c="e">b1</b><b c="d">b2</b><b c="f" c="e">b3</b></a>'; 291select extractValue(@xml,'/a/b[@c="c"]'); 292extractValue(@xml,'/a/b[@c="c"]') 293b1 294select extractValue(@xml,'/a/b[@c="d"]'); 295extractValue(@xml,'/a/b[@c="d"]') 296b2 297select extractValue(@xml,'/a/b[@c="e"]'); 298extractValue(@xml,'/a/b[@c="e"]') 299b1 b3 300select extractValue(@xml,'/a/b[not(@c="e")]'); 301extractValue(@xml,'/a/b[not(@c="e")]') 302b2 303select extractValue(@xml,'/a/b[@c!="e"]'); 304extractValue(@xml,'/a/b[@c!="e"]') 305b1 b2 b3 306select extractValue(@xml,'/a/b[@c="c" or @c="d"]'); 307extractValue(@xml,'/a/b[@c="c" or @c="d"]') 308b1 b2 309select extractValue(@xml,'/a/b[@c="c" and @c="e"]'); 310extractValue(@xml,'/a/b[@c="c" and @c="e"]') 311b1 312SET @xml='<a><b c="c" d="d">b1</b><b d="d" e="e">b2</b></a>'; 313select extractValue(@xml,'/a/b[@c]'); 314extractValue(@xml,'/a/b[@c]') 315b1 316select extractValue(@xml,'/a/b[@d]'); 317extractValue(@xml,'/a/b[@d]') 318b1 b2 319select extractValue(@xml,'/a/b[@e]'); 320extractValue(@xml,'/a/b[@e]') 321b2 322select extractValue(@xml,'/a/b[not(@c)]'); 323extractValue(@xml,'/a/b[not(@c)]') 324b2 325select extractValue(@xml,'/a/b[not(@d)]'); 326extractValue(@xml,'/a/b[not(@d)]') 327 328select extractValue(@xml,'/a/b[not(@e)]'); 329extractValue(@xml,'/a/b[not(@e)]') 330b1 331select extractValue(@xml, '/a/b[boolean(@c) or boolean(@d)]'); 332extractValue(@xml, '/a/b[boolean(@c) or boolean(@d)]') 333b1 b2 334select extractValue(@xml, '/a/b[boolean(@c) or boolean(@e)]'); 335extractValue(@xml, '/a/b[boolean(@c) or boolean(@e)]') 336b1 b2 337select extractValue(@xml, '/a/b[boolean(@d) or boolean(@e)]'); 338extractValue(@xml, '/a/b[boolean(@d) or boolean(@e)]') 339b1 b2 340select extractValue(@xml, '/a/b[boolean(@c) and boolean(@d)]'); 341extractValue(@xml, '/a/b[boolean(@c) and boolean(@d)]') 342b1 343select extractValue(@xml, '/a/b[boolean(@c) and boolean(@e)]'); 344extractValue(@xml, '/a/b[boolean(@c) and boolean(@e)]') 345 346select extractValue(@xml, '/a/b[boolean(@d) and boolean(@e)]'); 347extractValue(@xml, '/a/b[boolean(@d) and boolean(@e)]') 348b2 349select extractValue(@xml, '/a/b[@c or @d]'); 350extractValue(@xml, '/a/b[@c or @d]') 351b1 b2 352select extractValue(@xml, '/a/b[@c or @e]'); 353extractValue(@xml, '/a/b[@c or @e]') 354b1 b2 355select extractValue(@xml, '/a/b[@d or @e]'); 356extractValue(@xml, '/a/b[@d or @e]') 357b1 b2 358select extractValue(@xml, '/a/b[@c and @d]'); 359extractValue(@xml, '/a/b[@c and @d]') 360b1 361select extractValue(@xml, '/a/b[@c and @e]'); 362extractValue(@xml, '/a/b[@c and @e]') 363 364select extractValue(@xml, '/a/b[@d and @e]'); 365extractValue(@xml, '/a/b[@d and @e]') 366b2 367SET @xml='<a><b c="c">b1</b><b>b2</b></a>'; 368SELECT extractValue(@xml,'/a/b[@*]'); 369extractValue(@xml,'/a/b[@*]') 370b1 371SELECT extractValue(@xml,'/a/b[not(@*)]'); 372extractValue(@xml,'/a/b[not(@*)]') 373b2 374SELECT extractValue('<a>a</a>', '/a[ceiling(3.1)=4]'); 375extractValue('<a>a</a>', '/a[ceiling(3.1)=4]') 376a 377SELECT extractValue('<a>a</a>', '/a[floor(3.1)=3]'); 378extractValue('<a>a</a>', '/a[floor(3.1)=3]') 379a 380SELECT extractValue('<a>a</a>', '/a[round(3.1)=3]'); 381extractValue('<a>a</a>', '/a[round(3.1)=3]') 382a 383SELECT extractValue('<a>a</a>', '/a[round(3.8)=4]'); 384extractValue('<a>a</a>', '/a[round(3.8)=4]') 385a 386SELECT extractValue('<a><b>b</b><c>c</c></a>', '/a/b | /a/c'); 387extractValue('<a><b>b</b><c>c</c></a>', '/a/b | /a/c') 388b c 389select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=1]'); 390extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=1]') 391b1 392select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=2]'); 393extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=2]') 394b2 395select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3]'); 396extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3]') 397b3 398select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[1=position()]'); 399extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[1=position()]') 400b1 401select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2=position()]'); 402extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2=position()]') 403b2 404select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[3=position()]'); 405extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[3=position()]') 406b3 407select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2>=position()]'); 408extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2>=position()]') 409b1 b2 410select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2<=position()]'); 411extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2<=position()]') 412b2 b3 413select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3 or position()=2]'); 414extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3 or position()=2]') 415b2 b3 416SELECT extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=0]'); 417extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=0]') 418a2 419SELECT extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=1]'); 420extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=1]') 421a1 422select extractValue('<a>a1<b ba="1" ba="2">b1</b><b>b2</b>4</a>','/a/b[sum(@ba)=3]'); 423extractValue('<a>a1<b ba="1" ba="2">b1</b><b>b2</b>4</a>','/a/b[sum(@ba)=3]') 424b1 425select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[1]'); 426extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[1]') 427b1 428select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[boolean(1)]'); 429extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[boolean(1)]') 430b1 b2 431select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[true()]'); 432extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[true()]') 433b1 b2 434select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[number(true())]'); 435extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[number(true())]') 436b1 437select extractValue('<a>ab</a>','/a[contains("abc","b")]'); 438extractValue('<a>ab</a>','/a[contains("abc","b")]') 439ab 440select extractValue('<a>ab</a>','/a[contains(.,"a")]'); 441extractValue('<a>ab</a>','/a[contains(.,"a")]') 442ab 443select extractValue('<a>ab</a>','/a[contains(.,"b")]'); 444extractValue('<a>ab</a>','/a[contains(.,"b")]') 445ab 446select extractValue('<a>ab</a>','/a[contains(.,"c")]'); 447extractValue('<a>ab</a>','/a[contains(.,"c")]') 448 449select extractValue('<a b="1">ab</a>','/a[concat(@b,"2")="12"]'); 450extractValue('<a b="1">ab</a>','/a[concat(@b,"2")="12"]') 451ab 452SET @xml='<a b="11" b="12" b="21" b="22">ab</a>'; 453select extractValue(@xml, '/a/@b[substring(.,2)="1"]'); 454extractValue(@xml, '/a/@b[substring(.,2)="1"]') 45511 21 456select extractValue(@xml, '/a/@b[substring(.,2)="2"]'); 457extractValue(@xml, '/a/@b[substring(.,2)="2"]') 45812 22 459select extractValue(@xml, '/a/@b[substring(.,1,1)="1"]'); 460extractValue(@xml, '/a/@b[substring(.,1,1)="1"]') 46111 12 462select extractValue(@xml, '/a/@b[substring(.,1,1)="2"]'); 463extractValue(@xml, '/a/@b[substring(.,1,1)="2"]') 46421 22 465select extractValue(@xml, '/a/@b[substring(.,2,1)="1"]'); 466extractValue(@xml, '/a/@b[substring(.,2,1)="1"]') 46711 21 468select extractValue(@xml, '/a/@b[substring(.,2,1)="2"]'); 469extractValue(@xml, '/a/@b[substring(.,2,1)="2"]') 47012 22 471SET @xml='<a><b>b1</b><b>b2</b></a>'; 472SELECT extractValue(@xml, '/a/b[string-length("x")=1]'); 473extractValue(@xml, '/a/b[string-length("x")=1]') 474b1 b2 475SELECT extractValue(@xml, '/a/b[string-length("xx")=2]'); 476extractValue(@xml, '/a/b[string-length("xx")=2]') 477b1 b2 478SELECT extractValue(@xml, '/a/b[string-length("xxx")=2]'); 479extractValue(@xml, '/a/b[string-length("xxx")=2]') 480 481SELECT extractValue(@xml, '/a/b[string-length("x")]'); 482extractValue(@xml, '/a/b[string-length("x")]') 483b1 484SELECT extractValue(@xml, '/a/b[string-length("xx")]'); 485extractValue(@xml, '/a/b[string-length("xx")]') 486b2 487SELECT extractValue(@xml, '/a/b[string-length()]'); 488extractValue(@xml, '/a/b[string-length()]') 489b2 490SELECT extractValue(@xml, 'string-length()'); 491ERROR HY000: XPATH syntax error: '' 492SELECT extractValue(@xml, 'string-length("x")'); 493extractValue(@xml, 'string-length("x")') 4941 495SET @xml='<a b="b11" b="b12" b="b21" b="22"/>'; 496select extractValue(@xml,'/a/@b'); 497extractValue(@xml,'/a/@b') 498b11 b12 b21 22 499select extractValue(@xml,'/a/@b[contains(.,"1")]'); 500extractValue(@xml,'/a/@b[contains(.,"1")]') 501b11 b12 b21 502select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")]'); 503extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")]') 504b12 b21 505select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")][2]'); 506extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")][2]') 507b21 508SET @xml='<a>a1<b>b1<c>c1</c>b2</b>a2</a>'; 509select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','+++++++++'); 510UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','+++++++++') 511<a>a1<b>b1+++++++++b2</b>a2</a> 512select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1>+++++++++</c1>'); 513UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1>+++++++++</c1>') 514<a>a1<b>b1<c1>+++++++++</c1>b2</b>a2</a> 515select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1/>'); 516UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1/>') 517<a>a1<b>b1<c1/>b2</b>a2</a> 518SET @xml='<a><b>bb</b></a>'; 519select UpdateXML(@xml, '/a/b', '<b>ccc</b>'); 520UpdateXML(@xml, '/a/b', '<b>ccc</b>') 521<a><b>ccc</b></a> 522SET @xml='<a aa1="aa1" aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a>'; 523select UpdateXML(@xml, '/a/b', '<b>ccc</b>'); 524UpdateXML(@xml, '/a/b', '<b>ccc</b>') 525<a aa1="aa1" aa2="aa2"><b>ccc</b></a> 526select UpdateXML(@xml, '/a/@aa1', ''); 527UpdateXML(@xml, '/a/@aa1', '') 528<a aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a> 529select UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"'); 530UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"') 531<a aa3="aa3" aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a> 532select UpdateXML(@xml, '/a/@aa2', ''); 533UpdateXML(@xml, '/a/@aa2', '') 534<a aa1="aa1" ><b bb1="bb1" bb2="bb2">bb</b></a> 535select UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"'); 536UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"') 537<a aa1="aa1" aa3="aa3"><b bb1="bb1" bb2="bb2">bb</b></a> 538select UpdateXML(@xml, '/a/b/@bb1', ''); 539UpdateXML(@xml, '/a/b/@bb1', '') 540<a aa1="aa1" aa2="aa2"><b bb2="bb2">bb</b></a> 541select UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"'); 542UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"') 543<a aa1="aa1" aa2="aa2"><b bb3="bb3" bb2="bb2">bb</b></a> 544select UpdateXML(@xml, '/a/b/@bb2', ''); 545UpdateXML(@xml, '/a/b/@bb2', '') 546<a aa1="aa1" aa2="aa2"><b bb1="bb1" >bb</b></a> 547select UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"'); 548UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"') 549<a aa1="aa1" aa2="aa2"><b bb1="bb1" bb3="bb3">bb</b></a> 550select updatexml('<div><div><span>1</span><span>2</span></div></div>', 551'/','<tr><td>1</td><td>2</td></tr>') as upd1; 552upd1 553<tr><td>1</td><td>2</td></tr> 554select updatexml('', '/', '') as upd2; 555upd2 556 557SET @xml= '<order><clerk>lesser wombat</clerk></order>'; 558select extractvalue(@xml,'order/clerk'); 559extractvalue(@xml,'order/clerk') 560lesser wombat 561select extractvalue(@xml,'/order/clerk'); 562extractvalue(@xml,'/order/clerk') 563lesser wombat 564select extractvalue('<a><b>B</b></a>','/a|/b'); 565extractvalue('<a><b>B</b></a>','/a|/b') 566 567select extractvalue('<a><b>B</b></a>','/a|b'); 568extractvalue('<a><b>B</b></a>','/a|b') 569 570select extractvalue('<a>a<b>B</b></a>','/a|/b'); 571extractvalue('<a>a<b>B</b></a>','/a|/b') 572a 573select extractvalue('<a>a<b>B</b></a>','/a|b'); 574extractvalue('<a>a<b>B</b></a>','/a|b') 575a 576select extractvalue('<a>a<b>B</b></a>','a|/b'); 577extractvalue('<a>a<b>B</b></a>','a|/b') 578a 579select extractvalue('<a>A</a>','/<a>'); 580ERROR HY000: XPATH error: comparison of two nodesets is not supported: '<a>' 581select extractvalue('<a><b>b</b><b!>b!</b!></a>','//b!'); 582ERROR HY000: XPATH syntax error: '!' 583select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant::*'); 584extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant::*') 585B C 586select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/self::*'); 587extractvalue('<a>A<b>B<c>C</c></b></a>','/a/self::*') 588A 589select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant-or-self::*'); 590extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant-or-self::*') 591A B C 592select extractvalue('<A_B>A</A_B>','/A_B'); 593extractvalue('<A_B>A</A_B>','/A_B') 594A 595select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[position()]'); 596extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[position()]') 597B1 B2 598select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]'); 599extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]') 600B1 B2 601select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]'); 602extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]') 603B2 604select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]'); 605extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]') 606B1 607select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]'); 608extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]') 609 610select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]'); 611extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]') 612B1 B2 613select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]'); 614extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]') 615B2 616select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)]'); 617extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)]') 618B2 619select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)-1]'); 620extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)-1]') 621B1 622select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=1]'); 623extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=1]') 624 625select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=2]'); 626extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=2]') 627B1 B2 628select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=position()]'); 629extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=position()]') 630B2 631select extractvalue('<a>Jack</a>','/a[contains(../a,"J")]'); 632extractvalue('<a>Jack</a>','/a[contains(../a,"J")]') 633Jack 634select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]'); 635extractvalue('<a>Jack</a>','/a[contains(../a,"j")]') 636Jack 637select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]' collate latin1_bin); 638extractvalue('<a>Jack</a>','/a[contains(../a,"j")]' collate latin1_bin) 639 640select extractvalue('<a>Jack</a>' collate latin1_bin,'/a[contains(../a,"j")]'); 641extractvalue('<a>Jack</a>' collate latin1_bin,'/a[contains(../a,"j")]') 642 643select ExtractValue('<tag1><![CDATA[test]]></tag1>','/tag1'); 644ExtractValue('<tag1><![CDATA[test]]></tag1>','/tag1') 645test 646select extractValue('<a>a','/a'); 647extractValue('<a>a','/a') 648NULL 649Warnings: 650Warning 1525 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' 651select extractValue('<a>a<','/a'); 652extractValue('<a>a<','/a') 653NULL 654Warnings: 655Warning 1525 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' 656select extractValue('<a>a</','/a'); 657extractValue('<a>a</','/a') 658NULL 659Warnings: 660Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' 661select extractValue('<a>a</a','/a'); 662extractValue('<a>a</a','/a') 663NULL 664Warnings: 665Warning 1525 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' 666select extractValue('<a>a</a></b>','/a'); 667extractValue('<a>a</a></b>','/a') 668NULL 669Warnings: 670Warning 1525 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' 671select extractValue('<a b=>a</a>','/a'); 672extractValue('<a b=>a</a>','/a') 673NULL 674Warnings: 675Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' 676select extractValue('<e>1</e>','position()'); 677ERROR HY000: XPATH syntax error: '' 678select extractValue('<e>1</e>','last()'); 679ERROR HY000: XPATH syntax error: '' 680select extractValue('<e><a>1</a></e>','/e/'); 681ERROR HY000: XPATH syntax error: '' 682set names utf8; 683select extractValue('<Ñ><r>r</r></Ñ>','/Ñ/r'); 684extractValue('<Ñ><r>r</r></Ñ>','/Ñ/r') 685r 686select extractValue('<r><Ñ>Ñ</Ñ></r>','/r/Ñ'); 687extractValue('<r><Ñ>Ñ</Ñ></r>','/r/Ñ') 688Ñ 689select extractValue('<Ñ r="r"/>','/Ñ/@r'); 690extractValue('<Ñ r="r"/>','/Ñ/@r') 691r 692select extractValue('<r Ñ="Ñ"/>','/r/@Ñ'); 693extractValue('<r Ñ="Ñ"/>','/r/@Ñ') 694Ñ 695DROP PROCEDURE IF EXISTS p2; 696CREATE PROCEDURE p2 () 697BEGIN 698DECLARE p LONGTEXT CHARACTER SET UTF8 DEFAULT '<Ñ><r>A</r></Ñ>'; 699SELECT EXTRACTVALUE(p,'/Ñ/r'); 700END// 701CALL p2(); 702EXTRACTVALUE(p,'/Ñ/r') 703A 704DROP PROCEDURE p2; 705select extractValue('<ns:element xmlns:ns="myns"/>','count(ns:element)'); 706extractValue('<ns:element xmlns:ns="myns"/>','count(ns:element)') 7071 708select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element'); 709extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element') 710a 711select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element/@xmlns:ns'); 712extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element/@xmlns:ns') 713myns 714select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/foo.bar'); 715extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/foo.bar') 716Data 717select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/something'); 718extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/something') 719Otherdata 720select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','/zot/tim0/02'); 721ERROR HY000: XPATH syntax error: '02' 722select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*'); 723extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*') 724NULL 725Warnings: 726Warning 1525 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' 727select extractValue('<.>test</.>','//*'); 728extractValue('<.>test</.>','//*') 729NULL 730Warnings: 731Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' 732select extractValue('<->test</->','//*'); 733extractValue('<->test</->','//*') 734NULL 735Warnings: 736Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' 737select extractValue('<:>test</:>','//*'); 738extractValue('<:>test</:>','//*') 739test 740select extractValue('<_>test</_>','//*'); 741extractValue('<_>test</_>','//*') 742test 743select extractValue('<x.-_:>test</x.-_:>','//*'); 744extractValue('<x.-_:>test</x.-_:>','//*') 745test 746set @xml= "<entry><id>pt10</id><pt>10</pt></entry><entry><id>pt50</id><pt>50</pt></entry>"; 747select ExtractValue(@xml, "/entry[(pt=10)]/id"); 748ExtractValue(@xml, "/entry[(pt=10)]/id") 749pt10 750select ExtractValue(@xml, "/entry[(pt!=10)]/id"); 751ExtractValue(@xml, "/entry[(pt!=10)]/id") 752pt50 753select ExtractValue(@xml, "/entry[(pt<10)]/id"); 754ExtractValue(@xml, "/entry[(pt<10)]/id") 755 756select ExtractValue(@xml, "/entry[(pt<=10)]/id"); 757ExtractValue(@xml, "/entry[(pt<=10)]/id") 758pt10 759select ExtractValue(@xml, "/entry[(pt>10)]/id"); 760ExtractValue(@xml, "/entry[(pt>10)]/id") 761pt50 762select ExtractValue(@xml, "/entry[(pt>=10)]/id"); 763ExtractValue(@xml, "/entry[(pt>=10)]/id") 764pt10 pt50 765select ExtractValue(@xml, "/entry[(pt=50)]/id"); 766ExtractValue(@xml, "/entry[(pt=50)]/id") 767pt50 768select ExtractValue(@xml, "/entry[(pt!=50)]/id"); 769ExtractValue(@xml, "/entry[(pt!=50)]/id") 770pt10 771select ExtractValue(@xml, "/entry[(pt<50)]/id"); 772ExtractValue(@xml, "/entry[(pt<50)]/id") 773pt10 774select ExtractValue(@xml, "/entry[(pt<=50)]/id"); 775ExtractValue(@xml, "/entry[(pt<=50)]/id") 776pt10 pt50 777select ExtractValue(@xml, "/entry[(pt>50)]/id"); 778ExtractValue(@xml, "/entry[(pt>50)]/id") 779 780select ExtractValue(@xml, "/entry[(pt>=50)]/id"); 781ExtractValue(@xml, "/entry[(pt>=50)]/id") 782pt50 783select ExtractValue(@xml, "/entry[(10=pt)]/id"); 784ExtractValue(@xml, "/entry[(10=pt)]/id") 785pt10 786select ExtractValue(@xml, "/entry[(10!=pt)]/id"); 787ExtractValue(@xml, "/entry[(10!=pt)]/id") 788pt50 789select ExtractValue(@xml, "/entry[(10>pt)]/id"); 790ExtractValue(@xml, "/entry[(10>pt)]/id") 791 792select ExtractValue(@xml, "/entry[(10>=pt)]/id"); 793ExtractValue(@xml, "/entry[(10>=pt)]/id") 794pt10 795select ExtractValue(@xml, "/entry[(10<pt)]/id"); 796ExtractValue(@xml, "/entry[(10<pt)]/id") 797pt50 798select ExtractValue(@xml, "/entry[(10<=pt)]/id"); 799ExtractValue(@xml, "/entry[(10<=pt)]/id") 800pt10 pt50 801select ExtractValue(@xml, "/entry[(50=pt)]/id"); 802ExtractValue(@xml, "/entry[(50=pt)]/id") 803pt50 804select ExtractValue(@xml, "/entry[(50!=pt)]/id"); 805ExtractValue(@xml, "/entry[(50!=pt)]/id") 806pt10 807select ExtractValue(@xml, "/entry[(50>pt)]/id"); 808ExtractValue(@xml, "/entry[(50>pt)]/id") 809pt10 810select ExtractValue(@xml, "/entry[(50>=pt)]/id"); 811ExtractValue(@xml, "/entry[(50>=pt)]/id") 812pt10 pt50 813select ExtractValue(@xml, "/entry[(50<pt)]/id"); 814ExtractValue(@xml, "/entry[(50<pt)]/id") 815 816select ExtractValue(@xml, "/entry[(50<=pt)]/id"); 817ExtractValue(@xml, "/entry[(50<=pt)]/id") 818pt50 819select ExtractValue('<a><b><Text>test</Text></b></a>','/a/b/Text'); 820ExtractValue('<a><b><Text>test</Text></b></a>','/a/b/Text') 821test 822select ExtractValue('<a><b><comment>test</comment></b></a>','/a/b/comment'); 823ExtractValue('<a><b><comment>test</comment></b></a>','/a/b/comment') 824test 825select ExtractValue('<a><b><node>test</node></b></a>','/a/b/node'); 826ExtractValue('<a><b><node>test</node></b></a>','/a/b/node') 827test 828select ExtractValue('<a><b><processing-instruction>test</processing-instruction></b></a>','/a/b/processing-instruction'); 829ExtractValue('<a><b><processing-instruction>test</processing-instruction></b></a>','/a/b/processing-instruction') 830test 831select ExtractValue('<a><and>test</and></a>', '/a/and'); 832ExtractValue('<a><and>test</and></a>', '/a/and') 833test 834select ExtractValue('<a><or>test</or></a>', '/a/or'); 835ExtractValue('<a><or>test</or></a>', '/a/or') 836test 837select ExtractValue('<a><mod>test</mod></a>', '/a/mod'); 838ExtractValue('<a><mod>test</mod></a>', '/a/mod') 839test 840select ExtractValue('<a><div>test</div></a>', '/a/div'); 841ExtractValue('<a><div>test</div></a>', '/a/div') 842test 843select ExtractValue('<a><and:and>test</and:and></a>', '/a/and:and'); 844ExtractValue('<a><and:and>test</and:and></a>', '/a/and:and') 845test 846select ExtractValue('<a><or:or>test</or:or></a>', '/a/or:or'); 847ExtractValue('<a><or:or>test</or:or></a>', '/a/or:or') 848test 849select ExtractValue('<a><mod:mod>test</mod:mod></a>', '/a/mod:mod'); 850ExtractValue('<a><mod:mod>test</mod:mod></a>', '/a/mod:mod') 851test 852select ExtractValue('<a><div:div>test</div:div></a>', '/a/div:div'); 853ExtractValue('<a><div:div>test</div:div></a>', '/a/div:div') 854test 855select ExtractValue('<a><ancestor>test</ancestor></a>', '/a/ancestor'); 856ExtractValue('<a><ancestor>test</ancestor></a>', '/a/ancestor') 857test 858select ExtractValue('<a><ancestor-or-self>test</ancestor-or-self></a>', '/a/ancestor-or-self'); 859ExtractValue('<a><ancestor-or-self>test</ancestor-or-self></a>', '/a/ancestor-or-self') 860test 861select ExtractValue('<a><attribute>test</attribute></a>', '/a/attribute'); 862ExtractValue('<a><attribute>test</attribute></a>', '/a/attribute') 863test 864select ExtractValue('<a><child>test</child></a>', '/a/child'); 865ExtractValue('<a><child>test</child></a>', '/a/child') 866test 867select ExtractValue('<a><descendant>test</descendant></a>', '/a/descendant'); 868ExtractValue('<a><descendant>test</descendant></a>', '/a/descendant') 869test 870select ExtractValue('<a><descendant-or-self>test</descendant-or-self></a>', '/a/descendant-or-self'); 871ExtractValue('<a><descendant-or-self>test</descendant-or-self></a>', '/a/descendant-or-self') 872test 873select ExtractValue('<a><following>test</following></a>', '/a/following'); 874ExtractValue('<a><following>test</following></a>', '/a/following') 875test 876select ExtractValue('<a><following-sibling>test</following-sibling></a>', '/a/following-sibling'); 877ExtractValue('<a><following-sibling>test</following-sibling></a>', '/a/following-sibling') 878test 879select ExtractValue('<a><namespace>test</namespace></a>', '/a/namespace'); 880ExtractValue('<a><namespace>test</namespace></a>', '/a/namespace') 881test 882select ExtractValue('<a><parent>test</parent></a>', '/a/parent'); 883ExtractValue('<a><parent>test</parent></a>', '/a/parent') 884test 885select ExtractValue('<a><preceding>test</preceding></a>', '/a/preceding'); 886ExtractValue('<a><preceding>test</preceding></a>', '/a/preceding') 887test 888select ExtractValue('<a><preceding-sibling>test</preceding-sibling></a>', '/a/preceding-sibling'); 889ExtractValue('<a><preceding-sibling>test</preceding-sibling></a>', '/a/preceding-sibling') 890test 891select ExtractValue('<a><self>test</self></a>', '/a/self'); 892ExtractValue('<a><self>test</self></a>', '/a/self') 893test 894SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 895Warnings: 896Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 897set @i=1; 898select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 899ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 900b1 901set @i=2; 902select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 903ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 904b2 905set @i=NULL; 906select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 907ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 908 909CREATE PROCEDURE spxml(xml VARCHAR(128)) 910BEGIN 911DECLARE c INT; 912DECLARE i INT DEFAULT 1; 913SET c= ExtractValue(xml,'count(/a/b)'); 914SET @i= c; 915WHILE i <= c DO 916BEGIN 917SELECT i, @i, ExtractValue(xml,'/a/b[$i]'), ExtractValue(xml,'/a/b[$@i]'); 918SET i= i + 1; 919SET @i= @i - 1; 920END; 921END WHILE; 922END| 923call spxml('<a><b>b1</b><b>b2</b><b>b3</b></a>'); 924i @i ExtractValue(xml,'/a/b[$i]') ExtractValue(xml,'/a/b[$@i]') 9251 3 b1 b3 926i @i ExtractValue(xml,'/a/b[$i]') ExtractValue(xml,'/a/b[$@i]') 9272 2 b2 b2 928i @i ExtractValue(xml,'/a/b[$i]') ExtractValue(xml,'/a/b[$@i]') 9293 1 b3 b1 930drop procedure spxml; 931Multiple matches, but no index specification 932SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b'); 933ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b') 934b1 b2 935No matches 936SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/c'); 937ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/c') 938 939Index out of range 940SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[-1]'); 941ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[-1]') 942 943SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[10]'); 944ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[10]') 945 946With string-to-number conversion 947SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1"]'); 948ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1"]') 949b1 950SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1 and string"]'); 951ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1 and string"]') 952b1 953Warnings: 954Warning 1292 Truncated incorrect INTEGER value: '1 and string"]' 955Warning 1292 Truncated incorrect INTEGER value: '1 and string"]' 956SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["string and 1"]'); 957ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["string and 1"]') 958 959Warnings: 960Warning 1292 Truncated incorrect INTEGER value: 'string and 1"]' 961Warning 1292 Truncated incorrect INTEGER value: 'string and 1"]' 962SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["string"]'); 963ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["string"]') 964 965Warnings: 966Warning 1292 Truncated incorrect INTEGER value: 'string"]' 967Warning 1292 Truncated incorrect INTEGER value: 'string"]' 968String-to-number conversion from a user variable 969SET @i='1'; 970SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 971ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 972b1 973SET @i='1 and string'; 974SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 975ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 976b1 977SET @i='string and 1'; 978SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 979ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 980 981SET @i='string'; 982SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 983ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]') 984 985String-to-number conversion with a CHAR SP variable 986CREATE PROCEDURE spxml(xml VARCHAR(128), i CHAR(16)) 987BEGIN 988SELECT ExtractValue(xml,'/a/b[$i]'); 989END| 990CALL spxml('<a><b>b1</b><b>b2</b></a>', '1'); 991ExtractValue(xml,'/a/b[$i]') 992b1 993CALL spxml('<a><b>b1</b><b>b2</b></a>', '1 and string'); 994ExtractValue(xml,'/a/b[$i]') 995b1 996Warnings: 997Warning 1292 Truncated incorrect INTEGER value: '1 and string ' 998Warning 1292 Truncated incorrect INTEGER value: '1 and string ' 999CALL spxml('<a><b>b1</b><b>b2</b></a>', 'string and 1'); 1000ExtractValue(xml,'/a/b[$i]') 1001 1002Warnings: 1003Warning 1292 Truncated incorrect INTEGER value: 'string and 1 ' 1004Warning 1292 Truncated incorrect INTEGER value: 'string and 1 ' 1005CALL spxml('<a><b>b1</b><b>b2</b></a>', 'string'); 1006ExtractValue(xml,'/a/b[$i]') 1007 1008Warnings: 1009Warning 1292 Truncated incorrect INTEGER value: 'string ' 1010Warning 1292 Truncated incorrect INTEGER value: 'string ' 1011DROP PROCEDURE spxml; 1012SET sql_mode = default; 1013select UpdateXML('<a>a</a>',repeat('a b ',1000),''); 1014ERROR HY000: XPATH syntax error: 'b a b a b a b a b a b a b a b a ' 1015select ExtractValue('<a>a</a>', '/a[@x=@y0123456789_0123456789_0123456789_0123456789]'); 1016ERROR HY000: XPATH error: comparison of two nodesets is not supported: '=@y0123456789_0123456789_0123456' 1017select ExtractValue('<a>a</a>', '/a[@x=$y0123456789_0123456789_0123456789_0123456789]'); 1018ERROR HY000: Unknown XPATH variable at: '$y0123456789_0123456789_01234567' 1019select updatexml(NULL, 1, 1), updatexml(1, NULL, 1), updatexml(1, 1, NULL); 1020updatexml(NULL, 1, 1) updatexml(1, NULL, 1) updatexml(1, 1, NULL) 1021NULL NULL NULL 1022select updatexml(NULL, NULL, 1), updatexml(1, NULL, NULL), 1023updatexml(NULL, 1, NULL); 1024updatexml(NULL, NULL, 1) updatexml(1, NULL, NULL) updatexml(NULL, 1, NULL) 1025NULL NULL NULL 1026select updatexml(NULL, NULL, NULL); 1027updatexml(NULL, NULL, NULL) 1028NULL 1029CREATE TABLE t1(a INT NOT NULL); 1030INSERT INTO t1 VALUES (0), (0); 1031SELECT 1 FROM t1 ORDER BY(UPDATEXML(a, '1', '1')); 10321 10331 10341 1035DROP TABLE t1; 1036SET @xml= 1037'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 1038<html> 1039 <head> 1040 <title> Title - document with document declaration</title> 1041 </head> 1042 <body> Hi, Im a webpage with document a declaration </body> 1043</html>'; 1044SELECT ExtractValue(@xml, 'html/head/title'); 1045ExtractValue(@xml, 'html/head/title') 1046 Title - document with document declaration 1047SELECT ExtractValue(@xml, 'html/body'); 1048ExtractValue(@xml, 'html/body') 1049 Hi, Im a webpage with document a declaration 1050SELECT ExtractValue('<xml "xxx" "yyy">CharData</xml>', '/xml'); 1051ExtractValue('<xml "xxx" "yyy">CharData</xml>', '/xml') 1052NULL 1053Warnings: 1054Warning 1525 Incorrect XML value: 'parse error at line 1 pos 11: STRING unexpected ('>' wanted)' 1055SELECT ExtractValue('<xml xxx "yyy">CharData</xml>', '/xml'); 1056ExtractValue('<xml xxx "yyy">CharData</xml>', '/xml') 1057NULL 1058Warnings: 1059Warning 1525 Incorrect XML value: 'parse error at line 1 pos 17: STRING unexpected ('>' wanted)' 1060set @x=10; 1061select extractvalue('<a></a>','$@x/a'); 1062ERROR HY000: XPATH syntax error: '/a' 1063select extractvalue('<a></a>','round(123.4)/a'); 1064ERROR HY000: XPATH syntax error: '/a' 1065select extractvalue('<a></a>','1/a'); 1066ERROR HY000: XPATH syntax error: '/a' 1067select extractvalue('<a></a>','"b"/a'); 1068ERROR HY000: XPATH syntax error: '/a' 1069select extractvalue('<a></a>','(1)/a'); 1070ERROR HY000: XPATH syntax error: '/a' 1071CREATE TABLE IF NOT EXISTS t1 ( 1072id int(10) unsigned NOT NULL AUTO_INCREMENT, 1073xml text, 1074PRIMARY KEY (id) 1075); 1076INSERT INTO t1 (id, xml) VALUES 1077(15, '<?xml version="1.0"?><bla name="blubb"></bla>'), 1078(14, '<xml version="kaputt">'); 1079SELECT 1080extractvalue( xml, '/bla/@name' ), 1081extractvalue( xml, '/bla/@name' ) 1082FROM t1 ORDER BY t1.id; 1083extractvalue( xml, '/bla/@name' ) extractvalue( xml, '/bla/@name' ) 1084NULL NULL 1085blubb blubb 1086Warnings: 1087Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' 1088Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' 1089SELECT 1090UpdateXML(xml, '/bla/@name', 'test'), 1091UpdateXML(xml, '/bla/@name', 'test') 1092FROM t1 ORDER BY t1.id; 1093UpdateXML(xml, '/bla/@name', 'test') UpdateXML(xml, '/bla/@name', 'test') 1094NULL NULL 1095<?xml version="1.0"?><bla test></bla> <?xml version="1.0"?><bla test></bla> 1096Warnings: 1097Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' 1098Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' 1099DROP TABLE t1; 1100# 1101# Bug#57257 Replace(ExtractValue(...)) causes MySQL crash 1102# 1103SET NAMES utf8; 1104SELECT REPLACE(EXTRACTVALUE('1', '/a'),'ds',''); 1105REPLACE(EXTRACTVALUE('1', '/a'),'ds','') 1106 1107# 1108# Bug #57820 extractvalue crashes 1109# 1110SELECT AVG(DISTINCT EXTRACTVALUE((''),('$@k'))); 1111AVG(DISTINCT EXTRACTVALUE((''),('$@k'))) 1112NULL 1113# 1114# Bug#57279 updatexml dies with: Assertion failed: str_arg[length] == 0 1115# 1116SELECT UPDATEXML(NULL, (LPAD(0.1111E-15, '2011', 1)), 1); 1117ERROR 22007: Illegal double '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111' value found during parsing 1118SELECT EXTRACTVALUE('', LPAD(0.1111E-15, '2011', 1)); 1119ERROR 22007: Illegal double '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111' value found during parsing 1120# 1121# Bug #44332 my_xml_scan reads behind the end of buffer 1122# 1123SELECT UPDATEXML(CONVERT(_latin1'<' USING utf8),'1','1'); 1124UPDATEXML(CONVERT(_latin1'<' USING utf8),'1','1') 1125NULL 1126Warnings: 1127Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: END-OF-INPUT unexpected (ident or '/' wanted)' 1128SELECT UPDATEXML(CONVERT(_latin1'<!--' USING utf8),'1','1'); 1129UPDATEXML(CONVERT(_latin1'<!--' USING utf8),'1','1') 1130NULL 1131# 1132# Bug#11766725 (bug#59901): EXTRACTVALUE STILL BROKEN AFTER FIX FOR BUG #44332 1133# 1134SELECT ExtractValue(CONVERT('<\"', BINARY(10)), 1); 1135ExtractValue(CONVERT('<\"', BINARY(10)), 1) 1136NULL 1137Warnings: 1138Warning 1525 Incorrect XML value: 'parse error at line 1 pos 11: STRING unexpected (ident or '/' wanted)' 1139End of 5.1 tests 1140# 1141# Start of 5.5 tests 1142# 1143# 1144# Bug#58175 xml functions read initialized bytes when conversions happen 1145# 1146SET NAMES latin1; 1147SELECT UPDATEXML(CONVERT('' USING swe7), TRUNCATE('',1), 0); 1148UPDATEXML(CONVERT('' USING swe7), TRUNCATE('',1), 0) 1149NULL 1150# 1151# Bug#12375190: UPDATEXML CRASHES ON SIMPLE INPUTS 1152# 1153SELECT UPDATEXML('','(a)/a',''); 1154UPDATEXML('','(a)/a','') 1155 1156SELECT UPDATEXML('<a><a>x</a></a>','(a)/a','<b />'); 1157UPDATEXML('<a><a>x</a></a>','(a)/a','<b />') 1158<a><b /></a> 1159SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)/a','<b />'); 1160UPDATEXML('<a><c><a>x</a></c></a>','(a)/a','<b />') 1161<a><c><a>x</a></c></a> 1162SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','<b />'); 1163UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','<b />') 1164<a><c><b /></c></a> 1165SELECT ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b'); 1166ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b') 1167aa bb 1168# 1169# End of 5.5 tests 1170# 1171# 1172# Bug#13358486 WEIGHT_STRING = MY_STRNXFRM_UNICODE: 1173# ASSERTION `SRC' FAILED 1174# 1175set names utf8; 1176do weight_string(extractvalue('','/*/a') level 1 reverse); 1177do char((weight_string(extractvalue((''),('tX')) level 7 desc)) using cp852); 1178set names default; 1179# 1180# Bug#22552615 EXTRACTVALUE RETURNS NULL WHEN NO MATCHING 1181# TEXT NODE IS FOUND FOR THE EXPRESSION 1182# 1183set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>'; 1184set @y = 'Default Value'; 1185select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y; 1186select @y; 1187@y 1188 1189