Create XML File IN SQL Server 2012

Question

I have a store procedure that is supposed to generate an XML file based on data available in a table. If there are data in all the fields, it work fine. However, if there are no date in one or more of the fields, it does not create the xml. It return null instead. I started debugging it and noticed that @part1 has the correct value, part2 has the correct value, and part3 has the correct value. Once I concatenate them, I noticed part1 is now null. What am I missing here?

select @part1 = '<Students>';

            @part2 ='<NumberOFCourse>'+isnull(convert(varchar,(select NumberOFCourse from Courses)), 0)+'</NumberOFCourse>' ;
                select @part3 = '<NumberOFSemesterCredit>'+isnull(convert(varchar,(select NumberOFSemesterCredit from Courses)), 0)'</NumberOFSemesterCredit>' +
                '</Students>';
                select @part1 += @part2 + @part3

Mikael Eriksson · Accepted Answer · 2014-07-16 06:42:57Z

1

You have not posted the code you are actually using because there are several typos in there and when those are fixed it does not show the behavior that you describe so I can't tell you why your code does not work for you.

Instead of using string concatenation to build the XML you could use PATH Mode with FOR XML something like this.

select C.NumberOFCourse,
       C.NumberOFSemesterCredit
from Courses as C
for xml path('Students')

answered Jul 16, 2014 at 6:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create XML File IN SQL Server 2012

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related