XML parsing error while inserting XML to SQL Server

Question

I am writing a simple powershell script to insert XML files from a folder into SQL Server 2012. But every time it's failing when trying to insert the XML with below error. In Microsoft documentation, they are inserting UTF-8 encoded xml. I am able to insert XML data when I am changing encoding manually to "UTF-16". What I need to do to inset the XML without changing UTF-8 encoding. Thanks.

Error:

Exception calling "ExecuteScalar" with "0" argument(s): "XML parsing: line 1, character 38, unable to switch the encoding"

Line

$InsertedID = $sqlCommand.ExecuteScalar()

Table Definition:

CREATE TABLE TESTXML
(
     ID INT IDENTITY(1,1) NOT NULL,
     FileName VARCHAR(200),
     InsertedDate DATETIME DEFAULT GETDATE(),
     InsertedBy   VARCHAR(100) DEFAULT 'PS',
     XML XML,
     BLOB VARBINARY(MAX)
)

XML to parse:

<?xml version="1.0" encoding="UTF-8"?>
<order xmlns="http://XXXXX/xml/impex/XXX/2006-10-31" order-no="XXX">
XML Data......
</order>

Powershell Script:

foreach($xmlfile in $xmlfiles){
   [string]$xml = (Get-Content $xmlfile.FullName) -replace "'", "''"
    $sqlCommand.Parameters[0].Value = $xmlfile.FullName
    $sqlCommand.Parameters[1].Value = $xml 
    $InsertedID = $sqlCommand.ExecuteScalar()
    "Inserted row ID $InsertedID for file " + $xmlfile.FullName
}

Shnugo Shnugo · Accepted Answer · 2018-03-18T11:28:03

SQL Server handles an XML as unicode / UCS-2 (very related to utf-16). Powershell sends the string as 2-byte-encoded string, so the encoding="UTF-8" is not telling the truth. That's why it is complaining...

You have several choices:

Use something like ((Get-Content $xmlfile.FullName) -replace "'", "''") -replace "UTF-8","UTF-16" to change this right in your powershell code.
Use Powershell to cut off the declaration entirely (just send the XML without the <?xml ...?> thing)
Create a stored procedure accepting a NVARCHAR(MAX) and use T-SQL either to change the encoding or to remove the declaration.
Use a staging table in SQL-Server accepting the XML within a NVARCHAR(MAX) column and transfer the imported data to the target tables in a second step. (Staging tables are in many cases a good idea for bigger import scenarios)

Good luck

XML parsing error while inserting XML to SQL Server

1 Answers