1136 lines
40 KiB
Plaintext
1136 lines
40 KiB
Plaintext
|
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;
|
||
|
|
SET @xml=
|
||
|
|
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
|
||
|
|
<html>
|
||
|
|
<head>
|
||
|
|
<title> Title - document with document declaration</title>
|
||
|
|
</head>
|
||
|
|
<body> Hi, Im a webpage with document a declaration </body>
|
||
|
|
</html>';
|
||
|
|
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('<xml "xxx" "yyy">CharData</xml>', '/xml');
|
||
|
|
ExtractValue('<xml "xxx" "yyy">CharData</xml>', '/xml')
|
||
|
|
NULL
|
||
|
|
Warnings:
|
||
|
|
Warning 1525 Incorrect XML value: 'parse error at line 1 pos 11: STRING unexpected ('>' wanted)'
|
||
|
|
SELECT ExtractValue('<xml xxx "yyy">CharData</xml>', '/xml');
|
||
|
|
ExtractValue('<xml xxx "yyy">CharData</xml>', '/xml')
|
||
|
|
NULL
|
||
|
|
Warnings:
|
||
|
|
Warning 1525 Incorrect XML value: 'parse error at line 1 pos 17: STRING unexpected ('>' wanted)'
|
||
|
|
set @x=10;
|
||
|
|
select extractvalue('<a></a>','$@x/a');
|
||
|
|
ERROR HY000: XPATH syntax error: '/a'
|
||
|
|
select extractvalue('<a></a>','round(123.4)/a');
|
||
|
|
ERROR HY000: XPATH syntax error: '/a'
|
||
|
|
select extractvalue('<a></a>','1/a');
|
||
|
|
ERROR HY000: XPATH syntax error: '/a'
|
||
|
|
select extractvalue('<a></a>','"b"/a');
|
||
|
|
ERROR HY000: XPATH syntax error: '/a'
|
||
|
|
select extractvalue('<a></a>','(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, '<?xml version="1.0"?><bla name="blubb"></bla>'),
|
||
|
|
(14, '<xml version="kaputt">');
|
||
|
|
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
|
||
|
|
<?xml version="1.0"?><bla test></bla> <?xml version="1.0"?><bla test></bla>
|
||
|
|
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'<!--' USING utf8),'1','1');
|
||
|
|
UPDATEXML(CONVERT(_latin1'<!--' USING utf8),'1','1')
|
||
|
|
NULL
|
||
|
|
#
|
||
|
|
# Bug#11766725 (bug#59901): EXTRACTVALUE STILL BROKEN AFTER FIX FOR BUG #44332
|
||
|
|
#
|
||
|
|
SELECT ExtractValue(CONVERT('<\"', BINARY(10)), 1);
|
||
|
|
ExtractValue(CONVERT('<\"', BINARY(10)), 1)
|
||
|
|
NULL
|
||
|
|
Warnings:
|
||
|
|
Warning 1525 Incorrect XML value: 'parse error at line 1 pos 11: STRING unexpected (ident or '/' wanted)'
|
||
|
|
End of 5.1 tests
|