1 Reply Latest reply: Nov 20, 2012 4:56 PM by TrojanSpirit RSS

    Counting of XML nodes based on a condition

    TrojanSpirit
      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 :(
        • 1. Re: Counting of XML nodes based on a condition
          TrojanSpirit
          I got the answer on my own :)

          SELECT CASE WHEN count(RW."CountofBATCH") > 1 then 'Mixing'
          WHEN count(RW."CountofMAT") = 0 then 'None'
          ELSE 'Other'
          END
          FROM TABLENAME TT, XMLTable('/Rowsets/Rowset/Row' PASSING TT.XMLCOL
          COLUMNS
          "CountofBATCH" PATH '/Row[CLABS > 0]/BATCH',
          "CountofMAT" PATH '/Row[MAT = "[Parameter Value]"]/MAT'
          ) AS RW

          where

          some condition