SQL Server Performance Forum – Threads Archive
XML EXPLICIT with text column
Hi<br />I have to create an XML file based on a SQL SERVER 2005 table.<br />Everything works fine:<br />SELECT 1 as Tag,<br /> NULL as Parent,<br /> Ex_Id as [Exam!1!Ex_Id],<br /> NULL as [Ex_Title!2!!cdata]<br />FROM Exams<br />WHERE ex_State = ‘P'<br />UNION ALL<br />SELECT 2 as Tag,<br /> 1 as Parent,<br /> Ex_Id,<br /> ex_Title<br />FROM Exams<br />WHERE ex_State = ‘P'<br />order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata]<br />FOR XML EXPLICIT, ROOT(‘Exams’)<br /><br />BUT when i add another column, a text column, I get into trouble:<br /> SELECT 1 as Tag,<br /> NULL as Parent,<br /> Ex_Id as [Exam!1!Ex_Id],<br /> NULL as [Ex_Title!2!!cdata],<br />NULL as [Ex_Situation!3!!cdata]<br />FROM Exams<br />WHERE ex_State = ‘P'<br />UNION ALL<br />SELECT 2 as Tag,<br /> 1 as Parent,<br /> Ex_Id,<br /> ex_Title,<br />ex_Situation<br />FROM Exams<br />WHERE ex_State = ‘P'<br />order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata]<br />FOR XML EXPLICIT, ROOT(‘Exams’)<br /><br />The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosure<br />Please help me….i’m desperate <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /><br />
Should you consider using xml path instead? Check these sites if it helps:
http://builder.com.com/5100-6388_14-6140404.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql25xmlbp.asp
Thanks, Name
———
Dilli Grg (1 row(s) affected)
]]>