SQL Server XML output with CDATA

Question

Is there a way to get SQL Server XML back to use CDATA? I am returning XML to SQL Server as follows:

<locations> <site id="124"> <sitename>Texas A &amp; M</sitename> </site> </locations> 

When I need to do this:

 <locations> <site id="124"> <sitename><![CDATA[Texas A & M]]></sitename> </site> </locations> 

Answer 1

See the FOR XML EXPLICIT (parameter directive) options. This gives you more control, and you can also specify CDATA. Here is a good tutorial .

And the code added to this tutorial:

 declare @agent table ( AgentID int, Fname varchar(5), SSN varchar(11) ) insert into @agent select 1, 'Vimal', '123-23-4521' union all select 2, 'Jacob', '321-52-4562' union all select 3, 'Tom', '252-52-4563' SELECT 1 AS Tag, NULL AS Parent, NULL AS 'Agents!1!', NULL AS 'Agent!2!AgentID', NULL AS 'Agent!2!Fname!Element', NULL AS 'Agent!2!SSN!cdata' UNION ALL SELECT 2 AS Tag, 1 AS Parent, NULL, AgentID, Fname, SSN FROM @agent FOR XML EXPLICIT 

Answer 2

As Joel mentioned in the comment above, these two forms must mean the same thing. However, if you really need a CDATA form, you can write a post processor that takes the first form as input and outputs the second form using CDATA.

In this case, the postprocessor will uninstall the data saved using XML in the first form and transcode it using the CDATA-compatible method. See Question Is there a way to avoid inhibition of the end of CDATA in xml? for considerations regarding the shielding of CDATA.

Answer 3

Here is an example of my way to get CDATA:

 DECLARE @GlobalDateFormat varchar(32) SET @GlobalDateFormat = 'MM/dd/yyyy hh:mm tt' DECLARE @xml XML SET @xml = ( SELECT SegmentId = ISNULL(SegmentId,0) , DocumentId = ISNULL(DocumentId,0) , Title = CAST(Core.dbo.fCharFormat('xmlCDATA',Title,DEFAULT,'') AS xml) , DocumentShortName = CAST(Core.dbo.fCharFormat('xmlCDATA',DocumentShortName,DEFAULT,'') AS xml) , [FileName] = CAST(Core.dbo.fCharFormat('xmlCDATA',[FileName],DEFAULT,'') AS xml) , [Path] = CAST(Core.dbo.fCharFormat('xmlCDATA',[Path],DEFAULT,'') AS xml) , CreateDate = ISNULL(Core.dbo.fDateFormat(@GlobalDateFormat,CreateDate),Core.dbo.fDateFormat(@GlobalDateFormat,GETDATE())) , ModificationDate = ISNULL(Core.dbo.fDateFormat(@GlobalDateFormat,ModificationDate),Core.dbo.fDateFormat(@GlobalDateFormat,GETDATE())) , TemplateId = ISNULL(CAST(TemplateId AS varchar(16)),'') , IsRoot = ISNULL(IsRoot,0) , IsActive = ISNULL(IsActive,0) , SortOrdinal = ISNULL(CAST(SortOrdinal AS varchar(16)),'') , ClientId = ISNULL(ClientId,'') , Tag = CAST(Core.dbo.fCharFormat('xmlCDATA',Tag,DEFAULT,'') AS xml) FROM Document WHERE DocumentId = 9073 FOR XML AUTO, ELEMENTS ) SELECT @xml 

Here is an important detail from my laser related function, CDATA:

 IF @cmdName = 'xmlCDATA' BEGIN IF @chars IS NULL BEGIN SET @charsOut = @charsDefault END ELSE BEGIN SET @chars = REPLACE(@chars,'<![CDATA[','') SET @chars = REPLACE(@chars,']]>','') SET @charsOut = '<![CDATA[' + @chars + ']]>' END END