Parse an XML string stored in an Oracle table

Question

I have an XML string similar to the one stored in my database:

<?xml version="1.0" encoding="utf-8"?>
<AddressMaintenance>
<Label Name="lblAddress11">Address Line 1</Label><TextBox Name="txtAddress11">Zuellig Korea</TextBox>
</AddressMaintenance>

Do you know if there is a way to extract the value of Zuelling Korea using XMLQuery or SQL? I cannot create a temporary table because it is a verification environment, so I can only read this value. I know that you can use reg exp, but if possible, I'm trying to use XML.

thanks
Andrea

Answer 1

If this is stored in XMLTYPE in a table, you can use Oracle XML functions to extract it using XPath:

SELECT
  extractvalue(xmlcol, '/*/TextBox[@Name=''txtAddress11'']') txtaddress
FROM yourtable

Adapt XPath to your needs.

ExtractValue Oracle XML.

---

, , , 11g extractvalue , XMLQuery