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>