Good day!

There is a BLOB containing approximately such data:

<Data> <Name>Data1</Name> <Value>Data1_1</Value> </Data> <Data> <Name>Data2</Name> <Value>Data2_2</Value> </Data>

The task is to extract only Data2_2 with the help of extractvalue () focusing on Data2 . How? Through xmlsequence () did not work. Please tell me where to dig, preferably with an example :)

Thank!

  • PS for Data2_2 values there are regulars ... - sys1n4

1 answer 1

If xml stores xml and has a root element (I substituted root), then you can use this approach

 WITH tmp AS ( SELECT '<Root> <Data> <Name>Data1</Name> <Value>Data1_1</Value> </Data> <Data> <Name>Data2</Name> <Value>Data2_2</Value> </Data> </Root>' x FROM dual ) SELECT extractvalue(VALUE(b),'Data/Name') name, extractvalue(VALUE(b),'Data/Value') value FROM tmp, TABLE(xmlsequence(extract(XMLTYPE(tmp.x),'Root/Data'))) b WHERE extractvalue(VALUE(b),'Data/Name') = 'Data2'
  • Huge thanks, thanks to this example, my hands were straightened relative to the xmlsequence (). - sys1n4