SQL : How to pass a value of an SQL column as a filter to an XML and fetch data from XML

Viewed 34

Question: I want to retrieve value at a certain tag which is related to max value of another tag in the same group in an XML. For example in below XML, I want to retrieve value of Tag2a for max value of Tag2b in entire XML.This needs to be retrieved using SQL.When tried with below null value is returned as OUTPUT.

What I am trying with :

select
    (SELECT xmP.peakKW FROM XMLTABLE('/xml'
                       passing xmltype('<xml>'||BO_DATA_AREA||'</xml>')
                       columns
                       peakT FLOAT path 'Tag1/Tag2[Tag2b = TEMP.peak]/Tag2a'
                       )xmT) AS OUTPUT 
FROM (SELECT xmP.peakKW as peak FROM XMLTABLE('/xml'
                       passing xmltype('<xml>'||BO_DATA_AREA||'</xml>')
                       columns
                       peakA FLOAT path 'max(Tag1/Tag2/Tag2b)'
                       )xmP)TEMP;

It'd be great if anyone could suggest on how to work this out.

XML Sample

<Tag1>
  <Tag2>
    <Tag2a>X</Tag2a>
    <Tag2b>Y</Tag2b>
    <Tag2c>Z</Tag2c>
  </Tag2>
  <Tag2>
    <Tag2a>X</Tag2a>
    <Tag2b>X</Tag2b>
    <Tag2c>X</Tag2c>
  </Tag2>
</Tag1>
0 Answers
Related