选择不带列别名换行的 XML 列

我有以下查询返回以下 XML 结果集。请注意查询 1 结果中的嵌套<Configuration>元素。

问题：

如果我添加一个 case 语句，它会从 xml 输出中删除列名。这给了我预期的结果。如何在没有 case 语句或嵌套选择的情况下执行此操作？
有关示例，请参阅查询 2 和查询 2 结果。

查询 1：

   SELECT
        (SELECT
             q.NotificationQueueId
            ,nc.Configuration -- Provides unexpected result -- Nested <Configuration><Configuration /></Configuration>
            ,q.UserAdded
            ,q.DateAdded
            ,q.UserEdited
            ,q.DateEdited
            ,q.IsActive
        FROM Queue q
            INNER JOIN NotificationConfigurations nc ON nc.NotificationConfigurationId = q.NotificationConfigurationId
        FOR XML RAW ('NotificationItem'),TYPE)
    FOR XML RAW ('NotificationItems'),TYPE

查询 1 结果：

<NotificationItems>
  <NotificationItem NotificationQueueId="1" UserAdded="someone" DateAdded="2016-02-29T13:26:11.110" IsActive="1">
    <Configuration>
      <Configuration>
        <Email AddressList="blah@blah.com" Subject="blah blah blah">       
        </Email>
      </Configuration>
    </Configuration>
  </NotificationItem>
</NotificationItems>

查询 2：

   SELECT
        (SELECT
             q.NotificationQueueId
            ,CASE WHEN 1 = 1 THEN nc.Configuration END -- Provides expected result
            ,(SELECT nc.Configuration) -- Provides expected result
            ,q.UserAdded
            ,q.DateAdded
            ,q.UserEdited
            ,q.DateEdited
            ,q.IsActive
        FROM Queue q
            INNER JOIN NotificationConfigurations nc ON nc.NotificationConfigurationId = q.NotificationConfigurationId
        FOR XML RAW ('NotificationItem'),TYPE)
    FOR XML RAW ('NotificationItems'),TYPE

查询 2 结果

<NotificationItems>
  <NotificationItem NotificationQueueId="1" UserAdded="someone" DateAdded="2016-02-29T13:26:11.110" IsActive="1">
    <Configuration>
      <Email AddressList="blah@blah.com" Subject="blah blah blah">       
      </Email>
    </Configuration>
  </NotificationItem>
</NotificationItems>

表定义：

CREATE TABLE [dbo].[NotificationConfigurations](
    [NotificationConfigurationId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Configuration] [xml] NOT NULL,
 )
CREATE TABLE [dbo].[Queue](
    [NotificationQueueId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [NotificationConfigurationId] [uniqueidentifier] NOT NULL,
    [UserAdded] [nvarchar](128) NOT NULL,
    [DateAdded] [datetime] NOT NULL,
    [UserEdited] [nvarchar](128) NULL,
    [DateEdited] [datetime] NULL,
    [IsActive] [bit] NOT NULL,
)

示例配置 XML：

<Configuration>
    <Email AddressList="blah@blah.com"
           Subject="blah blah blah">
    </Email>
</Configuration>

请看例子：

WITH t1 AS (
SELECT 1 AS ID UNION SELECT 2
), t2 AS (
SELECT 1 AS ID, CONVERT(XML, '
    <Configuration>
        <Email AddressList="blah@blah.com" Subject="blah blah blah">       
        </Email>
    </Configuration>
    ') As Configuration
)
SELECT (
    SELECT t1.ID,
        t2.Configuration,
        CASE WHEN 1 = 1 THEN t2.Configuration END,
        (SELECT nc.Configuration)
    FROM t1
    INNER JOIN t2 On t1.ID = t2.ID
    FOR XML RAW ('NotificationItem'),TYPE
) FOR XML RAW ('NotificationItems'),TYPE

您的Configuration字段已经是带有<Configuration />根标记的 XML。选中后，它将包装在字段名称后面的额外<Configuration />标记中。

您已经通过(SELECT nc.Configuration)获得了解决方案，该解决方案导致"匿名"字段"按原样"包含在结果XML中，没有字段名称包装标记。而且这个"嵌套查询"实际上并不花费任何费用。

更新：原因Configuration XML可以在JOIN之前显式"解包"，但这效率要低得多：

WITH t1 AS (
SELECT 1 AS ID UNION SELECT 2
), t2 AS (
SELECT 1 AS ID, CONVERT(XML, '
    <Configuration>
        <Email AddressList="blah@blah.com" Subject="blah blah blah">       
        </Email>
    </Configuration>
    ') As Configuration
)
SELECT (
    SELECT t1.ID,
        t2sub.Configuration
    FROM t1
    INNER JOIN (
        SELECT ID, sub.ConfigurationContents.query('.') AS Configuration
        FROM t2
        CROSS APPLY Configuration.nodes('/Configuration/child::node()') AS sub(ConfigurationContents) 
    ) t2sub ON t2sub.id = t1.id
    FOR XML RAW ('NotificationItem'),TYPE
) FOR XML RAW ('NotificationItems'),TYPE

相关内容

最新更新

热门标签：