Friday, March 6, 2015

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

Hello ,


i have below data.



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_xml]
)
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>')

select * from #students

if all subjects- marks in s_m_xml marks > 80 then i select the row as promoted else as demoted. the exact output should look like below.




student_id [status]
---------------------------
101 promoted
102 demoted

what is the best way to achieve this using t-sql. Thanks in advance for any suggestions.


No comments:

Post a Comment