跳转至

把 XML 写出成文件 — BCP 存储过程与 SSIS 包两条路

这一章讲什么: 第 02 章生成的 XML 还困在查询结果窗口里。本章讲怎么把它写成磁盘上的 .xml 文件,纯 T-SQL 一条(BCP 实用工具 + 存储过程)、SSIS 一条(可视化数据集成包)。 读完你会知道两条路各自的构件、那个绕不开的 xp_cmdshell 安全开关,以及为什么作者 写文件偏爱 SSIS 的 Script Task。

1. 先看现象:为什么「查出来」不等于「拿到文件」

在 SSMS 里跑一条 FOR XML 查询,你看到的 XML 只是一个显示结果——它没有被保存,不能发给下游系统,重启即消失。要把 XML 变成文件,必须借助数据库之外的工具:命令行的 BCP 实用工具,或独立的 SSIS 平台1。这就是本章开篇的位置:生成(第 02 章)解决「XML 长什么样」,本章解决「XML 怎么离开数据库」。

2. 顶层全景:主线例子与两条路

本章主线例子: 把第 02 章 PATH 模式生成的那份商品目录 XML(根元素 <Products>,内含 Categories → Subcategory → Product 层级),写成一个带时间戳的文件:

  C:\TEMP\Categories_20260310_164701.xml
       │                          └─ 时间戳:2026年3月10日 16时47分01秒
       └─ 名字主体:Categories

(文件名格式来自原书 BCP 命令示例;这个具体时刻是原书演示的值。)

