通过批处理脚本运行SQLCMD，从存储的事务创建格式化的XML文件

I'm trying to automate the process of running a stored procedure in a database by using SQLCMD. SQL Server Management Studio 2008 is installed on the Windows Server that this is all trying to happen in. SQLCMD will be called through a batch script and told to execute the stored procedure and save the output into an XML file. I cannot show the stored procedure as it has sensitive material, but it includes the usage of FOR XML PATH('').

我试图通过使用SQLCMD使在数据库中运行存储过程的过程自动化。SQL Server Management Studio 2008安装在Windows服务器上，这一切都是为了实现。SQLCMD将通过批处理脚本调用，并被告知执行存储过程并将输出保存到XML文件中。我不能显示存储过程，因为它有敏感的材料，但是它包含了XML PATH(")的用法。

I read several articles from all kinds of sites and people have said to use :XML ON to get the output in actual XML format and not in tabular format, as well as the switches of "-h-1 -y 0" to make sure that the output isn't truncated. I am trying to run the SQLCMD through a batch script so that it can all be automated.

我阅读了各种网站上的一些文章，人们说过要使用:XML ON来获取实际的XML格式而不是表格格式的输出，以及“-h-1 -y 0”的开关，以确保输出没有被截断。我正在尝试通过一个批处理脚本运行SQLCMD，这样就可以全部实现自动化。

My current batch script (the variables are all defined before this line in the script):

我的当前批处理脚本(变量都是在脚本中这一行之前定义的):

    sqlcmd -X -Q -h-1 -y 0 "EXEC %TransactionName%" -d %Database% -S %ServerInstance% -o "%OutFilename%_%currDATE%.xml"

I tried adding :XML ON in the transaction as well as creating a seperate SQL script that reads:

我尝试在事务中添加:XML，并创建一个独立的SQL脚本，其内容如下:

    Run_Transact.sql
    :XML ON
    EXEC storedProcedure

and so the batch file would then read:

那么批处理文件就会是:

    sqlcmd -X -Q -h-1 -y 0 -i runTransact.sql -d %Database% -S %ServerInstance% -o "%OutFilename%_%currDATE%.xml"

I get back the error:

我得到了错误:

HResult 0x80004005, Level 16, State 1 No description provided

HResult 0x80004005，级别16，状态1没有提供描述

If I don't use :XML ON then I get output that looks like it is in tabular format and it includes a header as well as only the first record, but not all of it (it gets truncated).

如果我不使用:XML，那么我得到的输出看起来就像表格格式，它包括一个头文件和第一个记录，但不是所有的记录(它被截断)。

My question is how can I get the output in the XML file to actually look like XML and not truncated as well?

我的问题是，如何使XML文件中的输出看起来更像XML，而不是被截断?

Thanks so much in advance!

非常感谢!

2 个解决方案

#1

This approach works for me. I retrieved 2 milion lines xml usinq sqlcmd from xml column. My steps:

这种方法对我很有效。我从xml列中检索了2百万行xml usinq sqlcmd。我的步骤:

1) Create File with query, this is mine:

1)创建有查询的文件，这是我的:

:XML ON
USE <DB_NAME>
SELECT TOP 1 <COLUMN_NAME> FROM <TABLE>
WHERE <SOMETHING>
FOR XML PATH('')

2) Execute command in sqlcmd

2)在sqlcmd中执行命令

sqlcmd -d <DB_NAME> -i <PATH>\query.sql >result.txt

And replace what you need in <>

在<>中替换你需要的

In your case you have stored procedure. Maybe it will cause problems? But you can try something like;

在您的案例中，您有存储过程。也许会引起问题?但是你可以试试;

USE<DB_NAME>
DECLARE @XMLV XML
EXEC @XMLV = EXEC StoredProcedure
SELECT @XMLV
FOR XML PATH('')

I know the question is quite old, but maybe it will help someone.

我知道这个问题由来已久，但它可能会对某些人有所帮助。

#2

-1

SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
FOR
XML PATH('Colors'),
TYPE
),
( SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
FOR
XML PATH('Fruits'),
TYPE
)
FOR XML PATH(''),
ROOT('SampleXML')
GO

#1