1 person found this helpful
As this question has nothing to do with the XML DB, you have lowered your chance of getting the answer you seek.
I think you might be looking for
Without knowing your version, or apparently having an index setup like you do what about something like
SELECT extractValue( XmlType(attributes), '/attrs/attr[@name="ESB_Availability_Status"]/string[text()="D"]' ) AS ESB_Availability_Status FROM MyTable
which does return empty rows if the condition is not meet or
SELECT * FROM (SELECT extractValue( XmlType(attributes), '/attrs/attr[@name="ESB_Availability_Status"]/string' ) AS ESB_Availability_Status FROM MyTable) WHERE ESB_Availability_Status = 'D';
Of course there are also XMLTable/XQuery based approaches as well if you so desire.
1) I shall post in the suggested forum.
2) I tried the variations suggested and they work as you mention and they return the D columns. My problem is that a java library is already coded to something like the original SQL I gave. The primary question is that how and why the query can find one data and not the other. If it can find P it can surely find D in the text node of the <string>.
For other readers the select * approach with an inner query work, but in my case with large amounts of data, there is an immediate performance hit and the result slows down by 3 times.
Thanks again for responding.