Parse XML string stored on Oracle Table

sql xml oracle

15,803

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

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

Adapt the XPath to suit your needs.

See ExtractValue documentation or research other Oracle XML functions.

I should probably note that 11g and later, extractvalue is deprecated and you should use XMLQuery

15,803

Author by

Andrea Girardi

For the glory, for the money and for the fun. Mostly for the money! Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. IT errors are like snowflakes They didn't drop the ball. They dropped the ball, kicked the coach in the nuts and took a crap in the quarterback's mouth If they see a scrambled mass of code that looks like it was written by a bevy of drunken sailors, then they are likely to conclude that the same inattention to detail pervades every other aspect of the project http://www.andreagirardi.it https://twitter.com/bazingaaaa

Updated on June 26, 2022

Comments

Andrea Girardi almost 2 years
I've a XML string like that stored on my DB:
```
<?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 Zuelling Korea using XMLQuery or SQL? I can't create a temporary table because it's a validate environment so I can only read that value. I know is possible using reg exp, but, if possible I try to use XML.

thanks,
Andrea