Search a string in a table column which is an XML…..
Oh man! I have to search for a particular string inside an XML column of a table.
Found a reference in a site. Modified a little bit. If you find it easy use it, wait lets make this some bit complex — the string should have special character ‘ inside it.
Table Name: TABLE
Column Name : TABLE_XML
XML Structure:
TABLEDATA/FirstName
TABLEDATA/LastName
TABLEDATA/TABLE_ID
- I want to Query all the first Name and last name details, who has ‘ in their name. Eg: Jay O’Connell etc…
select def.* from TABLE,
XMLTABLE(‘$D/TABLEDATA‘ PASSING TABLE_XML as “D”
columns
firstname varchar(200) path ‘FirstName‘,
lastname varchar(200) path ‘LastName‘,
….) AS def where (def.firstname like ‘%”% or def.lastname like ‘%”%) with ur;
Result:
FirstName LastName
Jay O’Connell
R’Dia Lortez
That’s it…Any queries …posttttttttt please