Edit File - File Manager

Edit File: xml.test

SET @xml='<a aa1="aa1" aa2="aa2">a1b1<c>c1</c>b2a2</a>';
SELECT extractValue(@xml,'/a');
SELECT extractValue(@xml,'/a/b');
SELECT extractValue(@xml,'/a/b/c');
SELECT extractValue(@xml,'/a/@aa1');
SELECT extractValue(@xml,'/a/@aa2');
SELECT extractValue(@xml,'/a/@*');
SELECT extractValue(@xml,'//@ba1');

SELECT extractValue(@xml,'//a');
SELECT extractValue(@xml,'//b');
SELECT extractValue(@xml,'//c');
SELECT extractValue(@xml,'/a//b');
SELECT extractValue(@xml,'/a//c');
SELECT extractValue(@xml,'//*');
SELECT extractValue(@xml,'/a//*');
SELECT extractValue(@xml,'/./a');
SELECT extractValue(@xml,'/a/b/.');
SELECT extractValue(@xml,'/a/b/..');
SELECT extractValue(@xml,'/a/b/../@aa1');
SELECT extractValue(@xml,'/*');
SELECT extractValue(@xml,'/*/*');
SELECT extractValue(@xml,'/*/*/*');

SELECT extractValue(@xml,'/a/child::*');
SELECT extractValue(@xml,'/a/self::*');
SELECT extractValue(@xml,'/a/descendant::*');
SELECT extractValue(@xml,'/a/descendant-or-self::*');
SELECT extractValue(@xml,'/a/attribute::*');
SELECT extractValue(@xml,'/a/b/c/parent::*');
SELECT extractValue(@xml,'/a/b/c/ancestor::*');
SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*');
SELECT extractValue(@xml,'/descendant-or-self::*');

SET @xml='<a>a11b11b21<c>c1</c>b22a12</a>';
SELECT extractValue(@xml,'/a/b/c/ancestor-or-self::*');
SELECT extractValue(@xml,'//@ba');

SET @xml='<a>b<c>c</c></a>';
SELECT extractValue(@xml,'/a/b');
SELECT extractValue(@xml,'/a/c');
SELECT extractValue(@xml,'/a/child::b');
SELECT extractValue(@xml,'/a/child::c');

SET @xml='<a>b1<c>c1</c>b2<c>c2</c></a>';
SELECT extractValue(@xml,'/a/b[1]');
SELECT extractValue(@xml,'/a/b[2]');
SELECT extractValue(@xml,'/a/c[1]');
SELECT extractValue(@xml,'/a/c[2]');

SET @xml='<a><c x="xc1" x="xc2"/></a>';
SELECT extractValue(@xml,'/a//@x');
SELECT extractValue(@xml,'/a//@x[1]');
SELECT extractValue(@xml,'/a//@x[2]');

SET @xml='<a>b1b2<c>c1b1c1b2</c><c>c2b1</c></a>';
SELECT extractValue(@xml,'//b[1]');
SELECT extractValue(@xml,'/descendant::b[1]');

SET @xml='<a>b1b2</a>';
SELECT extractValue(@xml,'/a/b[1+0]');
SELECT extractValue(@xml,'/a/b[1*1]');
SELECT extractValue(@xml,'/a/b[--1]');
SELECT extractValue(@xml,'/a/b[2*1-1]');

SELECT extractValue(@xml,'/a/b[1+1]');
SELECT extractValue(@xml,'/a/b[1*2]');
SELECT extractValue(@xml,'/a/b[--2]');
SELECT extractValue(@xml,'/a/b[1*(3-1)]');

SELECT extractValue(@xml,'//*[1=1]');
SELECT extractValue(@xml,'//*[1!=1]');
SELECT extractValue(@xml,'//*[1>1]');
SELECT extractValue(@xml,'//*[2>1]');
SELECT extractValue(@xml,'//*[1>2]');
SELECT extractValue(@xml,'//*[1>=1]');
SELECT extractValue(@xml,'//*[2>=1]');
SELECT extractValue(@xml,'//*[1>=2]');
SELECT extractValue(@xml,'//*[1<1]');
SELECT extractValue(@xml,'//*[2<1]');
SELECT extractValue(@xml,'//*[1<2]');
SELECT extractValue(@xml,'//*[1<=1]');
SELECT extractValue(@xml,'//*[2<=1]');
SELECT extractValue(@xml,'//*[1<=2]');

