Oracle used : Release 22.214.171.124.0
I want to insert an XML document in a binary XML field. But for an element the value includes characters like > or <.
Here an example of request :
update /*+ no_xml_query_rewrite */ ms2 set mstag=XMLQuery('copy $tmp := .modify (for $i in $tmp/tags/fields[@id="MS"]
return insert nodes (<value>TEST\n<address1></value>) into $i) return $tmp' passing mstag returning content) where psid=5900;
But because we use the character & in a SQL request, this request failed. We have a prompt to specify 2 values.
So what is the way to give the value <address1> to the element value in the example ?
Thanks in advance
The command doesn't really "fail" per se. It's just that the tool you're using recognizes "&" characters and treats subsequent identifiers as replacement variables.
What tool are you using ?
For example, in SQL*Plus you can disable this behaviour via the SET SCAN OFF or SET DEFINE OFF command.