0 Replies Latest reply: Jan 28, 2007 3:29 PM by 494018 RSS

    XMLPARSE and NULL values

    494018
      Hi,

      The results of the following test case (executed on Oracle XE) are puzzling. Is this a bug or some feature I'm unaware of?
      SQL> set feedback off
      SQL> create table t ( key varchar2(10), xml_fragment varchar2(20) ) ;
      SQL>
      SQL> insert into t values( 'row 1', '<X>a</X>' );
      SQL> insert into t values( 'row 2', '<X>a</X> <Y>b</Y>' );
      SQL> insert into t values( 'row 3', null );
      SQL>
      SQL> column parsed_fragment format a20
      SQL>

      SQL> select
        2    key ,
        3    xml_fragment ,
        4    xmlparse( content xml_fragment ) as parsed_fragment
        5  from
        6    t
        7  where
        8    key in ( 'row 3' )
        9  ;

      KEY        XML_FRAGMENT         PARSED_FRAGMENT
      ---------- -------------------- --------------------
      row 3      (null)               (null)


      SQL> select
        2    key ,
        3    xml_fragment ,
        4    xmlparse( content xml_fragment ) as parsed_fragment
        5  from
        6    t
        7  where
        8    key in ( 'row 3', 'row 1' )
        9  ;

      KEY        XML_FRAGMENT         PARSED_FRAGMENT
      ---------- -------------------- --------------------
      row 1      <X>a</X>             <X>a</X>
      row 3      (null)               <X>a</X>


      SQL> select
        2    key ,
        3    xml_fragment ,
        4    xmlparse( content xml_fragment ) as parsed_fragment
        5  from
        6    t
        7  where
        8    key in ( 'row 3', 'row 2' )
        9  ;

      KEY        XML_FRAGMENT         PARSED_FRAGMENT
      ---------- -------------------- --------------------
      row 2      <X>a</X> <Y>b</Y>    <X>a</X> <Y>b</Y>
      row 3      (null)               <X>a</X> <Y>b</Y>


      SQL> select
        2    key ,
        3    xml_fragment ,
        4    xmlparse( content xml_fragment ) as parsed_fragment
        5  from
        6    t
        7  where
        8    key in ( 'row 3', 'row 2', 'row 1' )
        9  ;

      KEY        XML_FRAGMENT         PARSED_FRAGMENT
      ---------- -------------------- --------------------
      row 1      <X>a</X>             <X>a</X>
      row 2      <X>a</X> <Y>b</Y>    <X>a</X> <Y>b</Y>
      row 3      (null)               <X>a</X> <Y>b</Y>


      SQL> drop table t;
      The SQL Reference manual entry for XMLPARSE says

      "if value_expr resolves to null, then the function returns null.".

      So, I expected the value under PARSED_FRAGMENT to always be null for the row with KEY = 'row 3', but instead it seems to vary depending on other values in the result set.

      What's going on?