My xml:
declare @x xml='<Detail>
<ROll ID="1">
<Exams>
<Examdetails date="2017-04-02 13:30:00">
<Exam name="ECO" Total="100">150</Exam>
<Exam name="BIO" Total="150">50</Exam>
<Exam name="MATH" Total="200">28</Exam>
</Examdetails>
<Examdetails date="2017-04-02 14:30:00">
<Exam name="ENGLISH" Total="100">150</Exam>
<Exam name="BIO" Total="200">50</Exam>
<Exam name="ZIO" Total="250">28</Exam>
</Examdetails>
</Exams>
</ROll>
<ROll ID="2">
<Exams>
<Examdetails date="2017-05-02 13:30:00">
<Exam name="HIS" Total="100">150</Exam>
<Exam name="BIO" Total="200">50</Exam>
<Exam name="THI" Total="200">89</Exam>
</Examdetails>
</Exams>
</ROll>
</Detail>'
I want to Segregate my xml based on ROLL ID's while i tried with the below query referred from here
SELECT STUFF(
(
SELECT '!' + STUFF(p.query(N'for $n in .//*
return <a>{concat("$",($n/text())[1])}</a>'
).value(N'.',N'nvarchar(max)'),1,1,'')
FROM p.nodes(N'Exams') AS A(p)
FOR XML PATH(''),TYPE).value(N'.',N'nvarchar(max)'),1,1,'')
FROM @x.nodes(N'Detail/ROll') AS A(p);
I get the result as
But i want to query it back as
2017-04-02 13:30:00$ECO$100$150!2017-04-02 13:30:00$BIO$150$50!2017-04-02 13:30:00$MATH$200$28!2017-04-02 14:30:00$ENGLISH$100$150!2017-04-02 14:30:00$BIO$200$50!2017-04-02 14:30:00$ZIO$250$28
2017-05-02 13:30:00$HIS$100$150!2017-05-02 13:30:00$BIO$200$50!2017-05-02 13:30:00$THI$200$89
Kindly help me solve this complexity
Thanks in advance ,Jayendran