SET @xml='<a>b11<c>c11</c>b21<c>c21</c></a>';
SELECT extractValue(@xml,'/a/b[c="c11"]');
SELECT extractValue(@xml,'/a/b[c="c21"]');

SET @xml='<a>b11b21</a>';
SELECT extractValue(@xml,'/a/b[@c="c11"]');
SELECT extractValue(@xml,'/a/b[@c="c21"]');

SET @xml='<a>a1b11<d>d11</d>b21<d>d21</d></a>';
SELECT extractValue(@xml, '/a/b[@c="c11"]/d');
SELECT extractValue(@xml, '/a/b[@c="c21"]/d');
SELECT extractValue(@xml, '/a/b[d="d11"]/@c');
SELECT extractValue(@xml, '/a/b[d="d21"]/@c');
SELECT extractValue(@xml, '/a[b="b11"]');
SELECT extractValue(@xml, '/a[b/@c="c11"]');
SELECT extractValue(@xml, '/a[b/d="d11"]');
SELECT extractValue(@xml, '/a[/a/b="b11"]');
SELECT extractValue(@xml, '/a[/a/b/@c="c11"]');
SELECT extractValue(@xml, '/a[/a/b/d="d11"]');

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

SET @xml='<a>abb1b2b3</a>';
select extractValue(@xml,'/a/b[@c="c"]');
select extractValue(@xml,'/a/b[@c="d"]');
select extractValue(@xml,'/a/b[@c="e"]');
select extractValue(@xml,'/a/b[not(@c="e")]');
select extractValue(@xml,'/a/b[@c!="e"]');
select extractValue(@xml,'/a/b[@c="c" or @c="d"]');
select extractValue(@xml,'/a/b[@c="c" and @c="e"]');

SET @xml='<a>b1b2</a>';
select extractValue(@xml,'/a/b[@c]');
select extractValue(@xml,'/a/b[@d]');
select extractValue(@xml,'/a/b[@e]');
select extractValue(@xml,'/a/b[not(@c)]');
select extractValue(@xml,'/a/b[not(@d)]');
select extractValue(@xml,'/a/b[not(@e)]');

select extractValue(@xml, '/a/b[boolean(@c) or boolean(@d)]');
select extractValue(@xml, '/a/b[boolean(@c) or boolean(@e)]');
select extractValue(@xml, '/a/b[boolean(@d) or boolean(@e)]');
select extractValue(@xml, '/a/b[boolean(@c) and boolean(@d)]');
select extractValue(@xml, '/a/b[boolean(@c) and boolean(@e)]');
select extractValue(@xml, '/a/b[boolean(@d) and boolean(@e)]');

select extractValue(@xml, '/a/b[@c or @d]');
select extractValue(@xml, '/a/b[@c or @e]');
select extractValue(@xml, '/a/b[@d or @e]');
select extractValue(@xml, '/a/b[@c and @d]');
select extractValue(@xml, '/a/b[@c and @e]');
select extractValue(@xml, '/a/b[@d and @e]');

SET @xml='<a>b1b2</a>';
SELECT extractValue(@xml,'/a/b[@*]');
SELECT extractValue(@xml,'/a/b[not(@*)]');

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

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

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

SELECT extractValue('<a>aa1<c>c1</c>a2</a>','/a/b[count(c)=0]');
SELECT extractValue('<a>aa1<c>c1</c>a2</a>','/a/b[count(c)=1]');
select extractValue('<a>a1b1b24</a>','/a/b[sum(@ba)=3]');

