2 Replies Latest reply: Jan 29, 2013 7:33 AM by Samuel Rabini RSS

    XMLExists returning 1 instead of 0

    Samuel Rabini
      Hi,

      I'm working on an Oracle Database 11g Release 11.1.0.6.0 - 64bit Production With the Real Application Clusters option.

      I'm facing a problem while using XMLExists function.

      The source XML is:
      <value>
         <welcome_std>std_welcome_message</welcome_std>
      </value>
      This one:
            SELECT CASE WHEN XMLExists('$XML/value/$SETTING' passing c.value AS "XML", 'welcome_adv' as "SETTING") THEN 1 ELSE 0 END
              FROM table c
            WHERE ID = 1;
      is returning 1 (wrong).

      This one:
            SELECT CASE WHEN XMLExists('$XML/value/welcome_adv' passing c.value AS "XML") THEN 1 ELSE 0 END
              FROM table c
            WHERE ID = 1;
      is returning 0 (right).

      'welcome_adv' is a variable, so I should use the first style.
      What's wrong with the function?

      Thanks in advance,
      Samuel
        • 1. Re: XMLExists returning 1 instead of 0
          odie_63
          Hi,
          'welcome_adv' is a variable, so I should use the first style.
          What's wrong with the function?
          There's nothing wrong with the function.

          Maybe "SETTING" is a variable within the scope of your program/query but you're trying to use it as a static XPath step.
          If I take an SQL analogy, it's like trying to use a bind variable to pass a column or table name to a dynamic SQL query : that's not possible, identifiers have to be known at parse time.

          I see two options (tested on 11.2.0.2) :

          1) Building the XQuery string on the fly :
          SQL> WITH c AS (
            2    select xmltype('<value><welcome_std>std_welcome_message</welcome_std></value>') value
            3    from dual
            4  )
            5  SELECT CASE WHEN XMLExists(('$XML/value/' || 'welcome_adv') passing c.value AS "XML") THEN 1 ELSE 0 END
            6  FROM c ;
           
          CASEWHENXMLEXISTS(('$XML/VALUE
          ------------------------------
                                       0
           
          Note that this may not work in your version (can't check), as Oracle has changed the behaviour of dynamic XQuery throughout versions.


          2) Using a name() accessor, and therefore a true bind variable :
          SQL> WITH c AS (
            2    select xmltype('<value><welcome_std>std_welcome_message</welcome_std></value>') value
            3    from dual
            4  )
            5  SELECT CASE WHEN XMLExists('$XML/value/*[name()=$SETTING]'
            6                     passing c.value AS "XML"
            7                           , 'welcome_std' as "SETTING"
            8                   ) THEN 1
            9            ELSE 0 END
           10  FROM c ;
           
          CASEWHENXMLEXISTS('$XML/VALUE/
          ------------------------------
                                       1
           
          • 2. Re: XMLExists returning 1 instead of 0
            Samuel Rabini
            Hi,

            thanks for the replay, I didn't know about the second syntax, it works perfectly.

            Thanks!

            Samuel