CODEWITHSUNDEEP
c#.netsql-serverxmlc#-4.0
Evan M.
Evan M.

Reputation: 221

Importing US-ASCII into SQL Server

I'm trying to create a database that will act as a repository for a series XML files that are being generated.

In my SQL Server database, I have a table similar to the following to store the XML file:

CREATE TABLE [dbo].[MyTable] 
(
    [InternalID] INT NOT NULL IDENTITY, 
    [ExternalID] INT NOT NULL,
    [XmlData] XML NOT NULL
)

Each of the XML files is encoded in US-ASCII with the following header:

<?xml version="1.0" encoding="us-ascii"?>

I've got a stored procedure created to insert the file into the table, and I'm hitting it with .NET using the following:

int externalKey = someint;
string myXML = File.ReadAllText(xmlFilePath);

using (SqlCommand myCommand = new SqlCommand ("My_Stored_Proc", myConnection)) {
    myCommand.CommandType = SqlCommandType.StoredProcedure;
    myCommand.Parameters.Add(new SqlParameter ("@ExternalID", externalID));
    myCommand.Parameters.Add(new SqlParameter ("@XmlData", myXML));
    myCommand.ExecuteNonQuery();
}

When my code tries to run, it encounters the following SqlException when trying to execute the non-query:

XML parsing: line 1, character 41, unable to switch the encoding

If I modify the string before I execute by replacing the encoding="us-ascii" with one that uses UTF-8, it goes in fine. But it would be preferred to not have the source modified in the database.

Upvotes: 1

Views: 756

Answers (1)

Dai
Dai

Reputation: 155035

This is not possible. See the article "Limitations of the xml Data Type": https://technet.microsoft.com/en-us/library/ms187107%28v=sql.90%29.aspx?f=255&MSPPError=-2147217396

XML provides its own encoding. Collations apply to string types only. The xml data type is not a string type. However, it does have string representation and allows casting to and from string data types.

and (emphasis mine):

If you copy and paste XML as a string literal into the Query Editor window in SQL Server Management Studio, you might experience [N]VARCHAR string encoding incompatibilities. This will depend on the encoding of your XML instance. In many cases, you may want to remove the XML declaration.

When reading in your XML files, just remove the <?xml ?> prelude and insert it directly. Given that ASCII is a subset of UTF-8 you won't run into any problems.

Upvotes: 2

Related Questions