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.
| Author |
Topic |
|
madblackbirdbiker
Starting Member
2 Posts |
 Posted - 2012-11-26 : 11:58:07
|
| 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 encodingIt runs perfectly well in all other circumstances except when run as a job.Any ideas? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-26 : 12:19:30
|
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 belowDECLARE @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? |
 |
|
|
madblackbirdbiker
Starting Member
2 Posts |
Posted - 2012-11-29 : 03:11:10
|
| 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 |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-29 : 06:52:25
|
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. |
|
|
|
|
|
|
|
|
XML parsing error converting string to XML