使用 SSIS 将表数据的 XML 表示提取到文件中 [英] Using SSIS to extract a XML representation of table data to a file

问题描述

我正在尝试使用 SSIS 将查询结果集的 XML 表示提取到文本文件中.我的查询当前成功提取了我在 SSMS 中运行时所需的确切 XML 输出.我已经尝试了我能找到的所有技巧来使用 SSIS 包中的这个结果集来创建文件.

I'm trying to use SSIS to extract XML representation of a query result set to a text file. My query is currently successfully extracting the exact XML output I need when I run it in SSMS. I've tried every trick I can find to use this result set in a SSIS package to create a file.

使用数据流将 OLE 源移植到平面文件不起作用，因为 XML 查询的输出被视为 TEXT，而 SSIS 无法将 TEXT、NTEXT 或 IMAGE 推送到文件目标.

Using a dataflow to port a OLE Source to a Flat file doesn't work because the output of a XML query is treated as TEXT and SSIS can't push TEXT, NTEXT or IMAGE to a file destination.

我尝试然后执行 SQL 任务来填充用户变量，然后使用脚本任务(使用 C# 编写)将此用户变量的内容写入文件输出，但用户变量始终为空.我不知道，但我再次怀疑这是因为 XML 被视为 TEXT 或 IMAGE 而用户变量不处理这个.

I've tried to then Execute SQL Task to fill a user variable and then use a Script Task (written using C#) to write the contents of this user variable to a file output, but the user variable is always empty. I don't know, but I suspect this is, again, because the XML is treated as TEXT or IMAGE and the user variable doesn't handle this.

查询格式如下:选择 *从数据表WHERE dataTable.FIELD = '值'FOR XML AUTO, ROOT('RootVal')

The query is in this form: SELECT * FROM dataTable WHERE dataTable.FIELD = 'Value' FOR XML AUTO, ROOT('RootVal')

生成的数据集是格式良好的 XML，但我不知道如何将它从结果集获取到文件.

The resulting dataset is well formed XML, but I can't figure out how to get it from result set to file.

在 C# 4.0 中编写控制台应用程序来执行此操作对我来说是一项相对容易的任务，但限制要求我至少在编写控制台应用程序和调度程序之前证明它无法使用 SSIS 完成.

It's a relatively easy task for me to write a console app to do this in C# 4.0, but restrictions require me to at least prove it CAN'T be done with SSIS before I write the console app and a scheduler.

推荐答案

抱歉破坏，但有一个 SSIS 选项适合您:导出列转换.

Sorry to spoil, but there's an SSIS option for you: Export Column Transformation.

我定义了一个 OLE DB 查询

I defined an OLE DB query with

SELECT
    *
FROM
(
    SELECT * FROM dbo.spt_values FOR XML AUTO, ROOT('RootVal')
) D (xml_node)
CROSS APPLY
(
    SELECT 'C:\ssisdata\so_xmlExtract.xml'
) F (fileName)

这会导致数据流中有 1 行和 2 列.然后我附加了导出列转换并将它与 xml_node 作为 Extract Column 和 fileName 作为文件路径列

This results in 1 row and 2 columns in the dataflow. I then attached the Export Column Transformation and wired it up with xml_node as Extract Column and fileName as the File Path Column

大部分是截断的结果

<RootVal>
    <dbo.spt_values name="rpc" number="1" type="A  " status="0"/>
    <dbo.spt_values name="dist" number="8" type="A  " status="0"/>
    <dbo.spt_values name="deferred" number="8192" type="V  " low="0" high="1" status="0"/>
</RootVal>

此问答中提供了更详细的带有图片的答案使用 ssis 导出 Varbinary(max) 列

A more detailed answer, with pictures, is available on this Q&A Export Varbinary(max) column with ssis

这篇关于使用 SSIS 将表数据的 XML 表示提取到文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！