SQL命令字符串c#

Question

我目前正在编写一个小脚本，该脚本导出程序的事件日志并将其写入数据库。但是，我的SQL insert命令有问题。我做错了什么？这只是一个代码删减

string command = "DECLARE @x xml;";
command += "SELECT @x = P";
command += "FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";
command += "SELECT @x";
command += "DECLARE @hdoc int";
command += "EXEC sp_xml_preparedocument @hdoc, OUTPUT, @x";
command += "INSERT INTO [MFlogs].[dbo].[MLogs]";
command += "SELECT * FROM OPENXML(@hdoc, '/Export/root/event/data/objectversion/objver', 2);";
command += "WITH(";
command += "id int '../../../id',";
command += "type varchar(50) '../../../type',";
command += "category varchar(50) '../../../category',";
command += "timestamp varchar(50) '../../../timestamp',";
command += "causedbyuser varchar(50) '../../../causedbyuser',";
command += "objtype varchar(50),";
command += "objid varchar(50),";
command += "version varchar(50),";
command += "objectguid varchar(50) '../objectguid',";
command += "versionguid varchar(50) '../versionguid',";
command += "title varchar(100) '../title',";
command += "displayid int '../displayid')";

SqlCommand cmd = new SqlCommand(command, connection);
int result = cmd.ExecuteNonQuery();

这是事件日志中的错误：

@ hdoc标量变量必须声明。

有人能帮忙吗？

Answer 1

只看第一行，您就会遇到问题：

    command += "SELECT @x = P";
    command += "FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";

最后会变成这样：

SELECT @x = PFROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORI ....

因为您没有留下任何间隔，就像这样，但是在其他地方，您也需要它：

command += "SELECT @x = P";
command += " FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";

试着解决所有的间隔问题，看看会发生什么。

Answer 2

在OUTPUT之前，您不需要逗号。

替换

command += "EXEC sp_xml_preparedocument @hdoc, OUTPUT, @x";

使用

command += " EXEC sp_xml_preparedocument @hdoc OUTPUT, @x";

请参阅https://learn.microsoft.com/ru-ru/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql?view=sql-server-ver15

还在每个字符串的开头添加一个空格，或Environment.NewLine或使用StringBuilder.AppendLine。