SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML parsing error converting string to XML
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madblackbirdbiker
Starting Member

United Kingdom
2 Posts

Posted - 11/26/2012 :  11:58:07  Show Profile  Reply with Quote
Hi, I have a very strange problem, I have a store proc which quering a webservice. This is dumped into a temp table as a nvarchar(max). I then read this table convert the nvarchar to XML to do some XML querying to get certain results.

This works perfectly fine when I execute the stored proc from SSMS. However when I run it as a job I get an error:
XML parsing: line 1, character 38, unable to switch the encoding

It runs perfectly well in all other circumstances except when run as a job.
Any ideas?

Edited by - madblackbirdbiker on 11/26/2012 12:05:13

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/26/2012 :  12:19:30  Show Profile  Reply with Quote
I don't know enough about your setup to say precisely why, but this most often happens when you have an encoding specified in the XML document, but the data type that you are using causes xquery to think that it is a different encoding. You will see what I mean if you run the two queries below
DECLARE @x NVARCHAR(200) = '<?xml version="1.0" encoding="UTF-8"?> <a>123</a>';
SELECT CAST(@x AS XML).query('a');

DECLARE @x NVARCHAR(200) = '<?xml version="1.0" encoding="UTF-8"?> <a>123</a>';
SELECT CAST(CAST(@x AS VARCHAR(200)) AS XML).query('a');
The first one gives you the error because the string specifies UTF-8 encoding, but the data type being NVARCHAR causes xquery to think that it is UTF-16. In the second query, where I am casting the string to VARCHAR before casting to XML, it works correctly.

Can you look through your code and/or set up to see if that type of thing is happening anywhere?
Go to Top of Page

madblackbirdbiker
Starting Member

United Kingdom
2 Posts

Posted - 11/29/2012 :  03:11:10  Show Profile  Reply with Quote
I have now resolved the problem, there appeared to be a carriage return line feed in the XML, which when stripped out it ran ok. The issue only appeared when I ran the stored proc from a job, If I ran the stored proc manually it worked fine. I will review the code with your comments in mind,

Many thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  06:52:25  Show Profile  Reply with Quote
Presence of a carriage return in XML data should not cause any problem, unless it was causing some of the rules such as node naming conventions were being violated or caused the XML to be not well-formed. For example see below:
DECLARE @x XML = '<a> abc' + CHAR(10) + CHAR(13)+ 'cde</a>';
SELECT @x;
SELECT @x.query('data(/a)');
However, if it has fixed the problem for you I have no further thoughts.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000