Any good reasons to use a dynamic XQuery expression via a PL/SQL variable?
It's not a good idea for two reasons :
- It doesn't work with the XMLNamespaces clause, it is simply ignored.
- It disables parse-time optimization, so no streaming evaluation even tough you're using a binary xmltype source.
Use a static XQuery string as shown in the beginning of this thread.
Or, if you really need a variable expression you'll have to declare the namespace mappings in it as well :
'declare namespace soap = "http://www.w3.org/2003/05/soap-envelope";
declare namespace diffgr = "urn:schemas-microsoft-com:xml-diffgram-v1";
declare namespace ns0 = "http://api.***.com/";
but again that usage is discouraged.
Also note that declaring namespaces that way only apply to the scope of the main XQuery expression, so if there are any namespace-qualified PATH expressions in the COLUMNS clause, it won't work either.