Read XML child node attributes using SQL query

Question

I have one XML column (Criteria) in table (Qualifications) which contains different XML:

<training ID="173"><badge ID="10027" /><badge ID="10028" /></training>
<book Category="Hobbies And Interests" PropertyName="C#" CategoryID="44" />
<sport Category="Hobbies And Interests" PropertyName="Cricket" CategoryID="46" />
<education ID="450" School="Jai ambe vidyalaya"></education>

I want to read the "badge" node "ID" attributes for all nodes under the "training" node.

Can anyone help?

Answer 1

IDs of badge elements inside training only

select t.c.value('.', 'int') ID
from Qualifications q
    cross apply q.Criteria.nodes('//training[badge]/badge[@ID]/@ID') t(c)

IDs of badge elements anywhere (not only inside training)

select t.c.value('.', 'int') ID
from Qualifications q
    cross apply q.Criteria.nodes('//badge[@ID]/@ID') t(c)

If Criteria column is nvarchar type, you can cast to xml as:

select t.c.value('.', 'int') ID
from Qualifications q
    cross apply (select convert(xml, q.Criteria) xmlCriteria) a
    cross apply a.xmlCriteria.nodes('//training[badge]/badge[@ID]/@ID') t(c)