Importing US-ASCII into SQL Server

Question

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.

http://stackoverflow.com/questions/497782/how-to-convert-a-string-from-utf8-to-ascii-single-byte-in-c consider using string instead of XML — phil soady, Apr 08 '15 at 20:45
What's the character at line 1, character 41? It sounds like it might be that the XML file is buggy in claiming to be in US-ASCII but having a character that is not in that encoding. — Jon Hanna, Apr 08 '15 at 20:59
We want to use an XML data type so we can use XQuery against it later. Character 41 is the closing ">" in the ?> declaration. — Evan M., Apr 09 '15 at 15:17

score 2 · Accepted Answer · answered Apr 08 '15 at 20:46

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.

Thanks for the article Dai. Though, I think the more pertinent point is the 9th one. "The XML declaration PI in an instance is not preserved when the instance is stored in the database. The XML declaration PI, for example, , is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration () and its attributes (version/encoding/stand-alone) are lost after data is converted to type xml. The XML declaration is treated as a directive to the XML parser. The XML data is stored internally as ucs-2." — Evan M., Apr 09 '15 at 15:39

Importing US-ASCII into SQL Server

1 Answers1