SET @xml='a1b1c1b2a2'; SELECT extractValue(@xml,'/a'); extractValue(@xml,'/a') a1 a2 SELECT extractValue(@xml,'/a/b'); extractValue(@xml,'/a/b') b1 b2 SELECT extractValue(@xml,'/a/b/c'); extractValue(@xml,'/a/b/c') c1 SELECT extractValue(@xml,'/a/@aa1'); extractValue(@xml,'/a/@aa1') aa1 SELECT extractValue(@xml,'/a/@aa2'); extractValue(@xml,'/a/@aa2') aa2 SELECT extractValue(@xml,'/a/@*'); extractValue(@xml,'/a/@*') aa1 aa2 SELECT extractValue(@xml,'//@ba1'); extractValue(@xml,'//@ba1') ba1 SELECT extractValue(@xml,'//a'); extractValue(@xml,'//a') a1 a2 SELECT extractValue(@xml,'//b'); extractValue(@xml,'//b') b1 b2 SELECT extractValue(@xml,'//c'); extractValue(@xml,'//c') c1 SELECT extractValue(@xml,'/a//b'); extractValue(@xml,'/a//b') b1 b2 SELECT extractValue(@xml,'/a//c'); extractValue(@xml,'/a//c') c1 SELECT extractValue(@xml,'//*'); extractValue(@xml,'//*') a1 b1 c1 b2 a2 SELECT extractValue(@xml,'/a//*'); extractValue(@xml,'/a//*') b1 c1 b2 SELECT extractValue(@xml,'/./a'); extractValue(@xml,'/./a') a1 a2 SELECT extractValue(@xml,'/a/b/.'); extractValue(@xml,'/a/b/.') b1 b2 SELECT extractValue(@xml,'/a/b/..'); extractValue(@xml,'/a/b/..') a1 a2 SELECT extractValue(@xml,'/a/b/../@aa1'); extractValue(@xml,'/a/b/../@aa1') aa1 SELECT extractValue(@xml,'/*'); extractValue(@xml,'/*') a1 a2 SELECT extractValue(@xml,'/*/*'); extractValue(@xml,'/*/*') b1 b2 SELECT extractValue(@xml,'/*/*/*'); extractValue(@xml,'/*/*/*') c1 SELECT extractValue(@xml,'/a/child::*'); extractValue(@xml,'/a/child::*') b1 b2 SELECT extractValue(@xml,'/a/self::*'); extractValue(@xml,'/a/self::*') a1 a2 SELECT extractValue(@xml,'/a/descendant::*'); extractValue(@xml,'/a/descendant::*') b1 c1 b2 SELECT extractValue(@xml,'/a/descendant-or-self::*'); extractValue(@xml,'/a/descendant-or-self::*') a1 b1 c1 b2 a2 SELECT extractValue(@xml,'/a/attribute::*'); extractValue(@xml,'/a/attribute::*') aa1 aa2 SELECT extractValue(@xml,'/a/b/c/parent::*'); extractValue(@xml,'/a/b/c/parent::*') b1 b2 SELECT extractValue(@xml,'/a/b/c/ancestor::*'); extractValue(@xml,'/a/b/c/ancestor::*') a1 b1 b2 a2 SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*'); extractValue(@xml,'/a/b/c/ancestor-or-self::*') a1 b1 c1 b2 a2 SELECT extractValue(@xml,'/descendant-or-self::*'); extractValue(@xml,'/descendant-or-self::*') a1 b1 c1 b2 a2 SET @xml='a11b11b21c1b22a12'; SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*'); extractValue(@xml,'/a/b/c/ancestor-or-self::*') a11 b21 c1 b22 a12 SELECT extractValue(@xml,'//@ba'); extractValue(@xml,'//@ba') ba11 ba12 ba21 ba22 SET @xml='bc'; SELECT extractValue(@xml,'/a/b'); extractValue(@xml,'/a/b') b SELECT extractValue(@xml,'/a/c'); extractValue(@xml,'/a/c') c SELECT extractValue(@xml,'/a/child::b'); extractValue(@xml,'/a/child::b') b SELECT extractValue(@xml,'/a/child::c'); extractValue(@xml,'/a/child::c') c SET @xml='b1c1b2c2'; SELECT extractValue(@xml,'/a/b[1]'); extractValue(@xml,'/a/b[1]') b1 SELECT extractValue(@xml,'/a/b[2]'); extractValue(@xml,'/a/b[2]') b2 SELECT extractValue(@xml,'/a/c[1]'); extractValue(@xml,'/a/c[1]') c1 SELECT extractValue(@xml,'/a/c[2]'); extractValue(@xml,'/a/c[2]') c2 SET @xml=''; SELECT extractValue(@xml,'/a//@x'); extractValue(@xml,'/a//@x') xb1 xb2 xc1 xc2 SELECT extractValue(@xml,'/a//@x[1]'); extractValue(@xml,'/a//@x[1]') xb1 xc1 SELECT extractValue(@xml,'/a//@x[2]'); extractValue(@xml,'/a//@x[2]') xb2 xc2 SET @xml='b1b2c1b1c1b2c2b1'; SELECT extractValue(@xml,'//b[1]'); extractValue(@xml,'//b[1]') b1 c1b1 c2b1 SELECT extractValue(@xml,'/descendant::b[1]'); extractValue(@xml,'/descendant::b[1]') b1 SET @xml='b1b2'; SELECT extractValue(@xml,'/a/b[1+0]'); extractValue(@xml,'/a/b[1+0]') b1 SELECT extractValue(@xml,'/a/b[1*1]'); extractValue(@xml,'/a/b[1*1]') b1 SELECT extractValue(@xml,'/a/b[--1]'); extractValue(@xml,'/a/b[--1]') b1 SELECT extractValue(@xml,'/a/b[2*1-1]'); extractValue(@xml,'/a/b[2*1-1]') b1 SELECT extractValue(@xml,'/a/b[1+1]'); extractValue(@xml,'/a/b[1+1]') b2 SELECT extractValue(@xml,'/a/b[1*2]'); extractValue(@xml,'/a/b[1*2]') b2 SELECT extractValue(@xml,'/a/b[--2]'); extractValue(@xml,'/a/b[--2]') b2 SELECT extractValue(@xml,'/a/b[1*(3-1)]'); extractValue(@xml,'/a/b[1*(3-1)]') b2 SELECT extractValue(@xml,'//*[1=1]'); extractValue(@xml,'//*[1=1]') b1 b2 SELECT extractValue(@xml,'//*[1!=1]'); extractValue(@xml,'//*[1!=1]') SELECT extractValue(@xml,'//*[1>1]'); extractValue(@xml,'//*[1>1]') SELECT extractValue(@xml,'//*[2>1]'); extractValue(@xml,'//*[2>1]') b1 b2 SELECT extractValue(@xml,'//*[1>2]'); extractValue(@xml,'//*[1>2]') SELECT extractValue(@xml,'//*[1>=1]'); extractValue(@xml,'//*[1>=1]') b1 b2 SELECT extractValue(@xml,'//*[2>=1]'); extractValue(@xml,'//*[2>=1]') b1 b2 SELECT extractValue(@xml,'//*[1>=2]'); extractValue(@xml,'//*[1>=2]') SELECT extractValue(@xml,'//*[1<1]'); extractValue(@xml,'//*[1<1]') SELECT extractValue(@xml,'//*[2<1]'); extractValue(@xml,'//*[2<1]') SELECT extractValue(@xml,'//*[1<2]'); extractValue(@xml,'//*[1<2]') b1 b2 SELECT extractValue(@xml,'//*[1<=1]'); extractValue(@xml,'//*[1<=1]') b1 b2 SELECT extractValue(@xml,'//*[2<=1]'); extractValue(@xml,'//*[2<=1]') SELECT extractValue(@xml,'//*[1<=2]'); extractValue(@xml,'//*[1<=2]') b1 b2 SET @xml='b11c11b21c21'; SELECT extractValue(@xml,'/a/b[c="c11"]'); extractValue(@xml,'/a/b[c="c11"]') b11 SELECT extractValue(@xml,'/a/b[c="c21"]'); extractValue(@xml,'/a/b[c="c21"]') b21 SET @xml='b11b21'; SELECT extractValue(@xml,'/a/b[@c="c11"]'); extractValue(@xml,'/a/b[@c="c11"]') b11 SELECT extractValue(@xml,'/a/b[@c="c21"]'); extractValue(@xml,'/a/b[@c="c21"]') b21 SET @xml='a1b11d11b21d21'; SELECT extractValue(@xml, '/a/b[@c="c11"]/d'); extractValue(@xml, '/a/b[@c="c11"]/d') d11 SELECT extractValue(@xml, '/a/b[@c="c21"]/d'); extractValue(@xml, '/a/b[@c="c21"]/d') d21 SELECT extractValue(@xml, '/a/b[d="d11"]/@c'); extractValue(@xml, '/a/b[d="d11"]/@c') c11 SELECT extractValue(@xml, '/a/b[d="d21"]/@c'); extractValue(@xml, '/a/b[d="d21"]/@c') c21 SELECT extractValue(@xml, '/a[b="b11"]'); extractValue(@xml, '/a[b="b11"]') a1 SELECT extractValue(@xml, '/a[b/@c="c11"]'); extractValue(@xml, '/a[b/@c="c11"]') a1 SELECT extractValue(@xml, '/a[b/d="d11"]'); extractValue(@xml, '/a[b/d="d11"]') a1 SELECT extractValue(@xml, '/a[/a/b="b11"]'); extractValue(@xml, '/a[/a/b="b11"]') a1 SELECT extractValue(@xml, '/a[/a/b/@c="c11"]'); extractValue(@xml, '/a[/a/b/@c="c11"]') a1 SELECT extractValue(@xml, '/a[/a/b/d="d11"]'); extractValue(@xml, '/a[/a/b/d="d11"]') a1 SELECT extractValue('a', '/a[false()]'); extractValue('a', '/a[false()]') SELECT extractValue('a', '/a[true()]'); extractValue('a', '/a[true()]') a SELECT extractValue('a', '/a[not(false())]'); extractValue('a', '/a[not(false())]') a SELECT extractValue('a', '/a[not(true())]'); extractValue('a', '/a[not(true())]') SELECT extractValue('a', '/a[true() and true()]'); extractValue('a', '/a[true() and true()]') a SELECT extractValue('a', '/a[true() and false()]'); extractValue('a', '/a[true() and false()]') SELECT extractValue('a', '/a[false()and false()]'); extractValue('a', '/a[false()and false()]') SELECT extractValue('a', '/a[false()and true()]'); extractValue('a', '/a[false()and true()]') SELECT extractValue('a', '/a[true() or true()]'); extractValue('a', '/a[true() or true()]') a SELECT extractValue('a', '/a[true() or false()]'); extractValue('a', '/a[true() or false()]') a SELECT extractValue('a', '/a[false()or false()]'); extractValue('a', '/a[false()or false()]') SELECT extractValue('a', '/a[false()or true()]'); extractValue('a', '/a[false()or true()]') a SET @xml='abb1b2b3'; select extractValue(@xml,'/a/b[@c="c"]'); extractValue(@xml,'/a/b[@c="c"]') b1 select extractValue(@xml,'/a/b[@c="d"]'); extractValue(@xml,'/a/b[@c="d"]') b2 select extractValue(@xml,'/a/b[@c="e"]'); extractValue(@xml,'/a/b[@c="e"]') b1 b3 select extractValue(@xml,'/a/b[not(@c="e")]'); extractValue(@xml,'/a/b[not(@c="e")]') b2 select extractValue(@xml,'/a/b[@c!="e"]'); extractValue(@xml,'/a/b[@c!="e"]') b1 b2 b3 select extractValue(@xml,'/a/b[@c="c" or @c="d"]'); extractValue(@xml,'/a/b[@c="c" or @c="d"]') b1 b2 select extractValue(@xml,'/a/b[@c="c" and @c="e"]'); extractValue(@xml,'/a/b[@c="c" and @c="e"]') b1 SET @xml='b1b2'; select extractValue(@xml,'/a/b[@c]'); extractValue(@xml,'/a/b[@c]') b1 select extractValue(@xml,'/a/b[@d]'); extractValue(@xml,'/a/b[@d]') b1 b2 select extractValue(@xml,'/a/b[@e]'); extractValue(@xml,'/a/b[@e]') b2 select extractValue(@xml,'/a/b[not(@c)]'); extractValue(@xml,'/a/b[not(@c)]') b2 select extractValue(@xml,'/a/b[not(@d)]'); extractValue(@xml,'/a/b[not(@d)]') select extractValue(@xml,'/a/b[not(@e)]'); extractValue(@xml,'/a/b[not(@e)]') b1 select extractValue(@xml, '/a/b[boolean(@c) or boolean(@d)]'); extractValue(@xml, '/a/b[boolean(@c) or boolean(@d)]') b1 b2 select extractValue(@xml, '/a/b[boolean(@c) or boolean(@e)]'); extractValue(@xml, '/a/b[boolean(@c) or boolean(@e)]') b1 b2 select extractValue(@xml, '/a/b[boolean(@d) or boolean(@e)]'); extractValue(@xml, '/a/b[boolean(@d) or boolean(@e)]') b1 b2 select extractValue(@xml, '/a/b[boolean(@c) and boolean(@d)]'); extractValue(@xml, '/a/b[boolean(@c) and boolean(@d)]') b1 select extractValue(@xml, '/a/b[boolean(@c) and boolean(@e)]'); extractValue(@xml, '/a/b[boolean(@c) and boolean(@e)]') select extractValue(@xml, '/a/b[boolean(@d) and boolean(@e)]'); extractValue(@xml, '/a/b[boolean(@d) and boolean(@e)]') b2 select extractValue(@xml, '/a/b[@c or @d]'); extractValue(@xml, '/a/b[@c or @d]') b1 b2 select extractValue(@xml, '/a/b[@c or @e]'); extractValue(@xml, '/a/b[@c or @e]') b1 b2 select extractValue(@xml, '/a/b[@d or @e]'); extractValue(@xml, '/a/b[@d or @e]') b1 b2 select extractValue(@xml, '/a/b[@c and @d]'); extractValue(@xml, '/a/b[@c and @d]') b1 select extractValue(@xml, '/a/b[@c and @e]'); extractValue(@xml, '/a/b[@c and @e]') select extractValue(@xml, '/a/b[@d and @e]'); extractValue(@xml, '/a/b[@d and @e]') b2 SET @xml='b1b2'; SELECT extractValue(@xml,'/a/b[@*]'); extractValue(@xml,'/a/b[@*]') b1 SELECT extractValue(@xml,'/a/b[not(@*)]'); extractValue(@xml,'/a/b[not(@*)]') b2 SELECT extractValue('a', '/a[ceiling(3.1)=4]'); extractValue('a', '/a[ceiling(3.1)=4]') a SELECT extractValue('a', '/a[floor(3.1)=3]'); extractValue('a', '/a[floor(3.1)=3]') a SELECT extractValue('a', '/a[round(3.1)=3]'); extractValue('a', '/a[round(3.1)=3]') a SELECT extractValue('a', '/a[round(3.8)=4]'); extractValue('a', '/a[round(3.8)=4]') a SELECT extractValue('bc', '/a/b | /a/c'); extractValue('bc', '/a/b | /a/c') b c select extractValue('','/a/@b[position()=1]'); extractValue('','/a/@b[position()=1]') b1 select extractValue('','/a/@b[position()=2]'); extractValue('','/a/@b[position()=2]') b2 select extractValue('','/a/@b[position()=3]'); extractValue('','/a/@b[position()=3]') b3 select extractValue('','/a/@b[1=position()]'); extractValue('','/a/@b[1=position()]') b1 select extractValue('','/a/@b[2=position()]'); extractValue('','/a/@b[2=position()]') b2 select extractValue('','/a/@b[3=position()]'); extractValue('','/a/@b[3=position()]') b3 select extractValue('','/a/@b[2>=position()]'); extractValue('','/a/@b[2>=position()]') b1 b2 select extractValue('','/a/@b[2<=position()]'); extractValue('','/a/@b[2<=position()]') b2 b3 select extractValue('','/a/@b[position()=3 or position()=2]'); extractValue('','/a/@b[position()=3 or position()=2]') b2 b3 SELECT extractValue('aa1c1a2','/a/b[count(c)=0]'); extractValue('aa1c1a2','/a/b[count(c)=0]') a2 SELECT extractValue('aa1c1a2','/a/b[count(c)=1]'); extractValue('aa1c1a2','/a/b[count(c)=1]') a1 select extractValue('a1b1b24','/a/b[sum(@ba)=3]'); extractValue('a1b1b24','/a/b[sum(@ba)=3]') b1 select extractValue('b1b2','/a/b[1]'); extractValue('b1b2','/a/b[1]') b1 select extractValue('b1b2','/a/b[boolean(1)]'); extractValue('b1b2','/a/b[boolean(1)]') b1 b2 select extractValue('b1b2','/a/b[true()]'); extractValue('b1b2','/a/b[true()]') b1 b2 select extractValue('b1b2','/a/b[number(true())]'); extractValue('b1b2','/a/b[number(true())]') b1 select extractValue('ab','/a[contains("abc","b")]'); extractValue('ab','/a[contains("abc","b")]') ab select extractValue('ab','/a[contains(.,"a")]'); extractValue('ab','/a[contains(.,"a")]') ab select extractValue('ab','/a[contains(.,"b")]'); extractValue('ab','/a[contains(.,"b")]') ab select extractValue('ab','/a[contains(.,"c")]'); extractValue('ab','/a[contains(.,"c")]') select extractValue('ab','/a[concat(@b,"2")="12"]'); extractValue('ab','/a[concat(@b,"2")="12"]') ab SET @xml='ab'; select extractValue(@xml, '/a/@b[substring(.,2)="1"]'); extractValue(@xml, '/a/@b[substring(.,2)="1"]') 11 21 select extractValue(@xml, '/a/@b[substring(.,2)="2"]'); extractValue(@xml, '/a/@b[substring(.,2)="2"]') 12 22 select extractValue(@xml, '/a/@b[substring(.,1,1)="1"]'); extractValue(@xml, '/a/@b[substring(.,1,1)="1"]') 11 12 select extractValue(@xml, '/a/@b[substring(.,1,1)="2"]'); extractValue(@xml, '/a/@b[substring(.,1,1)="2"]') 21 22 select extractValue(@xml, '/a/@b[substring(.,2,1)="1"]'); extractValue(@xml, '/a/@b[substring(.,2,1)="1"]') 11 21 select extractValue(@xml, '/a/@b[substring(.,2,1)="2"]'); extractValue(@xml, '/a/@b[substring(.,2,1)="2"]') 12 22 SET @xml='b1b2'; SELECT extractValue(@xml, '/a/b[string-length("x")=1]'); extractValue(@xml, '/a/b[string-length("x")=1]') b1 b2 SELECT extractValue(@xml, '/a/b[string-length("xx")=2]'); extractValue(@xml, '/a/b[string-length("xx")=2]') b1 b2 SELECT extractValue(@xml, '/a/b[string-length("xxx")=2]'); extractValue(@xml, '/a/b[string-length("xxx")=2]') SELECT extractValue(@xml, '/a/b[string-length("x")]'); extractValue(@xml, '/a/b[string-length("x")]') b1 SELECT extractValue(@xml, '/a/b[string-length("xx")]'); extractValue(@xml, '/a/b[string-length("xx")]') b2 SELECT extractValue(@xml, '/a/b[string-length()]'); extractValue(@xml, '/a/b[string-length()]') b2 SELECT extractValue(@xml, 'string-length()'); ERROR HY000: XPATH syntax error: '' SELECT extractValue(@xml, 'string-length("x")'); extractValue(@xml, 'string-length("x")') 1 SET @xml=''; select extractValue(@xml,'/a/@b'); extractValue(@xml,'/a/@b') b11 b12 b21 22 select extractValue(@xml,'/a/@b[contains(.,"1")]'); extractValue(@xml,'/a/@b[contains(.,"1")]') b11 b12 b21 select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")]'); extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")]') b12 b21 select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")][2]'); extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")][2]') b21 SET @xml='a1b1c1b2a2'; select UpdateXML('a1b1c1b2a2','/a/b/c','+++++++++'); UpdateXML('a1b1c1b2a2','/a/b/c','+++++++++') a1b1+++++++++b2a2 select UpdateXML('a1b1c1b2a2','/a/b/c','+++++++++'); UpdateXML('a1b1c1b2a2','/a/b/c','+++++++++') a1b1+++++++++b2a2 select UpdateXML('a1b1c1b2a2','/a/b/c',''); UpdateXML('a1b1c1b2a2','/a/b/c','') a1b1b2a2 SET @xml='bb'; select UpdateXML(@xml, '/a/b', 'ccc'); UpdateXML(@xml, '/a/b', 'ccc') ccc SET @xml='bb'; select UpdateXML(@xml, '/a/b', 'ccc'); UpdateXML(@xml, '/a/b', 'ccc') ccc select UpdateXML(@xml, '/a/@aa1', ''); UpdateXML(@xml, '/a/@aa1', '') bb select UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"'); UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"') bb select UpdateXML(@xml, '/a/@aa2', ''); UpdateXML(@xml, '/a/@aa2', '') bb select UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"'); UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"') bb select UpdateXML(@xml, '/a/b/@bb1', ''); UpdateXML(@xml, '/a/b/@bb1', '') bb select UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"'); UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"') bb select UpdateXML(@xml, '/a/b/@bb2', ''); UpdateXML(@xml, '/a/b/@bb2', '') bb select UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"'); UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"') bb select updatexml('
12
', '/','12') as upd1; upd1 12 select updatexml('', '/', '') as upd2; upd2 SET @xml= 'lesser wombat'; select extractvalue(@xml,'order/clerk'); extractvalue(@xml,'order/clerk') lesser wombat select extractvalue(@xml,'/order/clerk'); extractvalue(@xml,'/order/clerk') lesser wombat select extractvalue('B','/a|/b'); extractvalue('B','/a|/b') select extractvalue('B','/a|b'); extractvalue('B','/a|b') select extractvalue('aB','/a|/b'); extractvalue('aB','/a|/b') a select extractvalue('aB','/a|b'); extractvalue('aB','/a|b') a select extractvalue('aB','a|/b'); extractvalue('aB','a|/b') a select extractvalue('A','/'); ERROR HY000: XPATH error: comparison of two nodesets is not supported: '' select extractvalue('bb!','//b!'); ERROR HY000: XPATH syntax error: '!' select extractvalue('ABC','/a/descendant::*'); extractvalue('ABC','/a/descendant::*') B C select extractvalue('ABC','/a/self::*'); extractvalue('ABC','/a/self::*') A select extractvalue('ABC','/a/descendant-or-self::*'); extractvalue('ABC','/a/descendant-or-self::*') A B C select extractvalue('A','/A_B'); extractvalue('A','/A_B') A select extractvalue('AB1B2','/a/b[position()]'); extractvalue('AB1B2','/a/b[position()]') B1 B2 select extractvalue('AB1B2','/a/b[count(.)=last()]'); extractvalue('AB1B2','/a/b[count(.)=last()]') B1 B2 select extractvalue('AB1B2','/a/b[last()]'); extractvalue('AB1B2','/a/b[last()]') B2 select extractvalue('AB1B2','/a/b[last()-1]'); extractvalue('AB1B2','/a/b[last()-1]') B1 select extractvalue('AB1B2','/a/b[last()=1]'); extractvalue('AB1B2','/a/b[last()=1]') select extractvalue('AB1B2','/a/b[last()=2]'); extractvalue('AB1B2','/a/b[last()=2]') B1 B2 select extractvalue('AB1B2','/a/b[last()=position()]'); extractvalue('AB1B2','/a/b[last()=position()]') B2 select extractvalue('AB1B2','/a/b[count(.)]'); extractvalue('AB1B2','/a/b[count(.)]') B2 select extractvalue('AB1B2','/a/b[count(.)-1]'); extractvalue('AB1B2','/a/b[count(.)-1]') B1 select extractvalue('AB1B2','/a/b[count(.)=1]'); extractvalue('AB1B2','/a/b[count(.)=1]') select extractvalue('AB1B2','/a/b[count(.)=2]'); extractvalue('AB1B2','/a/b[count(.)=2]') B1 B2 select extractvalue('AB1B2','/a/b[count(.)=position()]'); extractvalue('AB1B2','/a/b[count(.)=position()]') B2 select extractvalue('Jack','/a[contains(../a,"J")]'); extractvalue('Jack','/a[contains(../a,"J")]') Jack select extractvalue('Jack','/a[contains(../a,"j")]'); extractvalue('Jack','/a[contains(../a,"j")]') Jack select extractvalue('Jack','/a[contains(../a,"j")]' collate latin1_bin); extractvalue('Jack','/a[contains(../a,"j")]' collate latin1_bin) select extractvalue('Jack' collate latin1_bin,'/a[contains(../a,"j")]'); extractvalue('Jack' collate latin1_bin,'/a[contains(../a,"j")]') select ExtractValue('','/tag1'); ExtractValue('','/tag1') test select extractValue('a','/a'); extractValue('a','/a') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' select extractValue('a<','/a'); extractValue('a<','/a') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' select extractValue('aaaa' wanted)' select extractValue('a
','/a'); extractValue('a','/a') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 12: '' unexpected (END-OF-INPUT wanted)' select extractValue('a','/a'); extractValue('a','/a') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' select extractValue('1','position()'); ERROR HY000: XPATH syntax error: '' select extractValue('1','last()'); ERROR HY000: XPATH syntax error: '' select extractValue('1','/e/'); ERROR HY000: XPATH syntax error: '' set names utf8; select extractValue('<Ñ>r','/Ñ/r'); extractValue('<Ñ>r','/Ñ/r') r select extractValue('<Ñ>Ñ','/r/Ñ'); extractValue('<Ñ>Ñ','/r/Ñ') Ñ select extractValue('<Ñ r="r"/>','/Ñ/@r'); extractValue('<Ñ r="r"/>','/Ñ/@r') r select extractValue('','/r/@Ñ'); extractValue('','/r/@Ñ') Ñ DROP PROCEDURE IF EXISTS p2; CREATE PROCEDURE p2 () BEGIN DECLARE p LONGTEXT CHARACTER SET UTF8 DEFAULT '<Ñ>A'; SELECT EXTRACTVALUE(p,'/Ñ/r'); END// CALL p2(); EXTRACTVALUE(p,'/Ñ/r') A DROP PROCEDURE p2; select extractValue('','count(ns:element)'); extractValue('','count(ns:element)') 1 select extractValue('a','/ns:element'); extractValue('a','/ns:element') a select extractValue('a','/ns:element/@xmlns:ns'); extractValue('a','/ns:element/@xmlns:ns') myns select extractValue('DataOtherdata','/foo/foo.bar'); extractValue('DataOtherdata','/foo/foo.bar') Data select extractValue('DataOtherdata','/foo/something'); extractValue('DataOtherdata','/foo/something') Otherdata select extractValue('<01>10:39:15<02>140','/zot/tim0/02'); ERROR HY000: XPATH syntax error: '02' select extractValue('<01>10:39:15<02>140','//*'); extractValue('<01>10:39:15<02>140','//*') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' select extractValue('<.>test','//*'); extractValue('<.>test','//*') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<->test','//*'); extractValue('<->test','//*') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<:>test','//*'); extractValue('<:>test','//*') test select extractValue('<_>test','//*'); extractValue('<_>test','//*') test select extractValue('test','//*'); extractValue('test','//*') test set @xml= "pt1010pt5050"; select ExtractValue(@xml, "/entry[(pt=10)]/id"); ExtractValue(@xml, "/entry[(pt=10)]/id") pt10 select ExtractValue(@xml, "/entry[(pt!=10)]/id"); ExtractValue(@xml, "/entry[(pt!=10)]/id") pt50 select ExtractValue(@xml, "/entry[(pt<10)]/id"); ExtractValue(@xml, "/entry[(pt<10)]/id") select ExtractValue(@xml, "/entry[(pt<=10)]/id"); ExtractValue(@xml, "/entry[(pt<=10)]/id") pt10 select ExtractValue(@xml, "/entry[(pt>10)]/id"); ExtractValue(@xml, "/entry[(pt>10)]/id") pt50 select ExtractValue(@xml, "/entry[(pt>=10)]/id"); ExtractValue(@xml, "/entry[(pt>=10)]/id") pt10 pt50 select ExtractValue(@xml, "/entry[(pt=50)]/id"); ExtractValue(@xml, "/entry[(pt=50)]/id") pt50 select ExtractValue(@xml, "/entry[(pt!=50)]/id"); ExtractValue(@xml, "/entry[(pt!=50)]/id") pt10 select ExtractValue(@xml, "/entry[(pt<50)]/id"); ExtractValue(@xml, "/entry[(pt<50)]/id") pt10 select ExtractValue(@xml, "/entry[(pt<=50)]/id"); ExtractValue(@xml, "/entry[(pt<=50)]/id") pt10 pt50 select ExtractValue(@xml, "/entry[(pt>50)]/id"); ExtractValue(@xml, "/entry[(pt>50)]/id") select ExtractValue(@xml, "/entry[(pt>=50)]/id"); ExtractValue(@xml, "/entry[(pt>=50)]/id") pt50 select ExtractValue(@xml, "/entry[(10=pt)]/id"); ExtractValue(@xml, "/entry[(10=pt)]/id") pt10 select ExtractValue(@xml, "/entry[(10!=pt)]/id"); ExtractValue(@xml, "/entry[(10!=pt)]/id") pt50 select ExtractValue(@xml, "/entry[(10>pt)]/id"); ExtractValue(@xml, "/entry[(10>pt)]/id") select ExtractValue(@xml, "/entry[(10>=pt)]/id"); ExtractValue(@xml, "/entry[(10>=pt)]/id") pt10 select ExtractValue(@xml, "/entry[(10pt)]/id"); ExtractValue(@xml, "/entry[(50>pt)]/id") pt10 select ExtractValue(@xml, "/entry[(50>=pt)]/id"); ExtractValue(@xml, "/entry[(50>=pt)]/id") pt10 pt50 select ExtractValue(@xml, "/entry[(50test','/a/b/Text'); ExtractValue('test','/a/b/Text') test select ExtractValue('test','/a/b/comment'); ExtractValue('test','/a/b/comment') test select ExtractValue('test','/a/b/node'); ExtractValue('test','/a/b/node') test select ExtractValue('test','/a/b/processing-instruction'); ExtractValue('test','/a/b/processing-instruction') test select ExtractValue('test', '/a/and'); ExtractValue('test', '/a/and') test select ExtractValue('test', '/a/or'); ExtractValue('test', '/a/or') test select ExtractValue('test', '/a/mod'); ExtractValue('test', '/a/mod') test select ExtractValue('
test
', '/a/div'); ExtractValue('
test
', '/a/div') test select ExtractValue('test', '/a/and:and'); ExtractValue('test', '/a/and:and') test select ExtractValue('test', '/a/or:or'); ExtractValue('test', '/a/or:or') test select ExtractValue('test', '/a/mod:mod'); ExtractValue('test', '/a/mod:mod') test select ExtractValue('test', '/a/div:div'); ExtractValue('test', '/a/div:div') test select ExtractValue('test', '/a/ancestor'); ExtractValue('test', '/a/ancestor') test select ExtractValue('test', '/a/ancestor-or-self'); ExtractValue('test', '/a/ancestor-or-self') test select ExtractValue('test', '/a/attribute'); ExtractValue('test', '/a/attribute') test select ExtractValue('test', '/a/child'); ExtractValue('test', '/a/child') test select ExtractValue('test', '/a/descendant'); ExtractValue('test', '/a/descendant') test select ExtractValue('test', '/a/descendant-or-self'); ExtractValue('test', '/a/descendant-or-self') test select ExtractValue('test', '/a/following'); ExtractValue('test', '/a/following') test select ExtractValue('test', '/a/following-sibling'); ExtractValue('test', '/a/following-sibling') test select ExtractValue('test', '/a/namespace'); ExtractValue('test', '/a/namespace') test select ExtractValue('test', '/a/parent'); ExtractValue('test', '/a/parent') test select ExtractValue('test', '/a/preceding'); ExtractValue('test', '/a/preceding') test select ExtractValue('test', '/a/preceding-sibling'); ExtractValue('test', '/a/preceding-sibling') test select ExtractValue('test', '/a/self'); ExtractValue('test', '/a/self') test set @i=1; select ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') b1 set @i=2; select ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') b2 set @i=NULL; select ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') CREATE PROCEDURE spxml(xml VARCHAR(128)) BEGIN DECLARE c INT; DECLARE i INT DEFAULT 1; SET c= ExtractValue(xml,'count(/a/b)'); SET @i= c; WHILE i <= c DO BEGIN SELECT i, @i, ExtractValue(xml,'/a/b[$i]'), ExtractValue(xml,'/a/b[$@i]'); SET i= i + 1; SET @i= @i - 1; END; END WHILE; END| call spxml('b1b2b3'); i @i ExtractValue(xml,'/a/b[$i]') ExtractValue(xml,'/a/b[$@i]') 1 3 b1 b3 i @i ExtractValue(xml,'/a/b[$i]') ExtractValue(xml,'/a/b[$@i]') 2 2 b2 b2 i @i ExtractValue(xml,'/a/b[$i]') ExtractValue(xml,'/a/b[$@i]') 3 1 b3 b1 drop procedure spxml; Multiple matches, but no index specification SELECT ExtractValue('b1b2','/a/b'); ExtractValue('b1b2','/a/b') b1 b2 No matches SELECT ExtractValue('b1b2','/a/c'); ExtractValue('b1b2','/a/c') Index out of range SELECT ExtractValue('b1b2','/a/b[-1]'); ExtractValue('b1b2','/a/b[-1]') SELECT ExtractValue('b1b2','/a/b[10]'); ExtractValue('b1b2','/a/b[10]') With string-to-number conversion SELECT ExtractValue('b1b2','/a/b["1"]'); ExtractValue('b1b2','/a/b["1"]') b1 SELECT ExtractValue('b1b2','/a/b["1 and string"]'); ExtractValue('b1b2','/a/b["1 and string"]') b1 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1 and string' Warning 1292 Truncated incorrect INTEGER value: '1 and string' SELECT ExtractValue('b1b2','/a/b["string and 1"]'); ExtractValue('b1b2','/a/b["string and 1"]') Warnings: Warning 1292 Truncated incorrect INTEGER value: 'string and 1' Warning 1292 Truncated incorrect INTEGER value: 'string and 1' SELECT ExtractValue('b1b2','/a/b["string"]'); ExtractValue('b1b2','/a/b["string"]') Warnings: Warning 1292 Truncated incorrect INTEGER value: 'string' Warning 1292 Truncated incorrect INTEGER value: 'string' String-to-number conversion from a user variable SET @i='1'; SELECT ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') b1 SET @i='1 and string'; SELECT ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') b1 SET @i='string and 1'; SELECT ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') SET @i='string'; SELECT ExtractValue('b1b2','/a/b[$@i]'); ExtractValue('b1b2','/a/b[$@i]') String-to-number conversion with a CHAR SP variable CREATE PROCEDURE spxml(xml VARCHAR(128), i CHAR(16)) BEGIN SELECT ExtractValue(xml,'/a/b[$i]'); END| CALL spxml('b1b2', '1'); ExtractValue(xml,'/a/b[$i]') b1 CALL spxml('b1b2', '1 and string'); ExtractValue(xml,'/a/b[$i]') b1 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1 and string ' Warning 1292 Truncated incorrect INTEGER value: '1 and string ' CALL spxml('b1b2', 'string and 1'); ExtractValue(xml,'/a/b[$i]') Warnings: Warning 1292 Truncated incorrect INTEGER value: 'string and 1 ' Warning 1292 Truncated incorrect INTEGER value: 'string and 1 ' CALL spxml('b1b2', 'string'); ExtractValue(xml,'/a/b[$i]') Warnings: Warning 1292 Truncated incorrect INTEGER value: 'string ' Warning 1292 Truncated incorrect INTEGER value: 'string ' DROP PROCEDURE spxml; select UpdateXML('a',repeat('a b ',1000),''); ERROR HY000: XPATH syntax error: 'b a b a b a b a b a b a b a b a ' select ExtractValue('a', '/a[@x=@y0123456789_0123456789_0123456789_0123456789]'); ERROR HY000: XPATH error: comparison of two nodesets is not supported: '=@y0123456789_0123456789_0123456' select ExtractValue('a', '/a[@x=$y0123456789_0123456789_0123456789_0123456789]'); ERROR HY000: Unknown XPATH variable at: '$y0123456789_0123456789_01234567' select updatexml(NULL, 1, 1), updatexml(1, NULL, 1), updatexml(1, 1, NULL); updatexml(NULL, 1, 1) updatexml(1, NULL, 1) updatexml(1, 1, NULL) NULL NULL NULL select updatexml(NULL, NULL, 1), updatexml(1, NULL, NULL), updatexml(NULL, 1, NULL); updatexml(NULL, NULL, 1) updatexml(1, NULL, NULL) updatexml(NULL, 1, NULL) NULL NULL NULL select updatexml(NULL, NULL, NULL); updatexml(NULL, NULL, NULL) NULL CREATE TABLE t1(a INT NOT NULL); INSERT INTO t1 VALUES (0), (0); SELECT 1 FROM t1 ORDER BY(UPDATEXML(a, '1', '1')); 1 1 1 DROP TABLE t1; SET @xml= ' Title - document with document declaration Hi, Im a webpage with document a declaration '; SELECT ExtractValue(@xml, 'html/head/title'); ExtractValue(@xml, 'html/head/title') Title - document with document declaration SELECT ExtractValue(@xml, 'html/body'); ExtractValue(@xml, 'html/body') Hi, Im a webpage with document a declaration SELECT ExtractValue('CharData', '/xml'); ExtractValue('CharData', '/xml') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 11: STRING unexpected ('>' wanted)' SELECT ExtractValue('CharData', '/xml'); ExtractValue('CharData', '/xml') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 17: STRING unexpected ('>' wanted)' set @x=10; select extractvalue('','$@x/a'); ERROR HY000: XPATH syntax error: '/a' select extractvalue('','round(123.4)/a'); ERROR HY000: XPATH syntax error: '/a' select extractvalue('','1/a'); ERROR HY000: XPATH syntax error: '/a' select extractvalue('','"b"/a'); ERROR HY000: XPATH syntax error: '/a' select extractvalue('','(1)/a'); ERROR HY000: XPATH syntax error: '/a' CREATE TABLE IF NOT EXISTS t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, xml text, PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO t1 (id, xml) VALUES (15, ''), (14, ''); SELECT extractvalue( xml, '/bla/@name' ), extractvalue( xml, '/bla/@name' ) FROM t1 ORDER BY t1.id; extractvalue( xml, '/bla/@name' ) extractvalue( xml, '/bla/@name' ) NULL NULL blubb blubb Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' SELECT UpdateXML(xml, '/bla/@name', 'test'), UpdateXML(xml, '/bla/@name', 'test') FROM t1 ORDER BY t1.id; UpdateXML(xml, '/bla/@name', 'test') UpdateXML(xml, '/bla/@name', 'test') NULL NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' Warning 1525 Incorrect XML value: 'parse error at line 1 pos 23: unexpected END-OF-INPUT' DROP TABLE t1; # # Bug#57257 Replace(ExtractValue(...)) causes MySQL crash # SET NAMES utf8; SELECT REPLACE(EXTRACTVALUE('1', '/a'),'ds',''); REPLACE(EXTRACTVALUE('1', '/a'),'ds','') # # Bug #57820 extractvalue crashes # SELECT AVG(DISTINCT EXTRACTVALUE((''),('$@k'))); AVG(DISTINCT EXTRACTVALUE((''),('$@k'))) NULL # # Bug#57279 updatexml dies with: Assertion failed: str_arg[length] == 0 # SELECT UPDATEXML(NULL, (LPAD(0.1111E-15, '2011', 1)), 1); ERROR 22007: Illegal double '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111' value found during parsing SELECT EXTRACTVALUE('', LPAD(0.1111E-15, '2011', 1)); ERROR 22007: Illegal double '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111' value found during parsing # # Bug #44332 my_xml_scan reads behind the end of buffer # SELECT UPDATEXML(CONVERT(_latin1'<' USING utf8),'1','1'); UPDATEXML(CONVERT(_latin1'<' USING utf8),'1','1') NULL Warnings: Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: END-OF-INPUT unexpected (ident or '/' wanted)' SELECT UPDATEXML(CONVERT(_latin1'