DevOps, CI/CD and Automation

Hello,

I have following XML with me:

<?xml version="1.0" encoding="utf-8" ?>
<Rowsets DateCreated="2010-11-30T11:12:10" EndDate="2010-06-05T16:52:23" StartDate="2010-06-05T16:52:23" Version="12.0.10 Build(18)">
<Rowset>
<Columns>
<Column Description="Material Number" MaxRange="1" MinRange="0" Name="MAT" SQLDataType="1" SourceColumn="MAT"/>
<Column Description="Plant" MaxRange="1" MinRange="0" Name="WER" SQLDataType="1" SourceColumn="WER"/>
<Column Description="Storage Location" MaxRange="1" MinRange="0" Name="LGO" SQLDataType="1" SourceColumn="LGO"/>
<Column Description="" MaxRange="1" MinRange="0" Name="TANK" SQLDataType="1" SourceColumn="TANK"/>
<Column Description="Batch Number" MaxRange="1" MinRange="0" Name="BATCH" SQLDataType="1" SourceColumn="BATCH"/>
<Column Description="Valuated Unrestricted-Use Stock" MaxRange="1" MinRange="0" Name="CLABS" SQLDataType="8" SourceColumn="CLABS"/>
<Column Description="Quality Stock" MaxRange="1" MinRange="0" Name="CINSM" SQLDataType="8" SourceColumn="CINSM"/>
</Columns>
<Row>
<MAT>111</MAT>
<WER>US</WER>
<LGO>T1</LGO>
<TANK>T100</TANK>
<BATCH>2020</BATCH>
<CLABS>0</CLABS>
<CINSM>19.000</CINSM>
</Row>
<Row>
<MAT>222</MAT>
<WER>US</WER>
<LGO>T1</LGO>
<TANK>T100</TANK>
<BATCH>1010</BATCH>
<CLABS>1000000.000</CLABS>
<CINSM>0</CINSM>
</Row>
<Row>
<MAT>333</MAT>
<WER>US</WER>
<LGO>T1</LGO>
<TANK>T100</TANK>
<BATCH>1010</BATCH>
<CLABS>1000000.000</CLABS>
<CINSM>0</CINSM>
</Row>
<Row>
<MAT>444</MAT>
<WER>US</WER>
<LGO>T1</LGO>
<TANK>T100</TANK>
<BATCH>1010</BATCH>
<CLABS>1000000.000</CLABS>
<CINSM>0</CINSM>
</Row>
<Row>
<MAT>555</MAT>
<WER>US</WER>
<LGO>T1</LGO>
<TANK>T100</TANK>
<BATCH>1010</BATCH>
<CLABS>1000000.000</CLABS>
<CINSM>0</CINSM>
</Row>
<Row>
<MAT>666</MAT>
<WER>US</WER>
<LGO>T1</LGO>
<TANK>T100</TANK>
<BATCH>1010</BATCH>
<CLABS>1000000.000</CLABS>
<CINSM>0</CINSM>
</Row>
</Rowset>
</Rowsets>

Now, I want my select statement to return following based on a count of XML node:

select
If count(/Rowsets/Rowset/Row[CLABS > 0]/BATCH) > 1 then 'Mixing'
else if count(/Rowsets/Rowset/Row[MAT = "Parameter Value"]/MAT) = 0 then 'None'
else 'Other'
from Tablename TT where TANK = something and Plant = something


How can I achive this using Oracle query? I can do it using SQL server but not getting the correct syntax for Oracle :(