select extractValue('<a>b1b2</a>','/a/b[1]');
select extractValue('<a>b1b2</a>','/a/b[boolean(1)]');
select extractValue('<a>b1b2</a>','/a/b[true()]');
select extractValue('<a>b1b2</a>','/a/b[number(true())]');

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

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

SET @xml='<a b="11" b="12" b="21" b="22">ab</a>';
select extractValue(@xml, '/a/@b[substring(.,2)="1"]');
select extractValue(@xml, '/a/@b[substring(.,2)="2"]');
select extractValue(@xml, '/a/@b[substring(.,1,1)="1"]');
select extractValue(@xml, '/a/@b[substring(.,1,1)="2"]');
select extractValue(@xml, '/a/@b[substring(.,2,1)="1"]');
select extractValue(@xml, '/a/@b[substring(.,2,1)="2"]');

#
# Bug#16319: XML: extractvalue() returns syntax errors for some functions
#
SET @xml='<a>b1b2</a>';
SELECT extractValue(@xml, '/a/b[string-length("x")=1]');
SELECT extractValue(@xml, '/a/b[string-length("xx")=2]');
SELECT extractValue(@xml, '/a/b[string-length("xxx")=2]');
SELECT extractValue(@xml, '/a/b[string-length("x")]');
SELECT extractValue(@xml, '/a/b[string-length("xx")]');
SELECT extractValue(@xml, '/a/b[string-length()]');
--error 1105
SELECT extractValue(@xml, 'string-length()');
SELECT extractValue(@xml, 'string-length("x")');

SET @xml='<a b="b11" b="b12" b="b21" b="22"/>';
select extractValue(@xml,'/a/@b');
select extractValue(@xml,'/a/@b[contains(.,"1")]');
select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")]');
select extractValue(@xml,'/a/@b[contains(.,"1")][contains(.,"2")][2]');

SET @xml='<a>a1b1<c>c1</c>b2a2</a>';
select UpdateXML('<a>a1b1<c>c1</c>b2a2</a>','/a/b/c','+++++++++');
select UpdateXML('<a>a1b1<c>c1</c>b2a2</a>','/a/b/c','<c1>+++++++++</c1>');
select UpdateXML('<a>a1b1<c>c1</c>b2a2</a>','/a/b/c','<c1/>');

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

SET @xml='<a aa1="aa1" aa2="aa2">bb</a>';
select UpdateXML(@xml, '/a/b', 'ccc');
select UpdateXML(@xml, '/a/@aa1', '');
select UpdateXML(@xml, '/a/@aa1', 'aa3="aa3"');
select UpdateXML(@xml, '/a/@aa2', '');
select UpdateXML(@xml, '/a/@aa2', 'aa3="aa3"');
select UpdateXML(@xml, '/a/b/@bb1', '');
select UpdateXML(@xml, '/a/b/@bb1', 'bb3="bb3"');
select UpdateXML(@xml, '/a/b/@bb2', '');
select UpdateXML(@xml, '/a/b/@bb2', 'bb3="bb3"');

#
# Bug#27898 UPDATEXML Crashes the Server!
#
select updatexml('<div><div>12</div></div>',
 '/','<tr><td>1</td><td>2</td></tr>') as upd1;
select updatexml('', '/', '') as upd2;

#
# Bug#16234 XML: Crash if ExtractValue()
#
SET @xml= '<order><clerk>lesser wombat</clerk></order>';
select extractvalue(@xml,'order/clerk');
select extractvalue(@xml,'/order/clerk');

#
# Bug#16314 XML: extractvalue() crash if vertical bar
#
select extractvalue('<a>B</a>','/a|/b');
select extractvalue('<a>B</a>','/a|b');
select extractvalue('<a>aB</a>','/a|/b');
select extractvalue('<a>aB</a>','/a|b');
select extractvalue('<a>aB</a>','a|/b');

#
# Bug#16312 XML: extractvalue() crash if angle brackets
#
--error 1105
select extractvalue('<a>A</a>','/<a>');

#
# Bug#16313 XML: extractvalue() ignores '!' in names
#
--error 1105
select extractvalue('<a>b<b!>b!</b!></a>','//b!');

