469,630 Members | 1,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,630 developers. It's quick & easy.

XML encoding problems when storing to sql 2005 database.

16
Hello

I have searched through this forum and it seems some people are having similar problems to me but none of the fixes are fixing mine..! :(

I have a table in my database that has two xml fields.

I have two bits of generated XML I want to store.. the first one has
"<?xml version="1.0" encoding="utf-8" ?>" as its declaration and the second has "<?xml version="1.0" encoding="utf-16" ?>"

First time round these both get added to the database fine, with no errors. If I go back and try and edit I then get problems!

The xml gets generated again in the same function as it is generated the first time when it is added to the database, but this time the first one updates with no errors, but the second gives the error "XML parsing: line 1, character 38, unable to switch the encoding".

From what I have read it seems that SQL Server must have the xml encoded as UTF-16. If this is the case, I don't understand why I have no problems with my UTF-8 xml, but do with my UTF-16!

[EDIT] I have tried changing the UTF setting on the file in debug mode and the second one will insert if I change the encoding to UTF-8 when I do the update, but it gives the same error if I leave it as that when I try and insert the first time


Is there anyway of seeing what the encoding really is on my xml, to see if it is different to what it says in the declaration? And then to convert it to UTF-16 as it should be?

Thanks so much in advance

Bex
Sep 26 '07 #1
15 5729
davef
98
Hello

I have searched through this forum and it seems some people are having similar problems to me but none of the fixes are fixing mine..! :(

I have a table in my database that has two xml fields.

I have two bits of generated XML I want to store.. the first one has
"<?xml version="1.0" encoding="utf-8" ?>" as its declaration and the second has "<?xml version="1.0" encoding="utf-16" ?>"

First time round these both get added to the database fine, with no errors. If I go back and try and edit I then get problems!

The xml gets generated again in the same function as it is generated the first time when it is added to the database, but this time the first one updates with no errors, but the second gives the error "XML parsing: line 1, character 38, unable to switch the encoding".

From what I have read it seems that SQL Server must have the xml encoded as UTF-16. If this is the case, I don't understand why I have no problems with my UTF-8 xml, but do with my UTF-16!

[EDIT] I have tried changing the UTF setting on the file in debug mode and the second one will insert if I change the encoding to UTF-8 when I do the update, but it gives the same error if I leave it as that when I try and insert the first time


Is there anyway of seeing what the encoding really is on my xml, to see if it is different to what it says in the declaration? And then to convert it to UTF-16 as it should be?

Thanks so much in advance

Bex
How do you read the XML back from the database for edit? Code, please.
Sep 26 '07 #2
Bexm
16
Hi

I get it from the database as a string and then go

Expand|Select|Wrap|Line Numbers
  1.   XmlDocument xmlDoc = new XmlDocument();
  2.   xmlDoc.LoadXml(ProductXml);
  3.  
The new xml that is produced looks identicle to the old xml, as I have tried saving without making any changes.

Something to note, I am using LLBLGEN to deal with all database stuff..


Thanks

Becky
Sep 26 '07 #3
davef
98
Hi

I get it from the database as a string and then go

Expand|Select|Wrap|Line Numbers
  1.   XmlDocument xmlDoc = new XmlDocument();
  2.   xmlDoc.LoadXml(ProductXml);
  3.  
The new xml that is produced looks identicle to the old xml, as I have tried saving without making any changes.

Something to note, I am using LLBLGEN to deal with all database stuff..


Thanks

Becky
Does it crap out on LoadXml()?
Sep 26 '07 #4
Bexm
16
Does it crap out on LoadXml()?
No it loads the XML just fine, its just when it tries re-saving it to the DB..
Sep 26 '07 #5
davef
98
No it loads the XML just fine, its just when it tries re-saving it to the DB..
How do you do writing it back to the database?
Sep 26 '07 #6
Bexm
16
How do you do writing it back to the database?
I use llblGen..

This would be my code, basically I am telling it that my field ProductXML = the new xml and it stucks it in the database.

Expand|Select|Wrap|Line Numbers
  1.  
  2.   public static void SetJdf(Guid itemId, string productXml)
  3.         {
  4.             BasketItemEntity basketItem = new BasketItemEntity(itemId);
  5.  
  6.             using (DataAccessAdapter adapter = new DataAccessAdapter())
  7.             {
  8.                 adapter.FetchEntity(basketItem);
  9.                 basketItem.ProductXml = productXml;
  10.                 adapter.SaveEntity(basketItem);
  11.             }
  12.         }
  13.  

I think its some how somthing to do with the encoding before it even reaches this point?
Sep 26 '07 #7
davef
98
I use llblGen..

This would be my code, basically I am telling it that my field ProductXML = the new xml and it stucks it in the database.

Expand|Select|Wrap|Line Numbers
  1.  
  2.   public static void SetJdf(Guid itemId, string productXml)
  3.         {
  4.             BasketItemEntity basketItem = new BasketItemEntity(itemId);
  5.  
  6.             using (DataAccessAdapter adapter = new DataAccessAdapter())
  7.             {
  8.                 adapter.FetchEntity(basketItem);
  9.                 basketItem.ProductXml = productXml;
  10.                 adapter.SaveEntity(basketItem);
  11.             }
  12.         }
  13.  

I think its some how somthing to do with the encoding before it even reaches this point?
Hmm, I assume you get a nice exception in adapter.SaveEntity(basketItem); Is that correct? Not being sure how LLBLGenPro DataAccessAdapter class is internally implemented, but try to test saving XML with a .NET Data objects like SqlCommand. Just to see if it makes a difference for the same XML string.
Sep 26 '07 #8
Bexm
16
Hmm, I assume you get a nice exception in adapter.SaveEntity(basketItem); Is that correct? Not being sure how LLBLGenPro DataAccessAdapter class is internally implemented, but try to test saving XML with a .NET Data objects like SqlCommand. Just to see if it makes a difference for the same XML string.
I tried copying the xml from the immeidate window and did an update query in sql server 2005 and get the same error!
Sep 26 '07 #9
davef
98
I tried copying the xml from the immeidate window and did an update query in sql server 2005 and get the same error!
I bet ya you can hardly browse your utf-16 encoded XML with IE, can you? However, I was able to insert a utf-16 encoded XML into a database OK via the Management Studio. Here's my XML:
Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="utf-16" ?>
  2. <documents>
  3. <document type="Word" size="20k">Word Document</document>
  4. <document type="Excel" size="30k">Excel Document</document>
  5. </documents>
  6.  
Sep 26 '07 #10
Bexm
16
I bet ya you can hardly browse your utf-16 encoded XML with IE, can you? However, I was able to insert a utf-16 encoded XML into a database OK via the Management Studio. Here's my XML:
Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="utf-16" ?>
  2. <documents>
  3. <document type="Word" size="20k">Word Document</document>
  4. <document type="Excel" size="30k">Excel Document</document>
  5. </documents>
  6.  

can you run an update query on it? I have tried directly updating it again using the exact same xml that is entered in the first place and I still get the error!
Sep 26 '07 #11
davef
98
can you run an update query on it? I have tried directly updating it again using the exact same xml that is entered in the first place and I still get the error!
I can certainly do that no problem. What's your XML that you're entering?
Sep 26 '07 #12
Bexm
16
This is a very cut down example of the xml I am using. It still doesn't work with this though either...

Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="utf-16" standalone="yes"?>
  2. <order>
  3.   <JDF></JDF>
  4.   <part caption="" type="body">
  5.     <control  mandatory="0" visible="1">
  6.       <loc lang="en-gb" value="Collated Sets" />
  7.       <control type="dropdown" icon="" mandatory="0" visible="1">
  8.         <loc lang="en-gb" value="Paper size" />
  9.         <options selected="My option">
  10.           <option name="My option"  selected="False" imageurl="">
  11.             <JDF>
  12.  
  13.             </JDF>
  14.           </option>
  15.  
  16.         </options>
  17.       </control>
  18.     </control>
  19.   </part>
  20. </order>
Sep 27 '07 #13
davef
98
This is a very cut down example of the xml I am using. It still doesn't work with this though either...

Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="utf-16" standalone="yes"?>
  2. <order>
  3.   <JDF></JDF>
  4.   <part caption="" type="body">
  5.     <control  mandatory="0" visible="1">
  6.       <loc lang="en-gb" value="Collated Sets" />
  7.       <control type="dropdown" icon="" mandatory="0" visible="1">
  8.         <loc lang="en-gb" value="Paper size" />
  9.         <options selected="My option">
  10.           <option name="My option"  selected="False" imageurl="">
  11.             <JDF>
  12.  
  13.             </JDF>
  14.           </option>
  15.  
  16.         </options>
  17.       </control>
  18.     </control>
  19.   </part>
  20. </order>
I did a direct update query with this XML via the Mgmt studio no problem at all.
Sep 27 '07 #14
Bexm
16
I did a direct update query with this XML via the Mgmt studio no problem at all.

Really? Did you add it into the db in mgmt studio first then do an update?
I'm really confused now!
Sep 27 '07 #15
davef
98
Really? Did you add it into the db in mgmt studio first then do an update?
I'm really confused now!
I did it OK without a problem.
Sep 27 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

37 posts views Thread by chandy | last post: by
1 post views Thread by Lopezd9 | last post: by
8 posts views Thread by pabv | last post: by
23 posts views Thread by Allan Ebdrup | last post: by
8 posts views Thread by Erwin Moller | last post: by
17 posts views Thread by raymond_b_jimenez | last post: by
1 post views Thread by Alexander Adam | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.