SQL Server Performance

Trying to generate full XML result with CDATA

Discussion in 'SQL Server 2005 General Developer Questions' started by SQL_Guess, Aug 21, 2006.

  1. SQL_Guess New Member

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

    Hi,

    I think you need to add FOR XML EXPLICIT,ROOT [:I].

    ________________________________________________
    Be great in act, as you have been in thought.
  3. SQL_Guess New Member

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

Share This Page