2

I have a table called parts with an XMLType column called RunList_XML

The XML has several Unit tags example <Item> <Unit>420</Unit> </Item> <Item> <Unit>10</Unit> </Item> <Item> <Unit>0</Unit> </Item> I want to query to get back a list of all parts that have a Unit of 420 I cant seem to figure out how to get this using = 420 or = '420'

the below gives back false positives

select * from Parts P
   WHERE P.RunList_XML.extract('ArrayOfItem// /Unit/text()').getStringVal() like  '%420'
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
China Syndrome
  • 953
  • 12
  • 24

1 Answers1

0

Try this using existsnode:

select * from Parts P
   WHERE existsnode(RunList_XML, 'ArrayOfItem//Item[Unit="420"]') = 1

existsnode returns 1 if node is found and 0 if not.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76