xmlt-sqlbcpsqlcmdsql-server-2008

What is :XML ON in T-SQL query to export data into XML using SQLCMD?

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

: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.

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

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

Solution

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

    That pages states:

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

    When you expect XML output, use the following command: :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.

    To set the XML mode off, use the following command: :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 (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.

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

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

    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.