Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 31st, 2007, 07:09 AM
Registered User
 
Join Date: Jan 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default XML EXPLICIT with text column

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 :(


 
Old February 7th, 2007, 01:09 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL XML Explicit (Output file required) Neal SQL Server 2005 0 August 4th, 2008 03:04 AM
FOR XML Explicit and ASP tonyellard SQL Server ASP 1 April 8th, 2005 10:41 AM
For XML Explicit bmains SQL Server 2000 5 March 8th, 2004 09:18 AM
SQL Server For XML Explicit tonyellard Classic ASP Databases 0 November 3rd, 2003 03:03 PM
CDATA Set with For XML Explicit Yehuda SQL Server 2000 0 August 13th, 2003 01:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.