#
# Bug #16315 XML: extractvalue() handles self badly
#
select extractvalue('<a>AB<c>C</c></a>','/a/descendant::*');
select extractvalue('<a>AB<c>C</c></a>','/a/self::*');
select extractvalue('<a>AB<c>C</c></a>','/a/descendant-or-self::*');
# Bug #16320 XML: extractvalue() won't accept names containing underscores
#
select extractvalue('<A_B>A</A_B>','/A_B');

#
# Bug#16318: XML: extractvalue() incorrectly returns last() = 1
#
select extractvalue('<a>AB1B2</a>','/a/b[position()]');
select extractvalue('<a>AB1B2</a>','/a/b[count(.)=last()]');
select extractvalue('<a>AB1B2</a>','/a/b[last()]');
select extractvalue('<a>AB1B2</a>','/a/b[last()-1]');
select extractvalue('<a>AB1B2</a>','/a/b[last()=1]');
select extractvalue('<a>AB1B2</a>','/a/b[last()=2]');
select extractvalue('<a>AB1B2</a>','/a/b[last()=position()]');
select extractvalue('<a>AB1B2</a>','/a/b[count(.)]');
select extractvalue('<a>AB1B2</a>','/a/b[count(.)-1]');
select extractvalue('<a>AB1B2</a>','/a/b[count(.)=1]');
select extractvalue('<a>AB1B2</a>','/a/b[count(.)=2]');
select extractvalue('<a>AB1B2</a>','/a/b[count(.)=position()]');
#
# Bug#16316: XML: extractvalue() is case-sensitive with contains()
#
select extractvalue('<a>Jack</a>','/a[contains(../a,"J")]');
select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]');
select extractvalue('<a>Jack</a>','/a[contains(../a,"j")]' collate latin1_bin);
select extractvalue('<a>Jack</a>' collate latin1_bin,'/a[contains(../a,"j")]');

#
# Bug#18285: ExtractValue not returning character
# data within <![CDATA[]]> as expected
#
select ExtractValue('<tag1><![CDATA[test]]></tag1>','/tag1');

#
# Bug#18201: XML: ExtractValue works even if the xml fragment
# is not well-formed xml
#
select extractValue('<a>a','/a');
select extractValue('<a>a<','/a');
select extractValue('<a>a</','/a');
select extractValue('<a>a</a','/a');
select extractValue('<a>a</a>','/a');
select extractValue('<a b=>a</a>','/a');

#
# Bug #18171 XML: ExtractValue: the XPath position()
# function crashes the server!
#
--error 1105
select extractValue('<e>1</e>','position()');
--error 1105
select extractValue('<e>1</e>','last()');

#
# Bug #18172 XML: Extractvalue() accepts mallformed
# XPath without a XPath syntax error
#
--error 1105
select extractValue('<e><a>1</a></e>','/e/');

#
# Bug#16233: XML: ExtractValue() fails with special characters
#
set names utf8;
select extractValue('<Ñ><r>r</r></Ñ>','/Ñ/r');
select extractValue('<r><Ñ>Ñ</Ñ></r>','/r/Ñ');
select extractValue('<Ñ r="r"/>','/Ñ/@r');
select extractValue('<r Ñ="Ñ"/>','/r/@Ñ');
--disable_warnings
DROP PROCEDURE IF EXISTS p2;
--enable_warnings
DELIMITER //;
CREATE PROCEDURE p2 ()
BEGIN
 DECLARE p LONGTEXT CHARACTER SET UTF8 DEFAULT '<Ñ><r>A</r></Ñ>';
 SELECT EXTRACTVALUE(p,'/Ñ/r');
END//
DELIMITER ;//
CALL p2();
DROP PROCEDURE p2;

#
# Bug#18170: XML: ExtractValue():
# XPath expression can't use QNames (colon in names)
#
select extractValue('<ns:element xmlns:ns="myns"/>','count(ns:element)');
select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element');
select extractValue('<ns:element xmlns:ns="myns">a</ns:element>','/ns:element/@xmlns:ns');

