Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert TEXT to XML
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
27 Posts

Posted - 02/27/2014 :  12:22:29  Show Profile  Reply with Quote
Hi all

I'm running SQL Server 2008 R2 Express, which is linked to a SQL Server 2000 instance.

In the SQL2000 instance there is a table with a column containing XML strings of approximately 8000 characters in length. The column is in TEXT format. I need to be able to read the nodes contained within this string. According the xml string, it is formatted in UTF-16.

This is my first time working with XML in TSQL, and I'm a bit stuck. I've tried converting it into an XML format with little luck as follows:
SELECT CONVERT(XML,[Content]) FROM [LinkedServer].[UPLSalesP].[dbo].[RSSDocument]

However I always receive the following error:
Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character 39, unable to switch the encoding

Character 39 is the very end of the XML encoding tag:
<?xml version="1.0" encoding="utf-16"?>

Can anyone help shed any light on what I'm missing? I've attempted Google dozens of times today, but there seems to be little information regarding SQL2000 and XML that I can use.

Many thanks in advance.

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/27/2014 :  14:31:30  Show Profile  Reply with Quote
SQL 2000 doeant have XML data type. So you should use OPENXML

SQL Server MVP
Go to Top of Page

Patron Saint of Lost Yaks

30421 Posts

Posted - 02/27/2014 :  14:57:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Visakh, the data type conversion is taking place in the 2008R2 side.
What OP have to do is double conversion.

SELECT CONVERT(XML, CAST([Content] AS NVARCHAR(MAX))) FROM [LinkedServer].[UPLSalesP].[dbo].[RSSDocument]

Go to Top of Page

Starting Member

United Kingdom
27 Posts

Posted - 02/28/2014 :  04:21:08  Show Profile  Reply with Quote
Thanks for the advice guys, I was still getting an encoding conversion error and in the end used the following to sort it out - it seems removing the encoding tag from within the XML string itself did the trick quite nicely.
SELECT CAST(REPLACE(CAST(Content AS NVARCHAR(MAX)), 'encoding="utf-16"', '') AS XML) AS ContentXML
FROM [LinkedServer].[UPLSalesP].[dbo].[RSSDocument]

However having struggled for a few hours trying to work with the data, it seems my local workstation and 2008R2 just doesn't have enough juice to get the job done.

I thought about using the 2000 server to get the job done as it has a lot more resource available on its stack. I've tried the following and its working great:
DECLARE @i INT, @xml VARCHAR(8000)
SELECT TOP 1 @xml = REPLACE(CONVERT(VARCHAR(8000), Content),'encoding="utf-16"','') FROM UPLSalesP.dbo.RSSDocument

EXEC sp_xml_preparedocument @i OUTPUT, @xml

SELECT * FROM OPENXML(@i, N'/UPLFolder/UPL/ApplicationData/LoanRequirements')

EXEC sp_xml_removedocument @i

However this is providing me a node-by-node rowset, when in reality all I want is the value of one node, for every record in the table.

Can I use OPENXML to read the xml string from every row in the table and pull a value from a specific node?

Edited by - ryan.gillies on 02/28/2014 04:52:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000