title: Extracting value of xml tag in PostgreSQL
body: <p>Below is the column response from my Postgres table.  I want to extract the status from all the rows in my Postgres database. The status could be of varying sizes like <code>SUCCESS</code> as well so I do not want to use the substring function. Is there a way to do it?</p>
  
  <pre><code>&lt;?xml version="1.0" ?&gt;&lt;response&gt;&lt;status&gt;ERROR_MISSING_DATA&lt;/status&gt;&lt;responseType&gt;COUNTRY_MISSING&lt;/responseType&gt;&lt;country_info&gt;USA&lt;/country_info&gt;&lt;phone_country_code&gt;1234&lt;/phone_country_code&gt;&lt;/response&gt;
  </code></pre>
  
  <p>so my table structure is like this</p>
  
  <pre><code>   Column    |            Type             |                        Modifiers                         
  
  -------------+-----------------------------+----------------------------------------------------------
  
   id          | bigint                      | not null default nextval('events_id_seq'::regclass)
   hostname    | text                        | not null
   time        | timestamp without time zone | not null
   trn_type    | text                        | 
   db_ret_code | text                        | 
   request     | text                        | 
   response    | text                        | 
   wait_time   | text                        | 
  </code></pre>
  
  <p>And I want to extract status from each and every request.  How do i do this?</p>
  
  <p>Below is a sample row.  And assume the table name abc_events</p>
  
  <pre><code>id          | 1870667
  hostname    | abcd.local
  time        | 2013-04-16 00:00:23.861
  trn_type    | A
  request     | &lt;?xml version="1.0" ?&gt;&lt;response&gt;&lt;status&gt;ERROR_MISSING_DATA&lt;/status&gt;&lt;responseType&gt;COUNTRY_MISSING&lt;/responseType&gt;&lt;country_info&gt;USA&lt;/country_info&gt;&lt;phone_country_code&gt;1234&lt;/phone_country_code&gt;&lt;/response&gt;
  response    | &lt;?xml version="1.0" ?&gt;&lt;response&gt;&lt;status&gt;ERROR_MISSING_DATA&lt;/status&gt;&lt;responseType&gt;COUNTRY_MISSING&lt;/responseType&gt;&lt;country_info&gt;USA&lt;/country_info&gt;&lt;phone_country_code&gt;1234&lt;/phone_country_code&gt;&lt;/response&gt;
  </code></pre>