extractvalue: Only constant XPATH queries are supported

In mysql extractvalue is used in xml column to extract the values from it.


extractvalue cannot be implemented directly into query for attribute matching in joining a table.

for attribute matching join two tables with the coulmns with extractvalue using concat function. concat function is used to assign the variable in xml.

it can be used as a function.

CREATE FUNCTION `exv`(ky text,xpath text) RETURNS text CHARSET latin1
BEGIN
 return cast(extractvalue(ky,xpath) as char);
END $$

create table tbl_xml (u_id int,t_xml text)
insert into tbl_xml(u_id,t_xml) values(4,'<a o_id="197" amt="20.00"/><a o_id="198" amt="10.00"/>')


create table tbl_order(u_id int,o_id int,o_amt int);


insert into tbl_order(u_id,o_id,o_amt) values(1,11,20),(2,20,40),(4,197,60),(4,198,20);

select *,exv(A.t_xml,concat('sum(//a[@o_id="',B.o_id,'"]/@amt)')) from tbl_xml A join tbl_order B on A.u_id=B.u_id;


select *,exv(A.t_xml,concat('sum(//a[@o_id="',B.o_id,'"]/@amt)')) from tbl_xml A join tbl_order B on exv(A.t_xml,concat('(//a[@o_id="',B.o_id,'"]/@amt)'));