mysql - Count by checking XML child tag -

i want count child elements based on if condition.

 declare @mainxml xml =  '<root>      <data>            <a>fail</a>      </data>      <data>            <a>pass</a>      </data>      <data>            <a>pass</a>      </data>    <root>' 

i want amount of "data" elements child element "a"'s value "fail". above count 1.

i storing count in sql column. how can this?

i don't know if there's more direct way, shred xml , count results, (in sql server)

declare @mainxml xml = '<root>     <data>           <a>fail</a>     </data>     <data>           <a>pass</a>     </data>     <data>           <a>pass</a>     </data>   </root>'  select count(1) @mainxml.nodes ('root/data') t(c) t.c.value('a[1]', 'char(4)') = 'fail'