1

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

1 Answer 1

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;"
1
  • 1
    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
    Jul 19, 2023 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.