predicates on xmltype column nodes
921026Jul 25 2012 — edited Jul 25 2012hi,
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