Retrieving XML Data in SQL Server 2005

I recently developed an application, which, among other things, communicates with other already-existing applications via XML files. The files are passed through MQSeries queues and stored in an SQL Server database.

Today I needed to find database records with contain certain XML content, so I thought, OK, that’s SQL Server 2005, it has XML support, so that shouldn’t be a big deal.

The table stores the XML files in an NTEXT column, so I tried

SELECT ID, CAST(XmlFile AS XML) FROM TableName

which gave me an unexpected error message.

Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character 43, unable to switch the encoding

True, SQL Server stores my files in Unicode if I declare the column ntext. But how should I ever be able to store foreign XML documents, if the server can’t handle them? Ignoring the encoding attribute would be very nice in that case.

Next step: try to get rid of the encoding attribute. Easy, just replace it with an empty string:

SELECT ID, CAST(REPLACE(XmlFile, encoding="ISO-8859-1"', '') AS XML) ...

with the undesired side-effect, that *any* occurrence would be replace, not just the one within the ?xml? declaration.

Luckily, that didn’t work either:

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.

You’d expect that, since ntext and nvarchar(max) *behave* the same (i.e. unlimited Unicode strings), functions operating on them shouldn’t make a difference either.

Finally, my solution looked like this:

SELECT ID, CAST(
	REPLACE(CAST(XmlFile AS NVARCHAR(MAX)), 'encoding="ISO-8859-1"', '')
	AS XML).query('xpath to nodes') AS Node
FROM TableName

Should I be happy that this works, or angry that there’s no simpler way to do it?