Convert TEXT to XML - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert TEXT to XML

Author

Topic

ryan.gillies
Starting Member

27 Posts

Posted - 2014-02-27 : 12:22:29

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 14:31:30

SQL 2000 doeant have XML data type. So you should use OPENXML
see
http://www.databasejournal.com/features/mssql/article.php/2204421/XML-and-SQL-2000-Part-2.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-27 : 14:57:19

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]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ryan.gillies
Starting Member

27 Posts

Posted - 2014-02-28 : 04:21:08

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources