~drizzle-trunk/drizzle/development

SET @xml='<a aa1="aa1" aa2="aa2">a1<b ba1="ba1">b1<c>c1</c>b2</b>a2</a>';

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='<a>a11<b ba="ba11" ba="ba12">b11</b><b ba="ba21" ba="ba22">b21<c>c1</c>b22</b>a12</a>';

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='<a><b>b</b><c>c</c></a>';

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='<a><b>b1</b><c>c1</c><b>b2</b><c>c2</c></a>';

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='<a><b x="xb1" x="xb2"/><c x="xc1" x="xc2"/></a>';

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='<a><b>b1</b><b>b2</b><c><b>c1b1</b><b>c1b2</b></c><c><b>c2b1</c></b></a>';

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='<a><b>b1</b><b>b2</b></a>';

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='<a><b>b11<c>c11</c></b><b>b21<c>c21</c></b></a>';

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='<a><b c="c11">b11</b><b c="c21">b21</b></a>';

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='<a>a1<b c="c11">b11<d>d11</d></b><b c="c21">b21<d>d21</d></b></a>';

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</a>', '/a[false()]');

extractValue('<a>a</a>', '/a[false()]')

SELECT extractValue('<a>a</a>', '/a[true()]');

extractValue('<a>a</a>', '/a[true()]')

a

SELECT extractValue('<a>a</a>', '/a[not(false())]');

extractValue('<a>a</a>', '/a[not(false())]')

a

SELECT extractValue('<a>a</a>', '/a[not(true())]');

extractValue('<a>a</a>', '/a[not(true())]')

SELECT extractValue('<a>a</a>', '/a[true() and true()]');

extractValue('<a>a</a>', '/a[true() and true()]')

a

SELECT extractValue('<a>a</a>', '/a[true() and false()]');

extractValue('<a>a</a>', '/a[true() and false()]')

SELECT extractValue('<a>a</a>', '/a[false()and false()]');

extractValue('<a>a</a>', '/a[false()and false()]')

SELECT extractValue('<a>a</a>', '/a[false()and true()]');

extractValue('<a>a</a>', '/a[false()and true()]')

SELECT extractValue('<a>a</a>', '/a[true() or true()]');

extractValue('<a>a</a>', '/a[true() or true()]')

a

SELECT extractValue('<a>a</a>', '/a[true() or false()]');

extractValue('<a>a</a>', '/a[true() or false()]')

a

SELECT extractValue('<a>a</a>', '/a[false()or false()]');

extractValue('<a>a</a>', '/a[false()or false()]')

SELECT extractValue('<a>a</a>', '/a[false()or true()]');

extractValue('<a>a</a>', '/a[false()or true()]')

a

SET @xml='<a>ab<b c="c" c="e">b1</b><b c="d">b2</b><b c="f" c="e">b3</b></a>';

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='<a><b c="c" d="d">b1</b><b d="d" e="e">b2</b></a>';

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='<a><b c="c">b1</b><b>b2</b></a>';

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</a>', '/a[ceiling(3.1)=4]');

extractValue('<a>a</a>', '/a[ceiling(3.1)=4]')

a

SELECT extractValue('<a>a</a>', '/a[floor(3.1)=3]');

extractValue('<a>a</a>', '/a[floor(3.1)=3]')

a

SELECT extractValue('<a>a</a>', '/a[round(3.1)=3]');

extractValue('<a>a</a>', '/a[round(3.1)=3]')

a

SELECT extractValue('<a>a</a>', '/a[round(3.8)=4]');

extractValue('<a>a</a>', '/a[round(3.8)=4]')

a

SELECT extractValue('<a><b>b</b><c>c</c></a>', '/a/b | /a/c');

extractValue('<a><b>b</b><c>c</c></a>', '/a/b | /a/c')

b c

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=1]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=1]')

b1

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=2]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=2]')

b2

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3]')

b3

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[1=position()]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[1=position()]')

b1

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2=position()]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2=position()]')

b2

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[3=position()]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[3=position()]')

b3

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2>=position()]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2>=position()]')

b1 b2

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2<=position()]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[2<=position()]')

b2 b3

select extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3 or position()=2]');

