CODEWITHSUNDEEP
xmloracle-database
general46
general46

Reputation: 820

How to use the LIKE clause in an XML column in Oracle?

I have one column which contains XML data. The requirement is I need to have that column that has tag in XML (This tag is always present somewhere in the middle of the XML). And for that, I am using

WHERE XML_DATA LIKE '%DateofBirth%

Is there any other better way of doing this? Thanks for your help.

Upvotes: 0

Views: 709

Answers (1)

APC
APC

Reputation: 146239

If the column is defined as XMLType (and if not it probably should be) you can use existsnode():

WHERE EXISTSNODE(xml_data, '/DateofBirth') = 1;

Also, you should consider building an XML Index on it. It depends whether you're going to be doing enough of such searches to justify the cost of maintaining an index. Find out more

If the column is not defined as XMLType LIKE is the best you can do, unless you want to define an Oracle Text index.

APC sez: upon reflection this answer may not be useful to the Seeker. I have asked them for additional details. Depending on their response I may amend or delete this answer.

Upvotes: 1

Related Questions