1 Reply Latest reply: Jul 25, 2012 9:13 AM by odie_63 RSS

    predicates on xmltype column nodes

    921026
      hi,
      Here i'm sending 2 records with table. What i need is value of element <sno> for which the <name> is either Tamil or Ravi and for the record keyid=111
      That is output I'm looking is 102888(102 is sno of Tamil and 888 is for Ravi).

      with a as (
      SELECT xmltype('<class>
           <student>
                <name>Raju</name>
                <sno>101</sno>
           </student>
           <student>
                <name>Tamil</name>
                <sno>102</sno>
           </student>
      <student>
                <name>Ravi</name>
                <sno>888</sno>
           </student>
      </class>') result1,'111' keyid
      FROM dual
      union all
      SELECT xmltype('<class>
           <student>
                <name>Tamil</name>
                <sno>108</sno>
           </student>
           <student>
                <name>Alex</name>
                <sno>208</sno>
           </student>
      </class>') result1,'222' keyid FROM dual
      ) SELECT a.keyid,a.result1 from a


      Thanks,
      Ravi
        • 1. Re: predicates on xmltype column nodes
          odie_63
          Hi,

          Do you really need concatenated values, or are you actually looking for separate rows?

          Assuming a recent version :
          SQL> with a as (
            2  SELECT xmltype('<class>
            3  <student>
            4  <name>Raju</name>
            5  <sno>101</sno>
            6  </student>
            7  <student>
            8  <name>Tamil</name>
            9  <sno>102</sno>
           10  </student>
           11  <student>
           12  <name>Ravi</name>
           13  <sno>888</sno>
           14  </student>
           15  </class>') result1,'111' keyid
           16  FROM dual
           17  union all
           18  SELECT xmltype('<class>
           19  <student>
           20  <name>Tamil</name>
           21  <sno>108</sno>
           22  </student>
           23  <student>
           24  <name>Alex</name>
           25  <sno>208</sno>
           26  </student>
           27  </class>') result1,'222' keyid FROM dual
           28  )
           29  select xmlcast(
           30           xmlquery('/class/student[name="Tamil" or name="Ravi"]/sno'
           31                    passing a.result1
           32                    returning content)
           33           as varchar2(30)
           34         )
           35  from a
           36  where keyid = '111'
           37  ;
           
          XMLCAST(XMLQUERY('/CLASS/STUDE
          ------------------------------
          102888