extractValue('<a b="b1" b="b2" b="b3"/>','/a/@b[position()=3 or position()=2]')

b2 b3

SELECT extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=0]');

extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=0]')

a2

SELECT extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=1]');

extractValue('<a>a<b>a1<c>c1</c></b><b>a2</b></a>','/a/b[count(c)=1]')

a1

select extractValue('<a>a1<b ba="1" ba="2">b1</b><b>b2</b>4</a>','/a/b[sum(@ba)=3]');

extractValue('<a>a1<b ba="1" ba="2">b1</b><b>b2</b>4</a>','/a/b[sum(@ba)=3]')

b1

select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[1]');

extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[1]')

b1

select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[boolean(1)]');

extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[boolean(1)]')

b1 b2

select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[true()]');

extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[true()]')

b1 b2

select extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[number(true())]');

extractValue('<a><b>b1</b><b>b2</b></a>','/a/b[number(true())]')

b1

select extractValue('<a>ab</a>','/a[contains("abc","b")]');

extractValue('<a>ab</a>','/a[contains("abc","b")]')

ab

select extractValue('<a>ab</a>','/a[contains(.,"a")]');

extractValue('<a>ab</a>','/a[contains(.,"a")]')

ab

select extractValue('<a>ab</a>','/a[contains(.,"b")]');

extractValue('<a>ab</a>','/a[contains(.,"b")]')

ab

select extractValue('<a>ab</a>','/a[contains(.,"c")]');

extractValue('<a>ab</a>','/a[contains(.,"c")]')

select extractValue('<a b="1">ab</a>','/a[concat(@b,"2")="12"]');

extractValue('<a b="1">ab</a>','/a[concat(@b,"2")="12"]')

ab

SET @xml='<a b="11" b="12" b="21" b="22">ab</a>';

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='<a><b>b1</b><b>b2</b></a>';

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='<a b="b11" b="b12" b="b21" b="22"/>';

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='<a>a1<b>b1<c>c1</c>b2</b>a2</a>';

select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','+++++++++');

UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','+++++++++')

<a>a1<b>b1+++++++++b2</b>a2</a>

select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1>+++++++++</c1>');

UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1>+++++++++</c1>')

<a>a1<b>b1<c1>+++++++++</c1>b2</b>a2</a>

select UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1/>');

UpdateXML('<a>a1<b>b1<c>c1</c>b2</b>a2</a>','/a/b/c','<c1/>')

<a>a1<b>b1<c1/>b2</b>a2</a>

SET @xml='<a><b>bb</b></a>';

select UpdateXML(@xml, '/a/b', '<b>ccc</b>');

UpdateXML(@xml, '/a/b', '<b>ccc</b>')

<a><b>ccc</b></a>

SET @xml='<a aa1="aa1" aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a>';

select UpdateXML(@xml, '/a/b', '<b>ccc</b>');

UpdateXML(@xml, '/a/b', '<b>ccc</b>')

<a aa1="aa1" aa2="aa2"><b>ccc</b></a>

select UpdateXML(@xml, '/a/@aa1', '');

UpdateXML(@xml, '/a/@aa1', '')

<a  aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a>

select UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"');

UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"')

<a aa3="aa3" aa2="aa2"><b bb1="bb1" bb2="bb2">bb</b></a>

select UpdateXML(@xml, '/a/@aa2', '');

UpdateXML(@xml, '/a/@aa2', '')

<a aa1="aa1" ><b bb1="bb1" bb2="bb2">bb</b></a>

select UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"');

UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"')

<a aa1="aa1" aa3="aa3"><b bb1="bb1" bb2="bb2">bb</b></a>

select UpdateXML(@xml, '/a/b/@bb1', '');

UpdateXML(@xml, '/a/b/@bb1', '')

<a aa1="aa1" aa2="aa2"><b  bb2="bb2">bb</b></a>

select UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"');

UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"')

<a aa1="aa1" aa2="aa2"><b bb3="bb3" bb2="bb2">bb</b></a>

select UpdateXML(@xml, '/a/b/@bb2', '');

UpdateXML(@xml, '/a/b/@bb2', '')

<a aa1="aa1" aa2="aa2"><b bb1="bb1" >bb</b></a>

select UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"');

UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"')

