Q:

select the rows that contains different xml attribute value

select the rows that contains/assigned an xml attribute value
Eg.,
My_table contains two rows each has differnt XML columns say xml-1 and xml-2. 
xml-1 contains  

< foo xmlns="http://xxxxxe" person="Rahul" language="English">

and my xml-2 contains 

< foo xmlns="http://xxxxxe" person="Raj" language="Hindi">

Now I want a sql select query that selects the row whose language="English".

A:

Try this
with xml_table as (select xmltype('<foo person="Rahul" language="English">A</foo>') xml
                    from dual
                    union all
                    select xmltype('<foo person="Raj" language="Hindi">B</foo>') 
                    from dual)
select x.xml.getstringval(), x.xml.extract('/foo/@language')
  from xml_table x
 where x.xml.extract('/foo/@language').getstringval() = 'English'

