Reading attributes of XML node type using SQL query

Question

I have one XML column (criteria) in a table (Qualifications) that 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 attributes of the "icon" node "ID" for all nodes under the "learning" node.

Can anyone help?

Answer 1

IDs of badge elements inside training only

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

IDs of badge elements anywhere (not just inside training )

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

If the Criteria column is of type nvarchar , you can apply it to xml as:

 select tcvalue('.', '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) 

Answer 2

Try this sample, it should help (just replace @xml with the name of the table / column)

 DECLARE @xml XML SET @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>' SELECT data.col.value('(@ID)[1]', 'int') FROM @xml.nodes('(/training/badge)') AS data(col) 

Output:

 10027 10028