Trying to generate XML result with Header, 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:
------------
------------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'
SELECT 1 AS Tag,
0 AS Parent,
NULL AS [PromotionRuleSet!1!PromotionRuleSet],
NULL AS [RuleSetText!2!!CDATA]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL AS [PromotionRuleSet!1!PromotionRuleSet],
PromotionRuleSetText AS [RuleSetText!2!!CDATA]
FROM [PromotionRuleSet]
WHERE PromotionRuleSetToken = @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..
-
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...
-
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.
-
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
-
If you're going to use SSIS I answered a similar question here http://qa.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.
-
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply