Forum Stats

  • 3,875,461 Users
  • 2,266,923 Discussions
  • 7,912,222 Comments

Discussions

predicates on xmltype column nodes

921026
921026 Member Posts: 2
edited Jul 25, 2012 10:13AM in XQuery
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

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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
     

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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
     
This discussion has been closed.