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

Answer 1

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

• Jeramy: 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!