5

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

Improve this question
1
  • +1, looking for exactly the same thing Commented Feb 15, 2012 at 17:28

1 Answer 1

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I think xmlcol need to be an Xml datatype because when I try to execute the SQL Query it returns ORA-00932: inconsistent datatypes: expected - got -
Ah sorry I was under the impression the column was XMLTYPE. If you've got a VARCHAR2 / string you can use the XMLTYPE() function. So your query becomes SELECT extractvalue(xmltype(xmlcol), 'xpath') FROM yourtable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.