主页 / dba / 问题 / 329413
Accepted
Jeramy
Asked: 2023-07-19 00:09:23 +0800 CST

从 Powershell 调用 SQL 存储过程以将 XML 插入表中

  • 772

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

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

2 个回答

  1. Best Answer

    不要尝试手动清理您的输入。升级到Invoke-SqlCmd2或Invoke-DbaQuery，以便您可以改用绑定参数。

    这两个命令都采用参数-SqlParameter，您可以使用该参数来提供一个或多个未经处理的参数。你的用法是：

    Invoke-SqlCmd2 `
    -ServerInstance $server `
    -Database $database `
    -Query "[dbo].[INSERT_XML] @XML;" `
    -SqlParameters @{XML=$XMLString} `
    -Credential $PSCred

    注意：虽然您可能不需要切换真/假功能，但对此 SqlCmd2 上的 -TrustServerCert 切换支持的功能请求已被拒绝，转而支持支持 Invoke-DbaQuery 作为新标准：https: //github.com/dataplat/Invoke- SqlCmd2/问题/9

    您可以查看 Invoke-DbaQuery 文档页面上的示例，或者自己尝试最小的重现。

    use tempdb
go
create table tbl ( 
    doc_xml xml 
);
go
create proc p ( 
    @doc_xml xml 
) 
as 
insert tbl (doc_xml) 
values (@doc_xml);
go

    $xml = '<a>foo</a>'
Invoke-DbaQuery `
    -SqlInstance . `
    -Database tempdb `
    -Query "exec p @doc_xml;" `
    -SqlParameter @{doc_xml=$xml};

Invoke-DbaQuery `
    -SqlInstance . `
    -Database tempdb `
    -Query "select * from tbl;"
    • 1

  2. 这是我的 PowerShell 中的一个愚蠢错误。Powershell 中的这一行无法正常工作：

    [string]$XMLString = (Get-Content $XMLFile.FullName).replace('`n','').replace('`r','').replace("'","\'")

    我需要使用：

    [string]$XMLString = (Get-Content $XMLFile.FullName).replace('`n','').replace('`r','').replace("'","''")

    我最初没有这样做，因为我认为 PowerShell 中的两个单引号会出现问题，结果恰恰相反，斜杠把我搞乱了。

    • -1

相关问题