Hi
I have to create an XML file based on a SQL SERVER 2005 table.
Everything works fine:
SELECT 1 as Tag,
       NULL as Parent,
       Ex_Id as [Exam!1!Ex_Id],
       NULL as [Ex_Title!2!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       Ex_Id,
       ex_Title
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata]
FOR XML EXPLICIT, ROOT('Exams')

BUT when i add another column, a text column, I get into trouble:
 SELECT 1 as Tag,
       NULL as Parent,
       Ex_Id as [Exam!1!Ex_Id],
       NULL as [Ex_Title!2!!cdata],
        NULL as [Ex_Situation!3!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       Ex_Id,
       ex_Title,
        ex_Situation
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata]
FOR XML EXPLICIT, ROOT('Exams')

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
Please help me....i'm desperate :(

Hi,

In SQL Server 2005, a new FOR XML mode was added that is sooooo much easier that the EXPLICIT mode. The new mode is called the PATH mode, and provides a simpler way to mix elements and attributes as well as much simpler way to add nesting for complex queries.

I would recommend that you take a look at that, because it is MUCH simpler. If the PATH mode does not work for you, let me know and we'll address your issue using the EXPLICIT mode.


Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html