#
# Bug#20795 extractvalue() won't accept names containing a dot (.)
#
select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/foo.bar');
select extractValue('<foo><foo.bar>Data</foo.bar><something>Otherdata</something></foo>','/foo/something');

#
# Bug#20854 XML functions: wrong result in ExtractValue
#
--error 1105
select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','/zot/tim0/02');
select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*');
# dot and dash are bad identtifier start character
select extractValue('<.>test</.>','//*');
select extractValue('<->test</->','//*');
# semicolon is good identifier start character
select extractValue('<:>test</:>','//*');
# underscore is good identifier start character
select extractValue('<_>test</_>','//*');
# dot, dash, underscore and semicolon are good identifier middle characters
select extractValue('<x.-_:>test</x.-_:>','//*');

#
# Bug#22823 gt and lt operators appear to be
# reversed in ExtractValue() command
#
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");
select ExtractValue(@xml, "/entry[(pt!=10)]/id");
select ExtractValue(@xml, "/entry[(pt<10)]/id");
select ExtractValue(@xml, "/entry[(pt<=10)]/id");
select ExtractValue(@xml, "/entry[(pt>10)]/id");
select ExtractValue(@xml, "/entry[(pt>=10)]/id");
select ExtractValue(@xml, "/entry[(pt=50)]/id");
select ExtractValue(@xml, "/entry[(pt!=50)]/id");
select ExtractValue(@xml, "/entry[(pt<50)]/id");
select ExtractValue(@xml, "/entry[(pt<=50)]/id");
select ExtractValue(@xml, "/entry[(pt>50)]/id");
select ExtractValue(@xml, "/entry[(pt>=50)]/id");
select ExtractValue(@xml, "/entry[(10=pt)]/id");
select ExtractValue(@xml, "/entry[(10!=pt)]/id");
select ExtractValue(@xml, "/entry[(10>pt)]/id");
select ExtractValue(@xml, "/entry[(10>=pt)]/id");
select ExtractValue(@xml, "/entry[(10<pt)]/id");
select ExtractValue(@xml, "/entry[(10<=pt)]/id");
select ExtractValue(@xml, "/entry[(50=pt)]/id");
select ExtractValue(@xml, "/entry[(50!=pt)]/id");
select ExtractValue(@xml, "/entry[(50>pt)]/id");
select ExtractValue(@xml, "/entry[(50>=pt)]/id");
select ExtractValue(@xml, "/entry[(50<pt)]/id");
select ExtractValue(@xml, "/entry[(50<=pt)]/id");

#
# Bug#24747 XPath error with the node name "Text"
#
#
# Test nodetypes in node name context
#
select ExtractValue('<a><Text>test</Text></a>','/a/b/Text');
select ExtractValue('<a><comment>test</comment></a>','/a/b/comment');
select ExtractValue('<a><node>test</node></a>','/a/b/node');
select ExtractValue('<a><processing-instruction>test</processing-instruction></a>','/a/b/processing-instruction');
#
# Test keywords in node name contexts
#
select ExtractValue('<a><and>test</and></a>', '/a/and');
select ExtractValue('<a><or>test</or></a>', '/a/or');
select ExtractValue('<a><mod>test</mod></a>', '/a/mod');
select ExtractValue('<a><div>test</div></a>', '/a/div');
select ExtractValue('<a><and:and>test</and:and></a>', '/a/and:and');
select ExtractValue('<a><or:or>test</or:or></a>', '/a/or:or');
select ExtractValue('<a><mod:mod>test</mod:mod></a>', '/a/mod:mod');
select ExtractValue('<a><div:div>test</div:div></a>', '/a/div:div');
#
# Test axis names in node name context
#
select ExtractValue('<a><ancestor>test</ancestor></a>', '/a/ancestor');
select ExtractValue('<a><ancestor-or-self>test</ancestor-or-self></a>', '/a/ancestor-or-self');
select ExtractValue('<a><attribute>test</attribute></a>', '/a/attribute');
select ExtractValue('<a><child>test</child></a>', '/a/child');
select ExtractValue('<a><descendant>test</descendant></a>', '/a/descendant');
select ExtractValue('<a><descendant-or-self>test</descendant-or-self></a>', '/a/descendant-or-self');
select ExtractValue('<a><following>test</following></a>', '/a/following');
select ExtractValue('<a><following-sibling>test</following-sibling></a>', '/a/following-sibling');
select ExtractValue('<a><namespace>test</namespace></a>', '/a/namespace');
select ExtractValue('<a><parent>test</parent></a>', '/a/parent');
select ExtractValue('<a><preceding>test</preceding></a>', '/a/preceding');
select ExtractValue('<a><preceding-sibling>test</preceding-sibling></a>', '/a/preceding-sibling');
select ExtractValue('<a><self>test</self></a>', '/a/self');