<a aa1="aa1" aa2="aa2"><b bb1="bb1" bb3="bb3">bb</b></a>

select updatexml('<div><div><span>1</span><span>2</span></div></div>',

'/','<tr><td>1</td><td>2</td></tr>') as upd1;

upd1

<tr><td>1</td><td>2</td></tr>

select updatexml('', '/', '') as upd2;

upd2

SET @xml= '<order><clerk>lesser wombat</clerk></order>';

select extractvalue(@xml,'order/clerk');

extractvalue(@xml,'order/clerk')

lesser wombat

select extractvalue(@xml,'/order/clerk');

extractvalue(@xml,'/order/clerk')

lesser wombat

select extractvalue('<a><b>B</b></a>','/a|/b');

extractvalue('<a><b>B</b></a>','/a|/b')

select extractvalue('<a><b>B</b></a>','/a|b');

extractvalue('<a><b>B</b></a>','/a|b')

select extractvalue('<a>a<b>B</b></a>','/a|/b');

extractvalue('<a>a<b>B</b></a>','/a|/b')

a

select extractvalue('<a>a<b>B</b></a>','/a|b');

extractvalue('<a>a<b>B</b></a>','/a|b')

a

select extractvalue('<a>a<b>B</b></a>','a|/b');

extractvalue('<a>a<b>B</b></a>','a|/b')

a

select extractvalue('<a>A</a>','/<a>');

ERROR HY000: XPATH error: comparison of two nodesets is not supported: '<a>'

select extractvalue('<a><b>b</b><b!>b!</b!></a>','//b!');

ERROR HY000: XPATH syntax error: '!'

select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant::*');

extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant::*')

B C

select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/self::*');

extractvalue('<a>A<b>B<c>C</c></b></a>','/a/self::*')

A

select extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant-or-self::*');

extractvalue('<a>A<b>B<c>C</c></b></a>','/a/descendant-or-self::*')

A B C

select extractvalue('<A_B>A</A_B>','/A_B');

extractvalue('<A_B>A</A_B>','/A_B')

A

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[position()]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[position()]')

B1 B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]')

B1 B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]')

B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]')

B1

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]')

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]')

B1 B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]')

B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)]')

B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)-1]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)-1]')

B1

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=1]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=1]')

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=2]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=2]')

B1 B2

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=position()]');

extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=position()]')

B2

select extractvalue('<a>Jack</a>','/a[contains(../a,"J")]');

extractvalue('<a>Jack</a>','/a[contains(../a,"J")]')

Jack

select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]');

extractvalue('<a>Jack</a>','/a[contains(../a,"j")]')

Jack

select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]' collate latin1_bin);

extractvalue('<a>Jack</a>','/a[contains(../a,"j")]' collate latin1_bin)

select extractvalue('<a>Jack</a>' collate latin1_bin,'/a[contains(../a,"j")]');

extractvalue('<a>Jack</a>' collate latin1_bin,'/a[contains(../a,"j")]')

select ExtractValue('<tag1><![CDATA[test]]></tag1>','/tag1');

ExtractValue('<tag1><![CDATA[test]]></tag1>','/tag1')

test

select extractValue('<a>a','/a');

extractValue('<a>a','/a')

NULL

Warnings:

Warning	1525	Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT'

select extractValue('<a>a<','/a');

extractValue('<a>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('<a>a</','/a');

extractValue('<a>a</','/a')

NULL

Warnings:

Warning	1525	Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)'

select extractValue('<a>a</a','/a');

extractValue('<a>a</a','/a')

NULL

Warnings:

Warning	1525	Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)'

select extractValue('<a>a</a></b>','/a');

extractValue('<a>a</a></b>','/a')

NULL

Warnings:

Warning	1525	Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)'

select extractValue('<a b=>a</a>','/a');

extractValue('<a b=>a</a>','/a')

NULL

Warnings:

Warning	1525	Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)'

select extractValue('<e>1</e>','position()');

ERROR HY000: XPATH syntax error: ''

select extractValue('<e>1</e>','last()');

ERROR HY000: XPATH syntax error: ''

select extractValue('<e><a>1</a></e>','/e/');

ERROR HY000: XPATH syntax error: ''

set names utf8;

