SQL Server XML output with CDATA

Question

Is there a way to have an SQL Server XML return use CDATA? I have XML being returned by SQL Server like this:

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

When I am required to have this:

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

Seems like an odd requirement: they should both come to the same thing in any standards-compliant xml parser. But then, customers will be customers. — Joel Coehoorn, Sep 15 '09 at 20:10
Unfortunately, post process is not an option, as SQL has already taken column data (Texas A & M) and escaped it for XML (Texas A & M) direct. Something that CDATA allows. The client doesn't want to parse the XML, then search and rewrite values. And I've quickly discovered that returning XML, rather than taking a query return and building the XML string by hand, is the definite preference. — Steve -Cutter- Blades, Sep 15 '09 at 20:24

score 16 · Accepted Answer · edited Apr 22 '15 at 17:12

Look at the options of FOR XML EXPLICIT (parameter Directive). It gives the greater degree of control and you can also specify CDATA. Here is a good tutorial.

And the code addapted from that 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

Beware, though, that if you insert this output into a T-SQL XML variable or an XML field, the CDATA will be converted back to the ampersand-escaped format, and that cannot be turned off. http://stackoverflow.com/a/9133622/864696 — Ross Presser, May 28 '15 at 16:13

rasx · Answer 2 · 2009-11-11T01:58:59.090

1

Here is an example of my way of getting 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 the relevant detail from my weasel-like, CDATA-handling function:

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

edited Nov 11 '09 at 01:58

answered Sep 16 '09 at 04:13

rasx

5,288
2
45
60

This will fail if any of your data, eg. Title, contains the string `]]>`. – Greg Hewgill Sep 16 '09 at 07:58
Ouch! Right! I am going to have to write a function devoted exclusively to CDATA... should have it by end of day... – rasx Sep 16 '09 at 18:20
Wait a sec' Greg, I just tried this in Management Studio: SELECT CAST('<![CDATA[will ]] this fail?]]>' AS xml) Have you tried this? What happens? – rasx Sep 16 '09 at 21:21
Nevertheless, my answer does not help the question. Now I see that that Steve Cutter is looking for CDATA markup to be preserved in output. – rasx Sep 16 '09 at 21:24
1

Try `<![CDATA[will ]]> this fail?]]>`. – Greg Hewgill Sep 17 '09 at 07:54
@Greg you are right! This is the error: `no ']]>' in element content`. So I am going to have to write that function. – rasx Nov 10 '09 at 23:06
Actually, I better just frickin' learn FOR EXPLICIT syntax---just like I have to learn explicit JOIN syntax. – rasx Nov 10 '09 at 23:11
And developers (myself included) should stop finding them self unique and reinvent the wheel every time, we think no one else ever had the same problem. – Christian Aug 06 '19 at 12:04

score 0 · Answer 3 · edited May 23 '17 at 12:13

As Joel mentioned in the comment above, those two forms should mean exactly the same thing. However, if the CDATA form is really required, then you could write a post-processor that takes the first form as input and outputs the second form using CDATA.

When doing this, a post-processor would unencode the XML-escaped data in the first form, and re-encode it using a CDATA-compatible method. See the question Is there a way to escape a CDATA end token in xml? for considerations regarding CDATA escaping.

SQL Server XML output with CDATA

3 Answers3

Linked