主页 / user-276803

Jeramy's questions

Martin Hope
Jeramy
Asked: 2023-07-19 00:09:23 +0800 CST
  • 6

我有一个有效的存储过程：

USE [DB_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[INSERT_XML] 
    -- Add the parameters for the stored procedure here
    @XML XML
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @Order AS INT
    SELECT @Order = MAX([order_id])+1
    FROM [DB_NAME].[dbo].[TABLE_NAME]
    INSERT INTO [DB_NAME].[dbo].[TABLE_NAME] ([Status]
          ,[error_message]
          ,[start_date_time]
          ,[end_date_time]
          ,[SystemId]
          ,[bundle_name]
          ,[doc_xml]
          ,[order_id]
          ,[batch_load_date])
    VALUES (0,'',GETDATE(),GETDATE(),'tmpltst','TEST',@XML,@Order,GETDATE()); 
END

我已经测试过使用其他应用程序提供的 XML（在将所有单引号加倍之后）来填充 @XML 参数，它会产生预期的结果，将提供的 XML 添加到表中新行的单个单元格中。

XML 的源是另一个应用程序提供的文件。我需要定期使用所提供文件的 XML 内容执行存储过程。我一直在编写一个 PowerShell 脚本来执行此操作。

$server = "server.address.com"
$database = "DB_NAME"
$XMLFile = Get-ChildItem C:\local\*.xml | select -First 1
[string]$XMLString = (Get-Content $XMLFile.FullName).replace('`n','').replace('`r','').replace("'","\'")
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "[dbo].[INSERT_XML] @XML = '$($XMLString)'" -credential $PSCred -TrustServerCertificate

这会导致错误：

Invoke-Sqlcmd : Incorrect syntax near 's'.
 Msg 102, Level 15, State 1, Procedure , Line 1.
At line:1 char:1
+ Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "[db ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

我尝试了多种不同的方法来格式化该 -Query 并转义 $XMLString。我预计这是一个未正确转义 $XMLString 内容的问题，该内容将包含斜杠、单引号、双引号、换行符等。我将在 Get-Content 中删除换行符并用 ' 替换单引号，但我认为单引号仍然会抛出 PowerShell 命令？

任何帮助使其正常工作的帮助都将不胜感激。谢谢！

编辑：好的，我确认该变量是我的问题。我从字符串中删除了所有单引号，并且它按预期进行了处理。所以剩下的问题是：如何在不删除所有单引号的情况下让它运行。此时，这可能更像是 PowerShell 问题，而不是数据库问题。

Edit2：感谢下面 Peter 的推荐，我最终使用了 Invoke-SqlCmd2 并修改了我的 PowerShell 使用它。

$server = "server.address.com"
$database = "DB_NAME"
$XMLFile = Get-ChildItem C:\local\*.xml | select -First 1
[string]$XMLString = Get-Content $XMLFile.FullName
Invoke-SqlCmd2 `
    -ServerInstance $server `
    -Database $database `
    -Query "[dbo].[INSERT_XML] @XML;" `
    -SqlParameters @{XML=$XMLString} `
    -Credential $Credential
stored-procedures