select extractValue('<Ñ><r>r</r></Ñ>','/Ñ/r');

extractValue('<Ñ><r>r</r></Ñ>','/Ñ/r')

r

select extractValue('<r><Ñ>Ñ</Ñ></r>','/r/Ñ');

extractValue('<r><Ñ>Ñ</Ñ></r>','/r/Ñ')

Ñ

select extractValue('<Ñ r="r"/>','/Ñ/@r');

extractValue('<Ñ r="r"/>','/Ñ/@r')

r

select extractValue('<r Ñ="Ñ"/>','/r/@Ñ');

extractValue('<r Ñ="Ñ"/>','/r/@Ñ')

Ñ

DROP PROCEDURE IF EXISTS p2;

CREATE PROCEDURE p2 ()

BEGIN

DECLARE p LONGTEXT CHARACTER SET UTF8 DEFAULT '<Ñ><r>A</r></Ñ>';

SELECT EXTRACTVALUE(p,'/Ñ/r');

END//

CALL p2();

EXTRACTVALUE(p,'/Ñ/r')

A

DROP PROCEDURE p2;

select extractValue('<ns:element xmlns:ns="myns"/>','count(ns:element)');

extractValue('<ns:element xmlns:ns="myns"/>','count(ns:element)')

1

select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element');

extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element')

a

select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element/@xmlns:ns');

extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element/@xmlns:ns')

myns

select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/foo.bar');

extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/foo.bar')

Data

select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/something');

extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/something')

Otherdata

select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','/zot/tim0/02');

ERROR HY000: XPATH syntax error: '02'

select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*');

extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*')

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('<x.-_:>test</x.-_:>','//*');

extractValue('<x.-_:>test</x.-_:>','//*')

test

set @xml= "<entry><id>pt10</id><pt>10</pt></entry><entry><id>pt50</id><pt>50</pt></entry>";

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[(10<pt)]/id");

ExtractValue(@xml, "/entry[(10<pt)]/id")

pt50

select ExtractValue(@xml, "/entry[(10<=pt)]/id");

ExtractValue(@xml, "/entry[(10<=pt)]/id")

pt10 pt50

select ExtractValue(@xml, "/entry[(50=pt)]/id");

ExtractValue(@xml, "/entry[(50=pt)]/id")

pt50

select ExtractValue(@xml, "/entry[(50!=pt)]/id");

ExtractValue(@xml, "/entry[(50!=pt)]/id")

pt10

select ExtractValue(@xml, "/entry[(50>pt)]/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[(50<pt)]/id");

ExtractValue(@xml, "/entry[(50<pt)]/id")

select ExtractValue(@xml, "/entry[(50<=pt)]/id");

ExtractValue(@xml, "/entry[(50<=pt)]/id")

pt50

select ExtractValue('<a><b><Text>test</Text></b></a>','/a/b/Text');

ExtractValue('<a><b><Text>test</Text></b></a>','/a/b/Text')

test

select ExtractValue('<a><b><comment>test</comment></b></a>','/a/b/comment');

ExtractValue('<a><b><comment>test</comment></b></a>','/a/b/comment')

test

select ExtractValue('<a><b><node>test</node></b></a>','/a/b/node');

ExtractValue('<a><b><node>test</node></b></a>','/a/b/node')

test

select ExtractValue('<a><b><processing-instruction>test</processing-instruction></b></a>','/a/b/processing-instruction');

ExtractValue('<a><b><processing-instruction>test</processing-instruction></b></a>','/a/b/processing-instruction')

test

select ExtractValue('<a><and>test</and></a>', '/a/and');

ExtractValue('<a><and>test</and></a>', '/a/and')

test

select ExtractValue('<a><or>test</or></a>', '/a/or');

ExtractValue('<a><or>test</or></a>', '/a/or')

test

select ExtractValue('<a><mod>test</mod></a>', '/a/mod');

ExtractValue('<a><mod>test</mod></a>', '/a/mod')

test

select ExtractValue('<a><div>test</div></a>', '/a/div');

ExtractValue('<a><div>test</div></a>', '/a/div')

test

select ExtractValue('<a><and:and>test</and:and></a>', '/a/and:and');

ExtractValue('<a><and:and>test</and:and></a>', '/a/and:and')