两条路走的「楼梯」不一样,但目的地相同:

  路一(BCP,纯 T-SQL):  FOR XML 查询结果 → 全局临时表 ##XML → xp_cmdshell 调 BCP → 文件
  路二(SSIS 包):        Execute SQL Task 查库 → 变量 XMLData_Content → Script Task(C#)写文件

3. 核心原理

3.1 BCP 方案:为什么中间要夹一张全局临时表

解决什么问题: 想在一段存储过程里,从查询直接得到磁盘上的 XML 文件,不依赖任何外部程序。

怎么做,五个构件: 构件一是存储过程,收一个参数 @FilePath(目标文件夹),让同一段代码在开发、测试、生产环境都能用2。构件二是全局临时表 ##XML:先把 FOR XML 的查询结果 INSERT 进一张只有一列的临时表,再让 BCP 从这张表往外倒3。

构件三是个必须解释的名词:BCP(bulk copy program,大容量复制程序),SQL Server 自带的命令行工具,能把表数据导出成文件4。它从存储过程里被调用的方式是构件四:xp_cmdshell——一个让 T-SQL 执行操作系统命令的扩展存储过程5。构件五是 BCP 命令本身,拆开看每个零件6:

  BCP ##XML out "C:\TEMP\Categories_20260310_164701.xml" -S "APRESS\SQL2025" -T -c
   │     │      │                                        │      │     └ -c:内容按字符类型输出
   │     │      │                                        │      └ -T:用 Windows 信任连接(否则 -U 登录名 -P 密码)
   │     │      │                                        └ -S:服务器名\实例名
   │     │      └ 目标文件路径
   │     └ out:从表往文件拷(方向)
   └ 工具名

  图说:BCP 不连接查询,只认表——所以 XML 必须先落进 ##XML 这张表。

为什么有效: BCP 是独立进程,读写速度极快——书里实测创建一个 XML 文件用了 15 毫秒7。瓶颈不在工具,而在调用链上。

边界(三个坑,原书逐个给了处理):

  1. 为什么必须用全局临时表(##开头)而不是普通临时表(#开头)? BCP 是另一个进程、另一个会话;会话级的 # 临时表别的会话看不见,BCP 一引用就报错,所以要么永久表、要么全局临时表 ##XML8。
  2. 全局临时表的全局风险: ## 表对整个服务器可见。如果两个人同时执行这个存储过程,后写的人会覆盖前一个人的表,拿到的结果就是错的——同时执行(并发)场景下这是个真实的正确性隐患(书里给了 usp_WriteXMLFileDynamicTable 的动态表名方案来化解)9。
  3. xp_cmdshell 默认是关的: 因为「允许数据库执行操作系统命令」本身就是高权限操作,SQL Server 默认把它禁用10。书里的做法是:先记当前状态 → 临时打开 → 跑 BCP → 恢复原状,全程毫秒级,作者认为「服务器处于打开状态的那几毫秒几乎没有安全风险」11。——这是作者的安全判断,不是所有人的安全部门都买账,见第 4 节。

一个顺手的小技巧: BCP 跑完会向结果窗口回显完成状态和耗时。不想让调用方看到这些输出,就预建一个表变量 @stat,用 INSERT @stat EXECUTE master..xp_cmdshell @SQL 把输出「吸」进表里,调用方就干净了12。

3.2 SSIS 方案:三个组件走完「查数 → 写盘」

解决什么问题: BCP 是「好用的老工具」,但微软的标准 ETL 方案是 SSIS(SQL Server Integration Services,SQL Server 的集成交换工具),它面向的是可维护、可调度、可参数化的生产流程13。

怎么做,整包只有三个控制流任务:

  [Execute SQL Task: Get XML Content]
        │  执行第 02 章那条 FOR XML PATH 查询,ResultSet 设为 XML
        ▼
  [变量 TimeStamp 的表达式]   ← 拼出 yyyyMMdd_HHmmss 格式的时间戳
        │
        ▼
  [Script Task: Create XML File]
        │  几行 C#:从变量读出 XML 内容,用 StreamWriter 写文件
        ▼
  C:\TEMP\Categories_<时间戳>.xml

  图说:取数、起名、写盘,三段各司其职;连接它们的绿色箭头叫 Precedence Constraint(优先约束)。

支撑这套流程的是三个包级变量:FileDestinationPath(目标文件夹,包外可改,等于参数)、TimeStamp(文件名时间戳)、XMLData_Content(存查询结果)14。取数环节有一个必须点对的设置:Execute SQL Task 的 ResultSet 属性要设成 XML,查询返回的是单个 CLOB(大字符对象)值,绑到 XMLData_Content 变量上,Result Name 填 0(结果没有列名,0 表示第一列)15。

写盘环节的 C# 只有几行核心(原书 Listing 3-5,为讲解有删减):

string strFilePath = Dts.Variables["User::FileDestinationPath"].Value.ToString()
                   + @"\Categories_" + Dts.Variables["User::TimeStamp"].Value.ToString() + ".xml";
string strXML = Dts.Variables["User::XMLData_Content"].Value.ToString();
System.IO.StreamWriter file = new System.IO.StreamWriter(strFilePath);
file.WriteLine(strXML);   // 写内容

StreamWriter 是 .NET 里写文本文件的类,属于 System.IO 命名空间,脚本编辑器不会自动带上,要么手动 using System.IO;,要么像上面这样写全限定名16。

为什么有效: 流程的每一步都是可见的组件,双击就能配置、设断点就能调试;文件名、路径都在变量里,部署到生产只要改变量值,不用动包17。

边界:

  • 时间戳表达式的语法是 SSIS 方言,不是 T-SQL 也不是普通 .NET——(DT_STR, 4, 1252) DATEPART("yyyy", GETDATE()) 这种写法只在 SSIS 表达式里合法,逐段拼年、月、日、时、分、秒再补前导零18。
  • 变量名在 C# 里区分大小写,绑定时要照抄 User:: 前缀原名19。
  • SSIS 写文件其实还有两条备选路(Flat File 目标、Export Column 转换),作者都列了但明确偏爱 Script Task,理由:几行代码就完事、调试容易、全程可控20——这个偏爱见第 4 节的讨论。

4. 作者的判断与证据

有实测支撑的: BCP 的速度(15 毫秒出一个文件,含输出截图)7;SSIS 包的成功率(两个任务都亮绿勾、文件出现在 C:\TEMP)17。这些是演示证据,不是基准测试——单文件、小数据量,证明「可行」而非「多快」。

作者明说「我认为 / 我偏爱」的: Script Task 优于另外两种 SSIS 写文件方式(理由四条:快、代码少、调试易、可控)20;「xp_cmdshell 打开几毫秒几乎没有安全风险」11;「手动建 XML Schema 的时代结束了」(第 01 章那条立场在本章继续保持)。

值得停下来审视的安全判断: xp_cmdshell 的「开—关—还原」手法在实例层面确实把暴露窗口压到毫秒级,但它要求执行者本来就有改服务器配置的权限——在多数企业的生产环境里,这个权限本身就会被安全部门收走,根本没机会玩这个手法。书里也埋了退路:如果你的环境禁用 xp_cmdshell,考虑 SSIS 方案(补充判断:SSIS 的 Script Task 同样能执行进程,安全模型不同但也不是零风险——选路的真正依据是组织的安全审计口径,不是技术快慢)。

5. 边界与局限

  • SSIS 的界面操作细节会过时: 点哪个菜单、哪个对话框,随 Visual Studio/SSDT 版本变化;不变的是「Execute SQL Task 取数 + 变量传值 + Script Task 写盘」这个结构。
  • BCP 方案的并发缺陷书里承认但没有展开修复代码,只指了代码示例库里的 usp_WriteXMLFileDynamicTable——要抄作业得去看原书配套代码。
  • 本章没谈大数据量: 几百 GB 的 XML 导出、断点续传、并行写,都不在讨论范围;15 毫秒是玩具数据的数字。
  • 写完文件之后呢(权限、共享、下游通知)不属本章: 文件写好了谁来读、怎么知道写好了,留给集成设计。

6. 可带走的

本章主线例子,一行走完: Categories XML → (路一)INSERT 进 ##XML → xp_cmdshell 调 BCP ##XML out "C:\TEMP\Categories_时间戳.xml" -S 实例 -T -c → 15 毫秒成文;(路二)Execute SQL Task 查库 → 变量 → Script Task 三行 C# 写同一个文件。

  1. BCP 只认表不认查询——XML 必须先落进一张表(全局临时表 ##XML),这是整个存储过程方案的形状由它定的根本原因4。
  2. ## 全局临时表对全服务器可见,并发执行会互相覆盖——要么动态表名,要么换方案9。
  3. xp_cmdshell 默认禁用;「记状态 → 临时开 → 跑 → 还原」是书里的标准手法,但是否被允许取决于安全部门,不是 DBA 自己说了算11。
  4. BCP 命令的四个必背零件: 表 out "路径" -S 服务器 -T(信任连接) -c(字符模式)6。
  5. 想吞掉 BCP 的输出回显,用 INSERT @stat EXEC xp_cmdshell 把它吸进表变量12。
  6. SSIS 写文件 = 三个组件:Execute SQL Task(ResultSet 设 XML)→ 变量(TimeStamp 用 SSIS 方言表达式)→ Script Task(StreamWriter 写盘)1516。
  7. Result Name 填 0:查询结果没列名时,0 代表第一列15。
  8. SSIS 的时间戳格式语法自成一体,别拿 T-SQL 的思维写表达式18。

7. 原文地图

主题 原书章 原文位置
BCP 导出与 xp_cmdshell 配置 Chapter 3: Manipulating XML Files text/07-ch03-chapter-3-manipulating-xml-files.txt:13(搜「bulk copy program」) · text/07-ch03-chapter-3-manipulating-xml-files.txt:63(搜「xp_cmdshell」)
全局临时表的原因与风险 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:46(搜「global temporary table」)
BCP 命令与参数 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:91(搜「BCP ##XML out」) · text/07-ch03-chapter-3-manipulating-xml-files.txt:96(搜「executable name」)
15 毫秒实测与吞输出技巧 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:157(搜「15 milliseconds」) · text/07-ch03-chapter-3-manipulating-xml-files.txt:176(搜「absorbs」)
SSIS 三条写文件路线与作者偏爱 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:193(搜「handy legacy command-line utility」) · text/07-ch03-chapter-3-manipulating-xml-files.txt:202(搜「my preference」)
包的三个控制流任务与变量 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:213(搜「three control flow tasks」) · text/07-ch03-chapter-3-manipulating-xml-files.txt:260(搜「FileDestinationPath」)
ResultSet=XML 与 Result Name 0 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:327(搜「ResultSet property」) · text/07-ch03-chapter-3-manipulating-xml-files.txt:337(搜「first column」)
SSIS 时间戳表达式方言 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:348(搜「yyyyMMdd_HHmmss」)
Script Task 的 C# 写盘 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:435(搜「StreamWriter」)
包的整体工作方式 同上 text/07-ch03-chapter-3-manipulating-xml-files.txt:472(搜「How It Works」)

  1. 出处:「Chapter 3: Manipulating XML Files」第 2–6 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:4,搜「store the XML result」)。原文:在讨论拆分 XML 之前,「we first need to know how to store the XML result on the storage (disk, SSD drive, SAN, etc.) and how to upload an XML file into a table」。 ↩

  2. 出处:「Chapter 3: Manipulating XML Files」第 42–45 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:42,搜「destination path」)。原文:「The parameter @FilePath is the XML file destination path…makes the stored procedure flexible, especially when running it in different environments, such as development, staging, and production」。 ↩

  3. 出处:「Chapter 3: Manipulating XML Files」第 46–60 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:46,搜「global temporary table」)。原文:create 「a global temporary table (##XML)」,「Use a column name XMLHolder with the XML data type to get XML data」。 ↩

  4. 出处:「Chapter 3: Manipulating XML Files」第 13–17 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:13,搜「bulk copy program」)。原文:「The BCP (bulk copy program) utility allows the export of data into an XML file」。 ↩↩

  5. 出处:「Chapter 3: Manipulating XML Files」第 62–65 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:65,搜「xp_cmdshell extended stored procedure to run」)。原文:「the server instance needs to be configured for the "xp_cmdshell" option at value 1. This means that the instance allows the xp_cmdshell extended stored procedure to run」。 ↩

  6. 出处:「Chapter 3: Manipulating XML Files」第 91–109 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:91,搜「BCP ##XML out」)。参数逐一解释:「BCP – the bulk copy utility executable name」「##XML – name of table with XML data」「out – indicator that copies the data from the table」「-T – specifies that the BCP command runs under a trusted connection」「-c – specifies that the output content is in the character data type」。 ↩↩

  7. 出处:「Chapter 3: Manipulating XML Files」第 155–158 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:157,搜「15 milliseconds」)。原文:「my runtime to create the XML file was 15 milliseconds」。 ↩↩

  8. 出处:「Chapter 3: Manipulating XML Files」第 48–51 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:49,搜「the BCP command will raise an error」)。原文:「the BCP command will raise an error if a session-level temporary table is referenced. Therefore, we can use either a permanent table or a global temporary table」。 ↩

  9. 出处:「Chapter 3: Manipulating XML Files」第 51–59 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:52,搜「risk」)。原文:「There is always a risk in implementing a global temporary table if the process is part of the concurrent process. The global temporary table is seen throughout the entire server, and if somebody else happens to use the stored procedure at the same time, one of the user's tables could be overwritten」。 ↩↩

  10. 出处:「Chapter 3: Manipulating XML Files」第 179–185 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:182,搜「disabled」)。配方 3-1 与 3-3 的注释说明 xp_cmdshell 因安全原因默认禁用(disabled),存储过程自带了「self-detected to turn the logic on and off to minimize the security risk」的开关逻辑。 ↩

  11. 出处:「Chapter 3: Manipulating XML Files」第 66–73 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:71,搜「virtually no security risk」)。原文:「Since the BCP command is able to create the XML file in milliseconds, there is virtually no security risk during the milliseconds that the server is set to value 0 to enable the xp_cmdshell stored procedure」。 ↩↩↩

  12. 出处:「Chapter 3: Manipulating XML Files」第 162–177 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:176,搜「absorbs」)。原文:「The @stat table variable absorbs the BCP completion output; therefore, the stored procedure does not return any messages」。 ↩↩

  13. 出处:「Chapter 3: Manipulating XML Files」第 189–196 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:193,搜「handy legacy command-line utility」)。原文:「the BCP utility is a handy legacy command-line utility, but SSIS is Microsoft's standard ETL solution」。 ↩

  14. 出处:「Chapter 3: Manipulating XML Files」第 258–266 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:260,搜「FileDestinationPath」)。三个变量:「FileDestinationPath – To specify the destination folder…practically providing the parameter functionality」「TimeStamp – To obtain the file name time stamp」「XMLData_Content – To store the XML result that will be written to the file」。 ↩

  15. 出处:「Chapter 3: Manipulating XML Files」第 327–338 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:327,搜「ResultSet property」)与第 333–337 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:333,搜「scalar CLOB」)。原文:「set the ResultSet property to XML」;查询返回 XML「as a scalar CLOB (character large object) value」;「Set the Result Name property to 0. Since we do not have a name for the result set, setting it to 0 will choose the first column from the result set」。 ↩↩↩

  16. 出处:「Chapter 3: Manipulating XML Files」第 434–441 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:435,搜「StreamWriter」)。原文:「We need to create an instance of the StreamWriter object. The StreamWriter class belongs to the System.IO namespace, which is not part of the default namespaces…use the System.IO namespace as a full qualifier」。 ↩↩

  17. 出处:「Chapter 3: Manipulating XML Files」第 459–471 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:459,搜「Start button」)与第 472–480 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:472,搜「How It Works」)。测试:按 Start,「green circles with check marks will show up on each task」;工作方式:「The query result is assigned to the package-level variable XMLData_Content」「The "Create XML File" Script Task executes C# code to write the XML result out to the file」。 ↩↩

  18. 出处:「Chapter 3: Manipulating XML Files」第 343–360 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:348,搜「yyyyMMdd_HHmmss」)。原文:「the most often implemented format I've seen is yyyyMMdd_HHmmss. To this value, SSIS packages must have a syntax that is different from T-SQL or even .NET applications」。 ↩↩

  19. 出处:「Chapter 3: Manipulating XML Files」第 413–415 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:415,搜「case-sensitive」)。原文:「variable names are case-sensitive when referenced in the code」。 ↩

  20. 出处:「Chapter 3: Manipulating XML Files」第 195–211 段(text/07-ch03-chapter-3-manipulating-xml-files.txt:202,搜「my preference」)。原文:「my preference is Script Task」;理由:「Script Task is an easy and fast solution for file manipulation」「In a few lines of code…you can provide the task solution」「Easy and productive debugging process」「you have full control over the process」。 ↩↩