SQL Server 2005 Query XML data stored in ntext field

We moved from SQL 2000 to SQL 2005 so we are still storing the XML data in ntext field. Finally I need to mine the XML content and I thought cool let me write some XPath queries and be done with it. As is the case with everything new there was a slight learning curve and I am trying to document the process.

First thing I need to do was cast my ntext data into xml. Simple!

DECLARE @xmlData AS xml
SELECT @xmlData = CAST(messagexml AS XML) FROM dbo.H2O_Message WHERE MessageID = 80467

The query editor responded with the following SQL error message
SQL Server Database Error: XML parsing: line 1, character 38, unable to switch the encoding

After some googling it turns out that SQL server works with UTF-16 and has issues with UTF-8 XML document. No problem, lets switch XML header tag. Well, the field is ntext so I need to cast the field to nvarchar(max) then replace the utf-8 to utf-16

DECLARE @xmlData AS xml
SELECT @xmlData = CAST( REPLACE(CAST(messagexml AS nvarchar(MAX)),'utf-8', 'utf-16') AS XML) FROM dbo.H2O_Message WHERE MessageID = 80467

Wonderful all I had to do then was query the Xpath I needed. There are various examples on the web on to do this so  I would not go into details here.

Here is the final SQL statement to get MessageControlID element text from my xml document stored as ntext field in SQL server 2005 Database.

DECLARE @xmlData AS xml
SELECT @xmlData = CAST( REPLACE(CAST(messagexml AS nvarchar(MAX)),'utf-8', 'utf-16') AS XML) FROM dbo.H2O_Message WHERE MessageID = 80467

SELECT msg.query('data(/ObservationMessage/MessageControlID)') FROM (SELECT @xmlData msg) tbl

Advertisements

About Shashi Kuppa

I have 14 years of Information System experience. My background ranges from software architecture, analysis, design and development to team leadership and project management. My primary strengths include .Net system architecture, object oriented development using C#, web development using Asp.net and SQL server development, reporting and integration. I am an excellent team player, good communicator and have a proven track record for goal-oriented project leadership and management
This entry was posted in Development and tagged . Bookmark the permalink.

One Response to SQL Server 2005 Query XML data stored in ntext field

  1. Russell Forbes says:

    This was just what I was looking for! Thank you.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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