Call SQL Stored Procedure from Powershell to insert XML into a table

Question

I have a working stored procedure:

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

I have tested this as working with the XML provided by my other application (after doubling all the single quotes) to populate the @XML parameter, it produces the expected results, adding the XML provided into a single cell of a new row in the table.

The source of the XML is a file provided by another application. I need to execute the stored procedure using the XML contents of the provided file on a periodic basis. I have been working on a PowerShell script to do this.

$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

This results in the error:

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

I have tried a number of different ways of formatting that -Query and of escaping the $XMLString. I expect that this is an issue of not properly escaping the contents of the $XMLString which will have slashes, single quotes, double quotes, newlines, etc. I am stripping the newlines and replacing the single quotes with ' in the Get-Content, but I am thinking the single quotes are still throwing off the PowerShell command?

Any help getting this working is appreciated. Thanks!

Edit: Okay, I verified that the variable is my issue. I removed all single-quotes from the string and it processed as expected. So the remaining question is: How can I get it to run without removing all the single-quotes. This might be more of a PowerShell question than a DB question at this point.

Edit2: Thanks to Peter's recommendation below, I ended up using Invoke-SqlCmd2 and modifying my PowerShell use it.

$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

Peter Vandivier · Accepted Answer · 2023-07-19 13:26:43Z

Don't try to sanitize your inputs manually. Upgrade to Invoke-SqlCmd2 or Invoke-DbaQuery so you can use a bound parameter instead.

Both of these commands take the argument -SqlParameter which you can use to supply one or many parameters unsanitized. Your usage would be:

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

Note: while you may not need switch true/false functionality, the feature request for -TrustServerCert switch support on this SqlCmd2 was declined in favor of supporting Invoke-DbaQuery as the new standard: https://github.com/dataplat/Invoke-SqlCmd2/issues/9

You can review examples on the Invoke-DbaQuery docs page or try a minimal repro for yourself.

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;"

Thanks! I chose to go with Invoke-SqlCmd2. A couple of notes: You have an = in SqlParameters that is incorrect, and Invoke-SqlCmd2 does not have a TrustServerCertificate flag (and apparently doesn't need it). I added my updated PS to the orginal question. Thanks for your help! — Jeramy
– Jeramy, Commented Jul 19, 2023 at 13:06

Stack Exchange Network

Call SQL Stored Procedure from Powershell to insert XML into a table

1 Answer 1

Your Answer

Hot Network Questions

Call SQL Stored Procedure from Powershell to insert XML into a table

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions