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


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:

	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?

6 Responses to Retrieving XML Data in SQL Server 2005

  1. Just wanted to tell you that this article helped me in my job, so keep up the good work. Thanks.

  2. Catherine McManus says:

    This also helped me with a query I needed to do, so thanks a lot.

    Yes it is annoying that you have to manually remove the encoding. I wonder if there is some other way…


  3. You don’t need to use replace.
    Just do like this:

    cast(cast(XmlFile as nvarchar(max))as xml).query(‘xpath to nodes’) AS Node
    FROM TableName

    You need to convert to nvarchar and after it you can convert to xml.
    You don’t need to replace.


  4. ops…

    cast(cast(XmlFile as varchar(max))as xml).query(’xpath to nodes’) AS Node
    FROM TableName

    don’t use nvarchar, use varchar

  5. Ashok says:

    Can any one help me on this..

    We have a XML column in the Table, The XML File looks like
    Param Name=”ID” Value=”10090a93-b845-dd11-ab57-02bf0afb1072″ />
    Param Name=”Type” Value=”Req” />
    Param Name=”Type” Value=”Miss” />
    Param Name=”Time” Value=”5″ />
    In that i want to retrieve ID and Time value. Let me know how to retrive this from the Table

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: