SQL Server Performance Forum – Threads Archive

Trying to generate full XML result with CDATA

Hi all, I’m using SQL 2005, sp0 and sp1 (that is, I’ve tested against both). I’ve trying to find a way to have my SP return the following: <?xml version="1.0" encoding="utf-16" ?> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />romotionRuleSet> <RuleSetText><![CDATA[Rule 1]]></RuleSetText> </PromotionRuleSet> Now, from what I’ve read so far, getting CDATA is best (only?) done in XML EXPLICIT. So I have to following: /****** Object: Table [PromotionRuleSet] Script Date: 08/21/2006 12:02:23 ******/ IF OBJECT_ID (‘[PromotionRuleSet]’) IS NOT NULL DROP TABLE [PromotionRuleSet] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [PromotionRuleSet]( [UID] [int] IDENTITY(1,1) NOT NULL, [PromotionRuleSetStatusType_Uno] [smallint] NOT NULL, [PromotionRuleSetText] [nvarchar](max) NOT NULL, [PromotionRuleSetToken] [nchar](16) NOT NULL, [CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_CreatedOn_PromotionRuleSet] DEFAULT (getdate()), [CreatedBy] [sysname] NOT NULL CONSTRAINT [DF_CreatedBy_PromotionRuleSet] DEFAULT (user_name()), [LastChangedOn] [datetime] NOT NULL CONSTRAINT [DF_LastChangedOn_PromotionRuleSet] DEFAULT (getdate()), CONSTRAINT [PK_PromotionRuleSet] PRIMARY KEY CLUSTERED ( [UID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO insert INTO [PromotionRuleSet] ([PromotionRuleSetStatusType_Uno] ,[PromotionRuleSetText] ,[PromotionRuleSetToken]) SELECT -1,’Rule 1′,’20060627_0000001′ UNION all SELECT -1,’Rule 2′,’20060627_0000002′ UNION all SELECT -1,’Rule 3′,’20060627_0000003′ UNION all SELECT 2,’Rule 4′,’20060627_0000004′ DECLARE @RuleSetToken nCHAR(16) SET @RuleSetToken = ‘20060627_0000001′ SELECT1AS Tag, 0AS Parent, NULLAS [PromotionRuleSet!1!PromotionRuleSet], NULLAS [RuleSetText!2!!CDATA] UNION ALL SELECT2AS Tag, 1AS Parent, NULLAS [PromotionRuleSet!1!PromotionRuleSet], PromotionRuleSetTextAS [RuleSetText!2!!CDATA] FROM[PromotionRuleSet] WHEREPromotionRuleSetToken = @RuleSetToken ORDER BY Tag FOR XML EXPLICIT IF OBJECT_ID (‘[PromotionRuleSet]’) IS NOT NULL DROP TABLE [PromotionRuleSet] This generates the nodes, with the CDATA, but I’ve not found a way to add the xml header. anyone? Panic, Chaos, Disorder … my work here is done –unknown

Hi, I think you need to add FOR XML EXPLICIT,ROOT [:I]. ________________________________________________
Be great in act, as you have been in thought.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by waqar Hi, I think you need to add FOR XML EXPLICIT,ROOT [:I]. ________________________________________________ Be great in act, as you have been in thought. <hr height="1" noshade id="quote"></blockquote id="quote"> Thanks for the post, Waqar. ROOT just add’s my XML to a ROOT Node – not what I need. To put it another way, PromotionRuleSet is MY root. Adding ,ROOT generates: <pre id="code"> <root> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />romotionRuleSet> <RuleSetText><![CDATA[Rule 1]]></RuleSetText> </PromotionRuleSet> </root> </pre id="code"> whereas I am trying to get to: <pre id="code"> <?xml version="1.0" encoding="utf-16" ?> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />romotionRuleSet> <RuleSetText><![CDATA[Rule 1]]></RuleSetText> </PromotionRuleSet> </pre id="code"> Panic, Chaos, Disorder … my work here is done –unknown

]]>