#
# Bug#26518 XPath and variables problem
# Check with user defined variables
#
set @i=1;
select ExtractValue('<a>b1b2</a>','/a/b[$@i]');
set @i=2;
select ExtractValue('<a>b1b2</a>','/a/b[$@i]');
set @i=NULL;
select ExtractValue('<a>b1b2</a>','/a/b[$@i]');

#
# Check variables in a stored procedure - both local and user variables
# Make sure that SP and local variables with the same name work together.
#
DELIMITER |;
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|
DELIMITER ;|

call spxml('<a>b1b2b3</a>');
drop procedure spxml;

#
# Additional tests for bug#26518
--echo Multiple matches, but no index specification
SELECT ExtractValue('<a>b1b2</a>','/a/b');
--echo No matches
SELECT ExtractValue('<a>b1b2</a>','/a/c');
--echo Index out of range
SELECT ExtractValue('<a>b1b2</a>','/a/b[-1]');
SELECT ExtractValue('<a>b1b2</a>','/a/b[10]');
--echo With string-to-number conversion
SELECT ExtractValue('<a>b1b2</a>','/a/b["1"]');
SELECT ExtractValue('<a>b1b2</a>','/a/b["1 and string"]');
SELECT ExtractValue('<a>b1b2</a>','/a/b["string and 1"]');
SELECT ExtractValue('<a>b1b2</a>','/a/b["string"]');
--echo String-to-number conversion from a user variable
SET @i='1';
SELECT ExtractValue('<a>b1b2</a>','/a/b[$@i]');
SET @i='1 and string';
SELECT ExtractValue('<a>b1b2</a>','/a/b[$@i]');
SET @i='string and 1';
SELECT ExtractValue('<a>b1b2</a>','/a/b[$@i]');
SET @i='string';
SELECT ExtractValue('<a>b1b2</a>','/a/b[$@i]');

--echo String-to-number conversion with a CHAR SP variable
DELIMITER |;
CREATE PROCEDURE spxml(xml VARCHAR(128), i CHAR(16))
BEGIN
 SELECT ExtractValue(xml,'/a/b[$i]');
END|
DELIMITER ;|
CALL spxml('<a>b1b2</a>', '1');
CALL spxml('<a>b1b2</a>', '1 and string');
CALL spxml('<a>b1b2</a>', 'string and 1');
CALL spxml('<a>b1b2</a>', 'string');
DROP PROCEDURE spxml;

#
# Bug#28558 UpdateXML called with garbage crashes server
#
--error 1105
select UpdateXML('<a>a</a>',repeat('a b ',1000),'');
--error 1105
select ExtractValue('<a>a</a>', '/a[@x=@y0123456789_0123456789_0123456789_0123456789]');
--error 1105
select ExtractValue('<a>a</a>', '/a[@x=$y0123456789_0123456789_0123456789_0123456789]');

#
# Bug #31438: updatexml still crashes
#

select updatexml(NULL, 1, 1), updatexml(1, NULL, 1), updatexml(1, 1, NULL);
select updatexml(NULL, NULL, 1), updatexml(1, NULL, NULL), 
       updatexml(NULL, 1, NULL);
