condtion based xml for each row of the table t-sql

You dont need to parse out the values from xml for this

You can do the check inline

see illustration below

drop table #students
create table #students
(
        id int identity(1,1) primary key,
        student_id int not null, 
        [s_m_cml] xml
)
 insert into #students
 (
        student_id,
        [s_m_cml]
 )
 values
 (
        101,
        '<submarks><submark><subject>Arts</subject><marks>85</marks></submark><submark><subject>Science</subject><marks>95</marks></submark><submark><subject>maths</subject><marks>100</marks></submark></submarks>'
 ),(102,'<submarks><submark><subject>Arts</subject><marks>50</marks></submark><submark><subject>Science</subject><marks>75</marks></submark><submark><subject>maths</subject><marks>85</marks></submark></submarks>')
 ,(104,'<submarks><submark><subject>Arts</subject><marks>92</marks></submark><submark><subject>Science</subject><marks>88</marks></submark><submark><subject>maths</subject><marks>98</marks></submark></submarks>')
 
 select *,
 case when [s_m_cml].exist('/submarks/submark[data(marks) < 80]')=1 then 'demoted' else 'promoted' end as status
  from #students

---

Please Mark This As Answer if it solved your issue

Please Vote This As Helpful if it helps to solve your issue

Visakh

----------------------------

My Wiki User Page

My MSDN Page

My Personal Blog

My Facebook Page