1 2SET @xml='<a aa1="aa1" aa2="aa2">a1<b ba1="ba1">b1<c>c1</c>b2</b>a2</a>'; 3SELECT extractValue(@xml,'/a'); 4SELECT extractValue(@xml,'/a/b'); 5SELECT extractValue(@xml,'/a/b/c'); 6SELECT extractValue(@xml,'/a/@aa1'); 7SELECT extractValue(@xml,'/a/@aa2'); 8SELECT extractValue(@xml,'/a/@*'); 9SELECT extractValue(@xml,'//@ba1'); 10 11SELECT extractValue(@xml,'//a'); 12SELECT extractValue(@xml,'//b'); 13SELECT extractValue(@xml,'//c'); 14SELECT extractValue(@xml,'/a//b'); 15SELECT extractValue(@xml,'/a//c'); 16SELECT extractValue(@xml,'//*'); 17SELECT extractValue(@xml,'/a//*'); 18SELECT extractValue(@xml,'/./a'); 19SELECT extractValue(@xml,'/a/b/.'); 20SELECT extractValue(@xml,'/a/b/..'); 21SELECT extractValue(@xml,'/a/b/../@aa1'); 22SELECT extractValue(@xml,'/*'); 23SELECT extractValue(@xml,'/*/*'); 24SELECT extractValue(@xml,'/*/*/*'); 25 26SELECT extractValue(@xml,'/a/child::*'); 27SELECT extractValue(@xml,'/a/self::*'); 28SELECT extractValue(@xml,'/a/descendant::*'); 29SELECT extractValue(@xml,'/a/descendant-or-self::*'); 30SELECT extractValue(@xml,'/a/attribute::*'); 31SELECT extractValue(@xml,'/a/b/c/parent::*'); 32SELECT extractValue(@xml,'/a/b/c/ancestor::*'); 33SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*'); 34SELECT extractValue(@xml,'/descendant-or-self::*'); 35 36SET @xml='<a>a11<b ba="ba11" ba="ba12">b11</b><b ba="ba21" ba="ba22">b21<c>c1</c>b22</b>a12</a>'; 37SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*'); 38SELECT extractValue(@xml,'//@ba'); 39 40SET @xml='<a><b>b</b><c>c</c></a>'; 41SELECT extractValue(@xml,'/a/b'); 42SELECT extractValue(@xml,'/a/c'); 43SELECT extractValue(@xml,'/a/child::b'); 44SELECT extractValue(@xml,'/a/child::c'); 45 46SET @xml='<a><b>b1</b><c>c1</c><b>b2</b><c>c2</c></a>'; 47SELECT extractValue(@xml,'/a/b[1]'); 48SELECT extractValue(@xml,'/a/b[2]'); 49SELECT extractValue(@xml,'/a/c[1]'); 50SELECT extractValue(@xml,'/a/c[2]'); 51 52SET @xml='<a><b x="xb1" x="xb2"/><c x="xc1" x="xc2"/></a>'; 53SELECT extractValue(@xml,'/a//@x'); 54SELECT extractValue(@xml,'/a//@x[1]'); 55SELECT extractValue(@xml,'/a//@x[2]'); 56 57SET @xml='<a><b>b1</b><b>b2</b><c><b>c1b1</b><b>c1b2</b></c><c><b>c2b1</c></b></a>'; 58SELECT extractValue(@xml,'//b[1]'); 59SELECT extractValue(@xml,'/descendant::b[1]'); 60 61SET @xml='<a><b>b1</b><b>b2</b></a>'; 62SELECT extractValue(@xml,'/a/b[1+0]'); 63SELECT extractValue(@xml,'/a/b[1*1]'); 64SELECT extractValue(@xml,'/a/b[--1]'); 65SELECT extractValue(@xml,'/a/b[2*1-1]'); 66 67SELECT extractValue(@xml,'/a/b[1+1]'); 68SELECT extractValue(@xml,'/a/b[1*2]'); 69SELECT extractValue(@xml,'/a/b[--2]'); 70SELECT extractValue(@xml,'/a/b[1*(3-1)]'); 71 72SELECT extractValue(@xml,'//*[1=1]'); 73SELECT extractValue(@xml,'//*[1!=1]'); 74SELECT extractValue(@xml,'//*[1>1]'); 75SELECT extractValue(@xml,'//*[2>1]'); 76SELECT extractValue(@xml,'//*[1>2]'); 77SELECT extractValue(@xml,'//*[1>=1]'); 78SELECT extractValue(@xml,'//*[2>=1]'); 79SELECT extractValue(@xml,'//*[1>=2]'); 80SELECT extractValue(@xml,'//*[1<1]'); 81SELECT extractValue(@xml,'//*[2<1]'); 82SELECT extractValue(@xml,'//*[1<2]'); 83SELECT extractValue(@xml,'//*[1<=1]'); 84SELECT extractValue(@xml,'//*[2<=1]'); 85SELECT extractValue(@xml,'//*[1<=2]'); 86 87SET @xml='<a><b>b11<c>c11</c></b><b>b21<c>c21</c></b></a>'; 88SELECT extractValue(@xml,'/a/b[c="c11"]'); 89SELECT extractValue(@xml,'/a/b[c="c21"]'); 90 91SET @xml='<a><b c="c11">b11</b><b c="c21">b21</b></a>'; 92SELECT extractValue(@xml,'/a/b[@c="c11"]'); 93SELECT extractValue(@xml,'/a/b[@c="c21"]'); 94 95SET @xml='<a>a1<b c="c11">b11<d>d11</d></b><b c="c21">b21<d>d21</d></b></a>'; 96SELECT extractValue(@xml, '/a/b[@c="c11"]/d'); 97SELECT extractValue(@xml, '/a/b[@c="c21"]/d'); 98SELECT extractValue(@xml, '/a/b[d="d11"]/@c'); 99SELECT extractValue(@xml, '/a/b[d="d21"]/@c'); 100SELECT extractValue(@xml, '/a[b="b11"]'); 101SELECT extractValue(@xml, '/a[b/@c="c11"]'); 102SELECT extractValue(@xml, '/a[b/d="d11"]'); 103SELECT extractValue(@xml, '/a[/a/b="b11"]'); 104SELECT extractValue(@xml, '/a[/a/b/@c="c11"]'); 105SELECT extractValue(@xml, '/a[/a/b/d="d11"]'); 106 107SELECT extractValue('<a>a</a>', '/a[false()]'); 108SELECT extractValue('<a>a</a>', '/a[true()]'); 109SELECT extractValue('<a>a</a>', '/a[not(false())]'); 110SELECT extractValue('<a>a</a>', '/a[not(true())]'); 111SELECT extractValue('<a>a</a>', '/a[true() and true()]'); 112SELECT extractValue('<a>a</a>', '/a[true() and false()]'); 113SELECT extractValue('<a>a</a>', '/a[false()and false()]'); 114SELECT extractValue('<a>a</a>', '/a[false()and true()]'); 115SELECT extractValue('<a>a</a>', '/a[true() or true()]'); 116SELECT extractValue('<a>a</a>', '/a[true() or false()]'); 117SELECT extractValue('<a>a</a>', '/a[false()or false()]'); 118SELECT extractValue('<a>a</a>', '/a[false()or true()]'); 119 120SET @xml='<a>ab<b c="c" c="e">b1</b><b c="d">b2</b><b c="f" c="e">b3</b></a>'; 121select extractValue(@xml,'/a/b[@c="c"]'); 122select extractValue(@xml,'/a/b[@c="d"]'); 123select extractValue(@xml,'/a/b[@c="e"]'); 124select extractValue(@xml,'/a/b[not(@c="e")]'); 125select extractValue(@xml,'/a/b[@c!="e"]'); 126select extractValue(@xml,'/a/b[@c="c" or @c="d"]'); 127select extractValue(@xml,'/a/b[@c="c" and @c="e"]'); 128 129SET @xml='<a><b c="c" d="d">b1</b><b d="d" e="e">b2</b></a>'; 130select extractValue(@xml,'/a/b[@c]'); 131select extractValue(@xml,'/a/b[@d]'); 132select extractValue(@xml,'/a/b[@e]'); 133select extractValue(@xml,'/a/b[not(@c)]'); 134select extractValue(@xml,'/a/b[not(@d)]'); 135select extractValue(@xml,'/a/b[not(@e)]'); 136 137select extractValue(@xml, '/a/b[boolean(@c) or boolean(@d)]'); 138select extractValue(@xml, '/a/b[boolean(@c) or boolean(@e)]'); 139select extractValue(@xml, '/a/b[boolean(@d) or boolean(@e)]'); 140select extractValue(@xml, '/a/b[boolean(@c) and boolean(@d)]'); 141select extractValue(@xml, '/a/b[boolean(@c) and boolean(@e)]'); 142select extractValue(@xml, '/a/b[boolean(@d) and boolean(@e)]'); 143 144select extractValue(@xml, '/a/b[@c or @d]'); 145select extractValue(@xml, '/a/b[@c or @e]'); 146select extractValue(@xml, '/a/b[@d or @e]'); 147select extractValue(@xml, '/a/b[@c and @d]'); 148select extractValue(@xml, '/a/b[@c and @e]'); 149select extractValue(@xml, '/a/b[@d and @e]'); 150 151SET @xml='<a><b c="c">b1</b><b>b2</b></a>'; 152SELECT extractValue(@xml,'/a/b[@*]'); 153SELECT extractValue(@xml,'/a/b[not(@*)]'); 154 155SELECT extractValue('<a>a</a>', '/a[ceiling(3.1)=4]'); 156SELECT extractValue('<a>a</a>', '/a[floor(3.1)=3]'); 157SELECT extractValue('<a>a</a>', '/a[round(3.1)=3]'); 158SELECT extractValue('<a>a</a>', '/a[round(3.8)=4]'); 159 160SELECT extractValue('<a><b>b</b><c>c</c></a>', '/a/b | /a/c'); 161 162select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=1]'); 163select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=2]'); 164select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3]'); 165select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[1=position()]'); 166select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2=position()]'); 167select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[3=position()]'); 168select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2>=position()]'); 169select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2<=position()]'); 170select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3 or position()=2]'); 171 172SELECT extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=0]'); 173SELECT extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=1]'); 174select extractValue('<a>a1<b ba="1" ba="2">b1</b><b>b2</b>4</a>','/a/b[sum(@ba)=3]'); 175 176select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[1]'); 177select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[boolean(1)]'); 178select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[true()]'); 179select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[number(true())]'); 180 181select extractValue('<a>ab</a>','/a[contains("abc","b")]'); 182select extractValue('<a>ab</a>','/a[contains(.,"a")]'); 183select extractValue('<a>ab</a>','/a[contains(.,"b")]'); 184select extractValue('<a>ab</a>','/a[contains(.,"c")]'); 185 186select extractValue('<a b="1">ab</a>','/a[concat(@b,"2")="12"]'); 187 188SET @xml='<a b="11" b="12" b="21" b="22">ab</a>'; 189select extractValue(@xml, '/a/@b[substring(.,2)="1"]'); 190select extractValue(@xml, '/a/@b[substring(.,2)="2"]'); 191select extractValue(@xml, '/a/@b[substring(.,1,1)="1"]'); 192select extractValue(@xml, '/a/@b[substring(.,1,1)="2"]'); 193select extractValue(@xml, '/a/@b[substring(.,2,1)="1"]'); 194select extractValue(@xml, '/a/@b[substring(.,2,1)="2"]'); 195 196# 197# Bug#16319: XML: extractvalue() returns syntax errors for some functions 198# 199SET @xml='<a><b>b1</b><b>b2</b></a>'; 200SELECT extractValue(@xml, '/a/b[string-length("x")=1]'); 201SELECT extractValue(@xml, '/a/b[string-length("xx")=2]'); 202SELECT extractValue(@xml, '/a/b[string-length("xxx")=2]'); 203SELECT extractValue(@xml, '/a/b[string-length("x")]'); 204SELECT extractValue(@xml, '/a/b[string-length("xx")]'); 205SELECT extractValue(@xml, '/a/b[string-length()]'); 206--error 1105 207SELECT extractValue(@xml, 'string-length()'); 208SELECT extractValue(@xml, 'string-length("x")'); 209 210SET @xml='<a b="b11" b="b12" b="b21" b="22"/>'; 211select extractValue(@xml,'/a/@b'); 212select extractValue(@xml,'/a/@b[contains(.,"1")]'); 213select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")]'); 214select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")][2]'); 215 216SET @xml='<a>a1<b>b1<c>c1</c>b2</b>a2</a>'; 217select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','+++++++++'); 218select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1>+++++++++</c1>'); 219select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1/>'); 220 221SET @xml='<a><b>bb</b></a>'; 222select UpdateXML(@xml, '/a/b', '<b>ccc</b>'); 223 224SET @xml='<a aa1="aa1" aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a>'; 225select UpdateXML(@xml, '/a/b', '<b>ccc</b>'); 226select UpdateXML(@xml, '/a/@aa1', ''); 227select UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"'); 228select UpdateXML(@xml, '/a/@aa2', ''); 229select UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"'); 230select UpdateXML(@xml, '/a/b/@bb1', ''); 231select UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"'); 232select UpdateXML(@xml, '/a/b/@bb2', ''); 233select UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"'); 234 235# 236# Bug#27898 UPDATEXML Crashes the Server! 237# 238select updatexml('<div><div><span>1</span><span>2</span></div></div>', 239 '/','<tr><td>1</td><td>2</td></tr>') as upd1; 240select updatexml('', '/', '') as upd2; 241 242# 243# Bug#16234 XML: Crash if ExtractValue() 244# 245SET @xml= '<order><clerk>lesser wombat</clerk></order>'; 246select extractvalue(@xml,'order/clerk'); 247select extractvalue(@xml,'/order/clerk'); 248 249# 250# Bug#16314 XML: extractvalue() crash if vertical bar 251# 252select extractvalue('<a><b>B</b></a>','/a|/b'); 253select extractvalue('<a><b>B</b></a>','/a|b'); 254select extractvalue('<a>a<b>B</b></a>','/a|/b'); 255select extractvalue('<a>a<b>B</b></a>','/a|b'); 256select extractvalue('<a>a<b>B</b></a>','a|/b'); 257 258# 259# Bug#16312 XML: extractvalue() crash if angle brackets 260# 261--error 1105 262select extractvalue('<a>A</a>','/<a>'); 263 264# 265# Bug#16313 XML: extractvalue() ignores '!' in names 266# 267--error 1105 268select extractvalue('<a><b>b</b><b!>b!</b!></a>','//b!'); 269 270# 271# Bug #16315 XML: extractvalue() handles self badly 272# 273select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant::*'); 274select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/self::*'); 275select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant-or-self::*'); 276# Bug #16320 XML: extractvalue() won't accept names containing underscores 277# 278select extractvalue('<A_B>A</A_B>','/A_B'); 279 280# 281# Bug#16318: XML: extractvalue() incorrectly returns last() = 1 282# 283select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[position()]'); 284select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]'); 285select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]'); 286select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]'); 287select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]'); 288select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]'); 289select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]'); 290select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)]'); 291select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)-1]'); 292select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=1]'); 293select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=2]'); 294select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=position()]'); 295# 296# Bug#16316: XML: extractvalue() is case-sensitive with contains() 297# 298select extractvalue('<a>Jack</a>','/a[contains(../a,"J")]'); 299select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]'); 300select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]' collate latin1_bin); 301select extractvalue('<a>Jack</a>' collate latin1_bin,'/a[contains(../a,"j")]'); 302 303# 304# Bug#18285: ExtractValue not returning character 305# data within <![CDATA[]]> as expected 306# 307select ExtractValue('<tag1><![CDATA[test]]></tag1>','/tag1'); 308 309# 310# Bug#18201: XML: ExtractValue works even if the xml fragment 311# is not well-formed xml 312# 313select extractValue('<a>a','/a'); 314select extractValue('<a>a<','/a'); 315select extractValue('<a>a</','/a'); 316select extractValue('<a>a</a','/a'); 317select extractValue('<a>a</a></b>','/a'); 318select extractValue('<a b=>a</a>','/a'); 319 320# 321# Bug #18171 XML: ExtractValue: the XPath position() 322# function crashes the server! 323# 324--error 1105 325select extractValue('<e>1</e>','position()'); 326--error 1105 327select extractValue('<e>1</e>','last()'); 328 329 330# 331# Bug #18172 XML: Extractvalue() accepts mallformed 332# XPath without a XPath syntax error 333# 334--error 1105 335select extractValue('<e><a>1</a></e>','/e/'); 336 337# 338# Bug#16233: XML: ExtractValue() fails with special characters 339# 340set names utf8; 341select extractValue('<Ñ><r>r</r></Ñ>','/Ñ/r'); 342select extractValue('<r><Ñ>Ñ</Ñ></r>','/r/Ñ'); 343select extractValue('<Ñ r="r"/>','/Ñ/@r'); 344select extractValue('<r Ñ="Ñ"/>','/r/@Ñ'); 345--disable_warnings 346DROP PROCEDURE IF EXISTS p2; 347--enable_warnings 348DELIMITER //; 349CREATE PROCEDURE p2 () 350BEGIN 351 DECLARE p LONGTEXT CHARACTER SET UTF8 DEFAULT '<Ñ><r>A</r></Ñ>'; 352 SELECT EXTRACTVALUE(p,'/Ñ/r'); 353END// 354DELIMITER ;// 355CALL p2(); 356DROP PROCEDURE p2; 357 358# 359# Bug#18170: XML: ExtractValue(): 360# XPath expression can't use QNames (colon in names) 361# 362select extractValue('<ns:element xmlns:ns="myns"/>','count(ns:element)'); 363select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element'); 364select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element/@xmlns:ns'); 365 366# 367# Bug#20795 extractvalue() won't accept names containing a dot (.) 368# 369select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/foo.bar'); 370select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/something'); 371 372# 373# Bug#20854 XML functions: wrong result in ExtractValue 374# 375--error 1105 376select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','/zot/tim0/02'); 377select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*'); 378# dot and dash are bad identtifier start character 379select extractValue('<.>test</.>','//*'); 380select extractValue('<->test</->','//*'); 381# semicolon is good identifier start character 382select extractValue('<:>test</:>','//*'); 383# underscore is good identifier start character 384select extractValue('<_>test</_>','//*'); 385# dot, dash, underscore and semicolon are good identifier middle characters 386select extractValue('<x.-_:>test</x.-_:>','//*'); 387 388# 389# Bug#22823 gt and lt operators appear to be 390# reversed in ExtractValue() command 391# 392set @xml= "<entry><id>pt10</id><pt>10</pt></entry><entry><id>pt50</id><pt>50</pt></entry>"; 393select ExtractValue(@xml, "/entry[(pt=10)]/id"); 394select ExtractValue(@xml, "/entry[(pt!=10)]/id"); 395select ExtractValue(@xml, "/entry[(pt<10)]/id"); 396select ExtractValue(@xml, "/entry[(pt<=10)]/id"); 397select ExtractValue(@xml, "/entry[(pt>10)]/id"); 398select ExtractValue(@xml, "/entry[(pt>=10)]/id"); 399select ExtractValue(@xml, "/entry[(pt=50)]/id"); 400select ExtractValue(@xml, "/entry[(pt!=50)]/id"); 401select ExtractValue(@xml, "/entry[(pt<50)]/id"); 402select ExtractValue(@xml, "/entry[(pt<=50)]/id"); 403select ExtractValue(@xml, "/entry[(pt>50)]/id"); 404select ExtractValue(@xml, "/entry[(pt>=50)]/id"); 405select ExtractValue(@xml, "/entry[(10=pt)]/id"); 406select ExtractValue(@xml, "/entry[(10!=pt)]/id"); 407select ExtractValue(@xml, "/entry[(10>pt)]/id"); 408select ExtractValue(@xml, "/entry[(10>=pt)]/id"); 409select ExtractValue(@xml, "/entry[(10<pt)]/id"); 410select ExtractValue(@xml, "/entry[(10<=pt)]/id"); 411select ExtractValue(@xml, "/entry[(50=pt)]/id"); 412select ExtractValue(@xml, "/entry[(50!=pt)]/id"); 413select ExtractValue(@xml, "/entry[(50>pt)]/id"); 414select ExtractValue(@xml, "/entry[(50>=pt)]/id"); 415select ExtractValue(@xml, "/entry[(50<pt)]/id"); 416select ExtractValue(@xml, "/entry[(50<=pt)]/id"); 417 418# 419# Bug#24747 XPath error with the node name "Text" 420# 421# 422# Test nodetypes in node name context 423# 424select ExtractValue('<a><b><Text>test</Text></b></a>','/a/b/Text'); 425select ExtractValue('<a><b><comment>test</comment></b></a>','/a/b/comment'); 426select ExtractValue('<a><b><node>test</node></b></a>','/a/b/node'); 427select ExtractValue('<a><b><processing-instruction>test</processing-instruction></b></a>','/a/b/processing-instruction'); 428# 429# Test keywords in node name contexts 430# 431select ExtractValue('<a><and>test</and></a>', '/a/and'); 432select ExtractValue('<a><or>test</or></a>', '/a/or'); 433select ExtractValue('<a><mod>test</mod></a>', '/a/mod'); 434select ExtractValue('<a><div>test</div></a>', '/a/div'); 435select ExtractValue('<a><and:and>test</and:and></a>', '/a/and:and'); 436select ExtractValue('<a><or:or>test</or:or></a>', '/a/or:or'); 437select ExtractValue('<a><mod:mod>test</mod:mod></a>', '/a/mod:mod'); 438select ExtractValue('<a><div:div>test</div:div></a>', '/a/div:div'); 439# 440# Test axis names in node name context 441# 442select ExtractValue('<a><ancestor>test</ancestor></a>', '/a/ancestor'); 443select ExtractValue('<a><ancestor-or-self>test</ancestor-or-self></a>', '/a/ancestor-or-self'); 444select ExtractValue('<a><attribute>test</attribute></a>', '/a/attribute'); 445select ExtractValue('<a><child>test</child></a>', '/a/child'); 446select ExtractValue('<a><descendant>test</descendant></a>', '/a/descendant'); 447select ExtractValue('<a><descendant-or-self>test</descendant-or-self></a>', '/a/descendant-or-self'); 448select ExtractValue('<a><following>test</following></a>', '/a/following'); 449select ExtractValue('<a><following-sibling>test</following-sibling></a>', '/a/following-sibling'); 450select ExtractValue('<a><namespace>test</namespace></a>', '/a/namespace'); 451select ExtractValue('<a><parent>test</parent></a>', '/a/parent'); 452select ExtractValue('<a><preceding>test</preceding></a>', '/a/preceding'); 453select ExtractValue('<a><preceding-sibling>test</preceding-sibling></a>', '/a/preceding-sibling'); 454select ExtractValue('<a><self>test</self></a>', '/a/self'); 455 456# 457# Bug#26518 XPath and variables problem 458# Check with user defined variables 459# 460SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 461set @i=1; 462select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 463set @i=2; 464select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 465set @i=NULL; 466select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 467 468# 469# Check variables in a stored procedure - both local and user variables 470# Make sure that SP and local variables with the same name work together. 471# 472DELIMITER |; 473CREATE PROCEDURE spxml(xml VARCHAR(128)) 474BEGIN 475 DECLARE c INT; 476 DECLARE i INT DEFAULT 1; 477 SET c= ExtractValue(xml,'count(/a/b)'); 478 SET @i= c; 479 WHILE i <= c DO 480 BEGIN 481 SELECT i, @i, ExtractValue(xml,'/a/b[$i]'), ExtractValue(xml,'/a/b[$@i]'); 482 SET i= i + 1; 483 SET @i= @i - 1; 484 END; 485 END WHILE; 486END| 487DELIMITER ;| 488 489call spxml('<a><b>b1</b><b>b2</b><b>b3</b></a>'); 490drop procedure spxml; 491 492# 493# Additional tests for bug#26518 494--echo Multiple matches, but no index specification 495SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b'); 496--echo No matches 497SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/c'); 498--echo Index out of range 499SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[-1]'); 500SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[10]'); 501--echo With string-to-number conversion 502SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1"]'); 503SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1 and string"]'); 504SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["string and 1"]'); 505SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["string"]'); 506--echo String-to-number conversion from a user variable 507SET @i='1'; 508SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 509SET @i='1 and string'; 510SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 511SET @i='string and 1'; 512SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 513SET @i='string'; 514SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]'); 515 516--echo String-to-number conversion with a CHAR SP variable 517DELIMITER |; 518CREATE PROCEDURE spxml(xml VARCHAR(128), i CHAR(16)) 519BEGIN 520 SELECT ExtractValue(xml,'/a/b[$i]'); 521END| 522DELIMITER ;| 523CALL spxml('<a><b>b1</b><b>b2</b></a>', '1'); 524CALL spxml('<a><b>b1</b><b>b2</b></a>', '1 and string'); 525CALL spxml('<a><b>b1</b><b>b2</b></a>', 'string and 1'); 526CALL spxml('<a><b>b1</b><b>b2</b></a>', 'string'); 527DROP PROCEDURE spxml; 528SET sql_mode = default; 529# 530# Bug#28558 UpdateXML called with garbage crashes server 531# 532--error 1105 533select UpdateXML('<a>a</a>',repeat('a b ',1000),''); 534--error 1105 535select ExtractValue('<a>a</a>', '/a[@x=@y0123456789_0123456789_0123456789_0123456789]'); 536--error 1105 537select ExtractValue('<a>a</a>', '/a[@x=$y0123456789_0123456789_0123456789_0123456789]'); 538 539# 540# Bug #31438: updatexml still crashes 541# 542 543select updatexml(NULL, 1, 1), updatexml(1, NULL, 1), updatexml(1, 1, NULL); 544select updatexml(NULL, NULL, 1), updatexml(1, NULL, NULL), 545 updatexml(NULL, 1, NULL); 546select updatexml(NULL, NULL, NULL); 547 548# 549# Bug #32557: order by updatexml causes assertion in filesort 550# 551CREATE TABLE t1(a INT NOT NULL); 552INSERT INTO t1 VALUES (0), (0); 553SELECT 1 FROM t1 ORDER BY(UPDATEXML(a, '1', '1')); 554DROP TABLE t1; 555 556# 557# BUG#38227 EXTRACTVALUE doesn't work with DTD declarations 558# 559# Check that quoted strings work fine in DOCTYPE declaration. 560# 561 562SET @xml= 563'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 564<html> 565 <head> 566 <title> Title - document with document declaration</title> 567 </head> 568 <body> Hi, Im a webpage with document a declaration </body> 569</html>'; 570 571SELECT ExtractValue(@xml, 'html/head/title'); 572SELECT ExtractValue(@xml, 'html/body'); 573 574# These two documents will fail. 575# Quoted strings are not allowed in regular tags 576# 577SELECT ExtractValue('<xml "xxx" "yyy">CharData</xml>', '/xml'); 578SELECT ExtractValue('<xml xxx "yyy">CharData</xml>', '/xml'); 579 580# 581# Bug#42495 updatexml: Assertion failed: xpath->context, file .\item_xmlfunc.cc, line 2507 582# 583set @x=10; 584--error ER_UNKNOWN_ERROR 585select extractvalue('<a></a>','$@x/a'); 586--error ER_UNKNOWN_ERROR 587select extractvalue('<a></a>','round(123.4)/a'); 588--error ER_UNKNOWN_ERROR 589select extractvalue('<a></a>','1/a'); 590--error ER_UNKNOWN_ERROR 591select extractvalue('<a></a>','"b"/a'); 592--error ER_UNKNOWN_ERROR 593select extractvalue('<a></a>','(1)/a'); 594 595# 596# Bug#43183 ExctractValue() brings result list in missorder 597# 598CREATE TABLE IF NOT EXISTS t1 ( 599 id int(10) unsigned NOT NULL AUTO_INCREMENT, 600 xml text, 601 PRIMARY KEY (id) 602); 603 604INSERT INTO t1 (id, xml) VALUES 605(15, '<?xml version="1.0"?><bla name="blubb"></bla>'), 606(14, '<xml version="kaputt">'); 607 608 609SELECT 610extractvalue( xml, '/bla/@name' ), 611extractvalue( xml, '/bla/@name' ) 612FROM t1 ORDER BY t1.id; 613 614 615SELECT 616UpdateXML(xml, '/bla/@name', 'test'), 617UpdateXML(xml, '/bla/@name', 'test') 618FROM t1 ORDER BY t1.id; 619 620DROP TABLE t1; 621 622 623--echo # 624--echo # Bug#57257 Replace(ExtractValue(...)) causes MySQL crash 625--echo # 626SET NAMES utf8; 627SELECT REPLACE(EXTRACTVALUE('1', '/a'),'ds',''); 628 629 630--echo # 631--echo # Bug #57820 extractvalue crashes 632--echo # 633SELECT AVG(DISTINCT EXTRACTVALUE((''),('$@k'))); 634 635 636--echo # 637--echo # Bug#57279 updatexml dies with: Assertion failed: str_arg[length] == 0 638--echo # 639 640--error ER_ILLEGAL_VALUE_FOR_TYPE 641SELECT UPDATEXML(NULL, (LPAD(0.1111E-15, '2011', 1)), 1); 642--error ER_ILLEGAL_VALUE_FOR_TYPE 643SELECT EXTRACTVALUE('', LPAD(0.1111E-15, '2011', 1)); 644 645--echo # 646--echo # Bug #44332 my_xml_scan reads behind the end of buffer 647--echo # 648SELECT UPDATEXML(CONVERT(_latin1'<' USING utf8),'1','1'); 649SELECT UPDATEXML(CONVERT(_latin1'<!--' USING utf8),'1','1'); 650 651--echo # 652--echo # Bug#11766725 (bug#59901): EXTRACTVALUE STILL BROKEN AFTER FIX FOR BUG #44332 653--echo # 654SELECT ExtractValue(CONVERT('<\"', BINARY(10)), 1); 655 656--echo End of 5.1 tests 657 658 659--echo # 660--echo # Start of 5.5 tests 661--echo # 662 663--echo # 664--echo # Bug#58175 xml functions read initialized bytes when conversions happen 665--echo # 666SET NAMES latin1; 667SELECT UPDATEXML(CONVERT('' USING swe7), TRUNCATE('',1), 0); 668 669--echo # 670--echo # Bug#12375190: UPDATEXML CRASHES ON SIMPLE INPUTS 671--echo # 672SELECT UPDATEXML('','(a)/a',''); 673SELECT UPDATEXML('<a><a>x</a></a>','(a)/a','<b />'); 674SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)/a','<b />'); 675SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','<b />'); 676SELECT ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b'); 677 678--echo # 679--echo # End of 5.5 tests 680--echo # 681 682--echo # 683--echo # Bug#13358486 WEIGHT_STRING = MY_STRNXFRM_UNICODE: 684--echo # ASSERTION `SRC' FAILED 685--echo # 686set names utf8; 687do weight_string(extractvalue('','/*/a') level 1 reverse); 688do char((weight_string(extractvalue((''),('tX')) level 7 desc)) using cp852); 689set names default; 690 691--echo # 692--echo # Bug#22552615 EXTRACTVALUE RETURNS NULL WHEN NO MATCHING 693--echo # TEXT NODE IS FOUND FOR THE EXPRESSION 694--echo # 695 696set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>'; 697set @y = 'Default Value'; 698select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y; 699select @y; 700