Extracting XML Element and Attribute values in Oracle

Let’s assume that you have a XML (xmltype) table column.

<?xml version="1.0" encoding="UTF-8"?>
  <form>
     <data k="a1">
        <value>row 1</value>
     </data>
     <data k="a2">
       <value>row 2</value>
     </data>
</form>

One way to easily extract data is given below

Select Xmltype(t.Col).Extract('//form/data[@k="a1"]/value/text()').Getstringval()  a1
From Mytable t

and reading an attribute value can be like this:

Select Xmltype(t.Col).Extract('//form/data[1]/@k').Getstringval()  k
From Mytable t