时间戳

首页 > 解决方案 > 选择包含 XML 列中所有值的混合类型列？

sql-server - 选择包含 XML 列中所有值的混合类型列？

问题描述

我有一个混合列类型（int, varchar, & xml）的表。列varchar具有 XML 格式的数据，并且xml列具有大型数据节点。我正在尝试选择varchar包含 XML 的所有列，以使用 SQL 代理作业步骤创建 XML 输出文件。我遇到的问题是定义为 XML 的列在文件中填充了很多空格，并且正在截断该列中的数据。表如下：

CREATE TABLE [dbo].[MixedTYPEXML](
    [col1] [varchar](4) NOT NULL,
    [col2] [int] NOT NULL,
    [xmlinText1] [varchar](190) NOT NULL,
    [JOBP] [xml] NULL
) 
GO

INSERT INTO [dbo].[MixedTypeXML]
           ([col1]
           ,[col2]
           ,[xmlinText1]
           ,[JOBP]
           )
     VALUES
           ('Prod'
           ,'35490'
           ,'<JOBP AllowExternal="1" name="JOBP.EXTRACTS_PHP_SSIS_POST_CORE#35490">'
           ,'<PreCon><conditions id="CONDITIONS" /></PreCon>'
)
GO

我的选择语句是

SELECT col2, xmlinText1, JOBP from dbo.MixedTypeXML

预期结果如下，但实际结果将是数千条记录。我只用一个小样本数据示例提取一条记录。

35490 <JOBP AllowExternal="1" name="JOBP.EXTRACTS_PHP_SSIS_POST_CORE#35490"> <PreCon><conditions id="CONDITIONS" /></PreCon>

这只是我要提取的列的一个子集。我基本上需要提取表中的所有列，包括 XML 定义的列，以将结果发送到本地驱动器上的输出表，而无需填充空格或截断数据。输出文件将是 XML 格式的文件。JOBP 列示例数据只是一个片段。实际数据包含几百行xml节点。

我查看了很多关于如何提取节点和值的帖子，但我并没有尝试提取特定数据。我希望整个表创建一个 XML 输出文件。任何想法如何做到这一点？

标签： sql-serverxmltsql

解决方案

根据所需的输出，整个输出文件应为 XML 格式。它不能是与 XML 元素混合的部分 *.csv 文件。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    [col1] VARCHAR(4) NOT NULL,
    [col2] INT NOT NULL,
    [xmlinText] VARCHAR(190) NOT NULL,
    [JOBP] XML NULL
);

INSERT INTO @tbl ([col1] ,[col2] ,[xmlinText] ,[JOBP])
VALUES
(
    'Prod'
    ,35490
    ,'<JOBP AllowExternal="1" name="JOBP.EXTRACTS_PHP_SSIS_POST_CORE#35490"/>'
    ,'<PreCon><conditions id="CONDITIONS" /></PreCon>'
)
-- DDL and sample data population, end

SELECT col1, col2
    , TRY_CAST([xmlinText] AS XML) AS [xmlinText]
    , JOBP
FROM @tbl
FOR XML PATH('row'), TYPE, ROOT('root');

输出

<root>
  <row>
    <col1>Prod</col1>
    <col2>35490</col2>
    <xmlinText>
      <JOBP AllowExternal="1" name="JOBP.EXTRACTS_PHP_SSIS_POST_CORE#35490" />
    </xmlinText>
    <JOBP>
      <PreCon>
        <conditions id="CONDITIONS" />
      </PreCon>
    </JOBP>
  </row>
</root>

推荐阅读