SQL Server Performance

XML EXPLICIT with text column

Discussion in 'SQL Server 2005 General Developer Questions' started by stevemeyns, Jan 31, 2007.

  1. stevemeyns New Member

    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 />
  2. DilliGrg Member

Share This Page