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?
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)
Try this sample, it should help (just replace @xml with your table/column name)
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
