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

Hi Chakradhar,



Stan210's solution works fine to achieve your requirement. For potential further requirement, you may need the below query.

;WITH cte AS(
SELECT student_id,
Person.Name.value('./subject[1]','varchar(99)') AS course,
Person.Name.value('./marks[1]','int') AS [marks]
FROM #students cross apply s_m_xml.nodes('/submarks/submark') as Person(Name)
)
SELECT student_id,[Arts],[Science],[Maths] FROM cte
PIVOT
(SUM(marks) FOR course IN ([Arts],[Science],[Maths])) pvtTbl

If you have any question, feel free to let me know.

---

Eric Zhang

TechNet Community Support