将查询结果存储在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.

在此示例中，第一个选择（选择@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 / zh-CN / sqlxml / thread / e22efff3-192e-468e-b173-ced52ada857f /

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