将查询结果存储在 Xml 变量中时如何保留 CDATA 标记? [英] How can I retain CDATA tags when storing query results in an Xml variable?

问题描述

当我使用 For Explicit 在 Sql Server 2008 R2 中生成 Xml 时(因为我的消费者想要包含在 CDATA 中的元素之一)并将结果存储在一个 Xml 变量中，我想要包含在 CDATA 标签中的数据不再出现在CDATA 标签.如果我不将 For Xml Explicit 结果推送到 Xml 变量中，则保留 CDATA 标记.我正在使用 @Xml 变量作为来自 .Net 的 SqlParameter.

When I generate Xml in Sql Server 2008 R2 using For Explicit (because my consumer wants one of the elements wrapped in CDATA) and store the results in an Xml variable, the data I want wrapped in CDATA tags no longer appears wrapped in CDATA tags. If I don't push the For Xml Explicit results into an Xml variable then the CDATA tags are retained. I am using the @Xml variable as an SqlParameter from .Net.

在本例中，第一个选择(Select @Xml)没有将 Line2 包裹在 CDATA 标记中.但是第二个选择(用于填充@Xml 变量的相同查询)确实具有包装 Line2 列的 CDATA 标记.

In this example, the first select (Select @Xml) does not have Line2 wrapped in CDATA tags. But the second select (the same query used to populate the @Xml variable) does have the CDATA tags wrapping the Line2 column.

Declare @Xml Xml

Begin Try
    Drop Table #MyTempTable
End Try
Begin Catch
End Catch

Select
    'Record' As Record
    , 'Line1' As Line1
    , 'Line2' As Line2
Into
    #MyTempTable

Select @Xml =
(
    Select
        x.Tag
        , x.Parent
        , x.[Root!1]
        , x.[Record!2!Line1!Element]
        , x.[Record!2!Line2!cdata]
    From
        (
            Select
                1 As Tag, Null As Parent
                , Null As [Root!1]
                , Null As [Record!2!Line1!Element]
                , Null As [Record!2!Line2!cdata]
            From
                #MyTempTable
            Union
            Select
                2 As Tag, 1 As Parent
                , Null As [Root!1]
                , Line1 As [Record!2!Line1!Element]
                , Line2 As [Record!2!Line2!cdata]
            From
                #MyTempTable
        ) x
    For
        Xml Explicit
)

Select @Xml

    Select
        x.Tag
        , x.Parent
        , x.[Root!1]
        , x.[Record!2!Line1!Element]
        , x.[Record!2!Line2!cdata]
    From
        (
            Select
                1 As Tag, Null As Parent
                , Null As [Root!1]
                , Null As [Record!2!Line1!Element]
                , Null As [Record!2!Line2!cdata]
            From
                #MyTempTable
            Union
            Select
                2 As Tag, 1 As Parent
                , Null As [Root!1]
                , Line1 As [Record!2!Line1!Element]
                , Line2 As [Record!2!Line2!cdata]
            From
                #MyTempTable
        ) x
    For
        Xml Explicit

Begin Try
    Drop Table #MyTempTable
End Try
Begin Catch
End Catch

推荐答案

你不能.XML 数据类型不保留 CDATA 部分.

You can't. The XML data type does not preserve CDATA sections.

看看这里关于这个主题的讨论.

Have a look here for a discussion about the subject.

http:///social.msdn.microsoft.com/forums/en-US/sqlxml/thread/e22efff3-192e-468e-b173-ced52ada857f/

这篇关于将查询结果存储在 Xml 变量中时如何保留 CDATA 标记?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！