什么是：使用SQLCMD将数据导出到XML中的T-SQL查询中的XML ON？

On this blog post about exporting data to XML with TSQL using BCP or SQLCMD I came across this line of code:

在这篇关于使用BCP或SQLCMD使用TSQL将数据导出到XML的博客文章中,我遇到了以下代码:

:XML ON

SELECT
    *
FROM
    dbo02.ExcelTest
FOR XML AUTO, ELEMENTS, ROOT('doc')

I tried using it in the sql query and then triggering via sqlcmd in a batch file, it did return a correct xml file. Without the :XML ON it returns strange values as described on the webpage.

我尝试在sql查询中使用它,然后在批处理文件中通过sqlcmd触发,它确实返回了一个正确的xml文件。如果没有:XML ON,它将返回网页上描述的奇怪值。

Strangely enough, SSMS (2008) shows incorrect syntax when parsing or executing the query.

奇怪的是,SSMS(2008)在解析或执行查询时显示错误的语法。

What does :XML ON do and how to use it? And why doesn't SSMS recognize the line of code?

什么:XML ON和如何使用它?为什么SSMS不识别代码行?

1 个解决方案

#1

It is explained in this MSDN page (search for ":XML"): sqlcmd Utility

在此MSDN页面(搜索“:XML”)中解释:sqlcmd实用程序

That pages states:

该页面指出:

XML output that is the result of a FOR XML clause is output, unformatted, in a continuous stream.

作为FOR XML子句的结果的XML输出在连续流中输出,未格式化。

When you expect XML output, use the following command: :XML ON.

当您期望XML输出时,请使用以下命令:: XML ON。

Note
sqlcmd returns error messages in the usual format. Notice that the error messages are also output in the XML text stream in XML format. By using :XML ON, sqlcmd does not display informational messages.

注意sqlcmd以通常的格式返回错误消息。请注意,错误消息也以XML格式输出到XML文本流中。通过使用:XML ON,sqlcmd不显示信息性消息。

To set the XML mode off, use the following command: :XML OFF.

要关闭XML模式,请使用以下命令:: XML OFF。

The GO command should not appear before the XML OFF command is issued because the XML OFF command switches sqlcmd back to row-oriented output.

在发出XML OFF命令之前不应出现GO命令,因为XML OFF命令将sqlcmd切换回面向行的输出。

XML (streamed) data and rowset data cannot be mixed. If the XML ON command has not been issued before a Transact-SQL statement that outputs XML streams is executed, the output will be garbled. If the XML ON command has been issued, you cannot execute Transact-SQL statements that output regular row sets.

XML(流式)数据和行集数据不能混合使用。如果在执行输出XML流的Transact-SQL语句之前尚未发出XML ON命令,则输出将是乱码。如果已发出XML ON命令,则无法执行输出常规行集的Transact-SQL语句。

Note
The :XML command does not support the SET STATISTICS XML statement.

注意:XML命令不支持SET STATISTICS XML语句。

In SSMS, even if you enable "SQLCMD Mode" (in the Query menu), you will get the following error trying to use the :XML command:

在SSMS中,即使启用“SQLCMD模式”(在“查询”菜单中),尝试使用:XML命令时也会出现以下错误:

Scripting warning.
Command Xml is not supported. String was not processed.

This is most likely due to this particular command not being needed within the context of the Query Editor.

这很可能是由于在查询编辑器的上下文中不需要此特定命令。

#1