CODEWITHSUNDEEP
sqlxmloracle-database
Andrea Girardi
Andrea Girardi

Reputation: 4437

Parse XML string stored on Oracle Table

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

Upvotes: 5

Views: 18120

Answers (1)

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

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

Upvotes: 6

Related Questions