I want to create a stored procedure that is able to edit an XML based on the path the user inputs but using passing seem to not work at all.
Here is my sample XML:
Here is the stored procedure i have created which I am having problems with:
CREATE PROCEDURE XMLTRIAL(
in A VARCHAR(50),
in B VARCHAR(50)
DYNAMIC RESULT SETS 1
SET SAMPLEXML = xmlquery('copy $test := $SAMPLEXML modify
(do replace value of $test/$x with "first",
do replace value of $test/$y with "second"
A as "x",
B as "y");
I want the user to input the XML path to replace with, a sample user input would be these sample path that i have colored in red fonts. Yes it works if i would set them directly in the code itself, but I want that the user would have a freedom to set the XML path.
do replace value of $test/Main/First/FirstInner with "first",
do replace value of $test/Main/Second with "second"
Every time i would input it gives me an error saying:
A target node in one or more replace value of expressions is not a node that was newly created in the copy clause of the transform expression. Error QName=err:XUDY0014.. SQLCODE=-16082, SQLSTATE=10703, DRIVER=4.14.113
Is there something wrong with my code? I think XML path is not a varchar datatype and i don't know what datatype should I use to make it work. I need help regarding this. Thanks in advance.
Is there anyone here that can tell me if this kind of approach I am coding works or not? I am really out of ideas now. I am stuck on how to create a user input based XML updating storedprocedure.
A simple "no it will not work" or "yes it is possible" will do.
So I can move on to another experiment. Thanks in advance.