test

select ExtractValue('<a><or:or>test</or:or></a>', '/a/or:or');

ExtractValue('<a><or:or>test</or:or></a>', '/a/or:or')

test

select ExtractValue('<a><mod:mod>test</mod:mod></a>', '/a/mod:mod');

ExtractValue('<a><mod:mod>test</mod:mod></a>', '/a/mod:mod')

test

select ExtractValue('<a><div:div>test</div:div></a>', '/a/div:div');

ExtractValue('<a><div:div>test</div:div></a>', '/a/div:div')

test

select ExtractValue('<a><ancestor>test</ancestor></a>', '/a/ancestor');

ExtractValue('<a><ancestor>test</ancestor></a>', '/a/ancestor')

test

select ExtractValue('<a><ancestor-or-self>test</ancestor-or-self></a>', '/a/ancestor-or-self');

ExtractValue('<a><ancestor-or-self>test</ancestor-or-self></a>', '/a/ancestor-or-self')

test

select ExtractValue('<a><attribute>test</attribute></a>', '/a/attribute');

ExtractValue('<a><attribute>test</attribute></a>', '/a/attribute')

test

select ExtractValue('<a><child>test</child></a>', '/a/child');

ExtractValue('<a><child>test</child></a>', '/a/child')

test

select ExtractValue('<a><descendant>test</descendant></a>', '/a/descendant');

ExtractValue('<a><descendant>test</descendant></a>', '/a/descendant')

test

select ExtractValue('<a><descendant-or-self>test</descendant-or-self></a>', '/a/descendant-or-self');

ExtractValue('<a><descendant-or-self>test</descendant-or-self></a>', '/a/descendant-or-self')

test

select ExtractValue('<a><following>test</following></a>', '/a/following');

ExtractValue('<a><following>test</following></a>', '/a/following')

test

select ExtractValue('<a><following-sibling>test</following-sibling></a>', '/a/following-sibling');

ExtractValue('<a><following-sibling>test</following-sibling></a>', '/a/following-sibling')

test

select ExtractValue('<a><namespace>test</namespace></a>', '/a/namespace');

ExtractValue('<a><namespace>test</namespace></a>', '/a/namespace')

test

select ExtractValue('<a><parent>test</parent></a>', '/a/parent');

ExtractValue('<a><parent>test</parent></a>', '/a/parent')

test

select ExtractValue('<a><preceding>test</preceding></a>', '/a/preceding');

ExtractValue('<a><preceding>test</preceding></a>', '/a/preceding')

test

select ExtractValue('<a><preceding-sibling>test</preceding-sibling></a>', '/a/preceding-sibling');

ExtractValue('<a><preceding-sibling>test</preceding-sibling></a>', '/a/preceding-sibling')

test

select ExtractValue('<a><self>test</self></a>', '/a/self');

ExtractValue('<a><self>test</self></a>', '/a/self')

test

set @i=1;

select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]')

b1

set @i=2;

select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]')

b2

set @i=NULL;

select ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/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('<a><b>b1</b><b>b2</b><b>b3</b></a>');

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('<a><b>b1</b><b>b2</b></a>','/a/b');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b')

b1 b2

No matches

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/c');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/c')

Index out of range

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[-1]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[-1]')

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[10]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[10]')

With string-to-number conversion

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1"]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1"]')

b1

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b["1 and string"]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/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('<a><b>b1</b><b>b2</b></a>','/a/b["string and 1"]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/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('<a><b>b1</b><b>b2</b></a>','/a/b["string"]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/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('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]')

b1

SET @i='1 and string';

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]')

b1

SET @i='string and 1';

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]')

SET @i='string';

SELECT ExtractValue('<a><b>b1</b><b>b2</b></a>','/a/b[$@i]');

ExtractValue('<a><b>b1</b><b>b2</b></a>','/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('<a><b>b1</b><b>b2</b></a>', '1');

ExtractValue(xml,'/a/b[$i]')

b1

CALL spxml('<a><b>b1</b><b>b2</b></a>', '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('<a><b>b1</b><b>b2</b></a>', '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('<a><b>b1</b><b>b2</b></a>', '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>a</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</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</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;

End of 5.1 tests