This discussion is archived
2 Replies Latest reply: Jan 29, 2013 5:33 AM by Samuel Rabini RSS

XMLExists returning 1 instead of 0

Samuel Rabini Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

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

    Thanks!

    Samuel

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points