1 Reply Latest reply on Mar 4, 2019 7:31 PM by tsuji

    Passing path in XMLQUERY

    3891771

      I need some help to pass the path dynamicaly using xmlquery as example below.

      At line 10 I want something like : $input_path (input_path := "Test1\Test2")

      The input_value is Test3

      I tried but I get the error "ORA-19224: XPTY0004 - não correspondência de tipos estáticos XQuery: era esperado - node()* foi obtido - xs:string". 

       

      1. create table xqupdate as  
      2.     select 1 as ind,to_clob('<ns:Test1 xmlns:ns="abc"><Test2><ns:Test3>adfkgjdfgkljhhgkjdfhgldkfjgh</ns:Test3></Test2></ns:Test1>') clobdata 
      3.     from dual; 
      4.      
      5. create or replace procedure test_Clob(p_input_tag_name varchar2, p_tag_update_Value varchar2) as 
      6. begin 
      7.     update xqupdate set clobdata = 
      8.     xmlquery( 
      9.         'copy $tmp := . 
      10.         modify (for $i in $tmp//*[local-name()=$input_tag] return replace value of node $i with $input_value) 
      11.         return $tmp' 
      12.         passing xmltype(clobdata), p_input_tag_name as "input_tag", p_tag_update_value as "input_value" 
      13.         returning content 
      14.     ).GetClobVal() 
      15.     where ind = 1; 
      16. end test_Clob; 
        • 1. Re: Passing path in XMLQUERY
          tsuji

          Maybe let me provide some elements for you to develop in order to achieve the requested functionality of incorporating what we may call dynamic xpath.

           

          The main thing to note is that within the xmlquery(), the part of script between '...' can actually be a full xquery "program" proper. It can be much more elaborated than a simple use of a couple of line pertaining to xquery update facility, And xquery itself has a lot of functionality that can be used to facilitate the construction of dynamic xpath from xs:string. Whether there exists a simple built-in xpath function can do it, I doubt it. But there are quite a bit of user-defined functions, amid usually written with clearly stated limitation, exist out there. And you can take advantage using them.

           

          A wealth of user defined functions is collected in the functx site. And in particular what comes to my mind is the functx:dynamic-path() which itself depends on three helper functions that you need to add to the script.

          FunctX XQuery Functions: functx:dynamic-path

          It comes with some sever restrictions : but with your need of having ns:Test1/Test2 part, it is within its applicable range for dynamically constructed xpath.

           

          Take a look of it and incorporating it to the xmlquery() will do it nicely though on its look may seem laborious for some. But the main thing is it gets the job done. If you find some difficulty incorporating it, return by describing what advance you've made and may be I can help you further if necessary. If you think that is just a kind of sweetener and that you prefer to let it go, then don't worry about it.