Trying to generate XML result with Header, CDATA

Question

Post reply

Trying to generate XML result with Header, CDATA

Regan Galbraith

SSCertifiable

Points: 5227
More actions
August 21, 2006 at 6:30 am

#173690

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:
------------------------
------------------------
Now, from what I've read so far, 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?
...GRRR - forums destroying my attempt to show the XML... I'll try in a next post..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 1

XML required:

?xml version="1.0" encoding="utf-16" ?

PromotionRuleSet

RuleSetText ![CDATA[Rule 1]] /RuleSetText

/PromotionRuleSet

--I'm afraid adding the GREATER THAN and LESS THAN tags, amkes the text invisible!

What I get atm:

PromotionRuleSet

RuleSetText ![CDATA[Rule 1]] /RuleSetText

/PromotionRuleSet

--again - add the GT's and LT's.

I need to find a way of adding

?xml version="1.0" encoding="utf-16" ?

while generating CDATA section...

DanKennedy Hall of Fame Points: 3736 More actions · Answer 2

How are you persisting the generated xml ? I've never seen SQL Server generate the xml declaration and there's no way I can see of inserting it into the stream. If you're using VBScript within a DTS package or an equivalent then you can script the inclusion of the declaration there instead.

If you need an example let me know.

Sorry I can't be more help.

Dan
www.firstcs.co.uk

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 3

Hi Noggin,

thanks for the response.

I'm currently look at a stored procedure that I had hoped would be able to include the XML header as part of the XML result returned. There are ways to 'hack' this by appending, for example, the header as a string to a string conversion of the XML, and then casting the resulting string as XML, and assigning it to the variable.... I know it ugly - I did say it was a hack (maybe klidge would be a more appropriate label).

However, I seem to be having a strange problem trying to use even that soultion when using XML explicit to create my XML with the CDATA scetion.

In an ideal world, I was hoping that somone with a bit more experience was going to tell me about a parametre I hadn't found that would include a header, something like:

FOR XML EXPLICIT, HEADER ''

but it would seem there is no such thing...

I may end up having to do this in the SSIS package (we are using sql 2005 []) - I was thinking of creating the target file, then using a .Net script component to edit the file and include the header, since I don't know of a way to do this before writing the file (open to suggestions though).

Well, thanks for the response... let me know if you have any comments/suggestions

DanKennedy Hall of Fame Points: 3736 More actions · Answer 4

If you're going to use SSIS I answered a similar question here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=291798

Instead of using a StreamWriter you could create an XMLDocument or XMLTextWriter which will enable you to easily save the file with the required encoding and declaration.

Dan
www.firstcs.co.uk

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 5

great, thanks Noggin - I'm going to play around with that. Since I'm a virtual virgin with .Net, Weould it be alright if I ran a question ro 2 past you, in this thread, as I play around with this?

TIA