select updatexml(NULL, NULL, NULL);

#
# Bug #32557: order by updatexml causes assertion in filesort
#
CREATE TABLE t1(a INT NOT NULL);
INSERT INTO t1 VALUES (0), (0);
SELECT 1 FROM t1 ORDER BY(UPDATEXML(a, '1', '1'));
DROP TABLE t1;

#
# BUG#38227 EXTRACTVALUE doesn't work with DTD declarations 
#
# Check that quoted strings work fine in DOCTYPE declaration.
#

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');
SELECT ExtractValue(@xml, 'html/body');

# These two documents will fail. 
# Quoted strings are not allowed in regular tags
#
SELECT ExtractValue('<xml "xxx" "yyy">CharData</xml>', '/xml');
SELECT ExtractValue('<xml xxx "yyy">CharData</xml>', '/xml');

#
# Bug#42495 updatexml: Assertion failed: xpath->context, file .\item_xmlfunc.cc, line 2507
#
set @x=10;
--error ER_UNKNOWN_ERROR
select extractvalue('<a></a>','$@x/a');
--error ER_UNKNOWN_ERROR
select extractvalue('<a></a>','round(123.4)/a');
--error ER_UNKNOWN_ERROR
select extractvalue('<a></a>','1/a');
--error ER_UNKNOWN_ERROR
select extractvalue('<a></a>','"b"/a');
--error ER_UNKNOWN_ERROR
select extractvalue('<a></a>','(1)/a');

#
# Bug#43183 ExctractValue() brings result list in missorder
#
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;

SELECT
UpdateXML(xml, '/bla/@name', 'test'),
UpdateXML(xml, '/bla/@name', 'test')
FROM t1 ORDER BY t1.id;

DROP TABLE t1;

--echo #
--echo # Bug#57257 Replace(ExtractValue(...)) causes MySQL crash
--echo #
SET NAMES utf8;
SELECT REPLACE(EXTRACTVALUE('1', '/a'),'ds','');

--echo #
--echo # Bug #57820 extractvalue crashes
--echo #
SELECT AVG(DISTINCT EXTRACTVALUE((''),('$@k')));

--echo #
--echo # Bug#57279 updatexml dies with: Assertion failed: str_arg[length] == 0
--echo #

--error ER_ILLEGAL_VALUE_FOR_TYPE
SELECT UPDATEXML(NULL, (LPAD(0.1111E-15, '2011', 1)), 1);
--error ER_ILLEGAL_VALUE_FOR_TYPE
SELECT EXTRACTVALUE('', LPAD(0.1111E-15, '2011', 1));

--echo #
--echo # Bug #44332 	my_xml_scan reads behind the end of buffer
--echo #
SELECT UPDATEXML(CONVERT(_latin1'<' USING utf8),'1','1');
SELECT UPDATEXML(CONVERT(_latin1'<!--' USING utf8),'1','1');

--echo #
--echo # Bug#11766725 (bug#59901): EXTRACTVALUE STILL BROKEN AFTER FIX FOR BUG #44332
--echo #
SELECT ExtractValue(CONVERT('<\"', BINARY(10)), 1);

--echo End of 5.1 tests

--echo #
--echo # Start of 5.5 tests
--echo #

--echo #
--echo # Bug#58175 xml functions read initialized bytes when conversions happen
--echo #
SET NAMES latin1;
SELECT UPDATEXML(CONVERT('' USING swe7), TRUNCATE('',1), 0);

--echo #
--echo # Bug#12375190: UPDATEXML CRASHES ON SIMPLE INPUTS
--echo #
SELECT UPDATEXML('','(a)/a','');
SELECT UPDATEXML('<a><a>x</a></a>','(a)/a','');
SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)/a','');
SELECT UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','');
SELECT ExtractValue('<a><a>aa</a>bb</a>','(a)/a|(a)/b');

--echo #
--echo # End of 5.5 tests
--echo #

Back to File Manager