XML encoding problems when storing to sql 2005 database.

[Bexm]

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

[davef]

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.

[Bexm]

Hi

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

  XmlDocument xmlDoc = new XmlDocument();
  xmlDoc.LoadXml(ProductXml);
 

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

[davef]

Hi

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

  XmlDocument xmlDoc = new XmlDocument();
  xmlDoc.LoadXml(ProductXml);
 

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()?

[Bexm]

Does it crap out on LoadXml()?

No it loads the XML just fine, its just when it tries re-saving it to the DB..

[davef]

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?

[Bexm]

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.

 
  public static void SetJdf(Guid itemId, string productXml)
        {
            BasketItemEntity basketItem = new BasketItemEntity(itemId);
 
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(basketItem);
                basketItem.ProductXml = productXml;
                adapter.SaveEntity(basketItem);
            }
        }
 

I think its some how somthing to do with the encoding before it even reaches this point?

[davef]

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.

 
  public static void SetJdf(Guid itemId, string productXml)
        {
            BasketItemEntity basketItem = new BasketItemEntity(itemId);
 
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(basketItem);
                basketItem.ProductXml = productXml;
                adapter.SaveEntity(basketItem);
            }
        }
 

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.

[Bexm]

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!

[davef]

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:

<?xml version="1.0" encoding="utf-16" ?>
<documents>
<document type="Word" size="20k">Word Document</document>
<document type="Excel" size="30k">Excel Document</document>
</documents>
 

[Bexm]

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:

<?xml version="1.0" encoding="utf-16" ?>
<documents>
<document type="Word" size="20k">Word Document</document>
<document type="Excel" size="30k">Excel Document</document>
</documents>
 

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!

[davef]

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?

[Bexm]

This is a very cut down example of the xml I am using. It still doesn't work with this though either...

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<order>
  <JDF></JDF>
  <part caption="" type="body">
    <control  mandatory="0" visible="1">
      <loc lang="en-gb" value="Collated Sets" />
      <control type="dropdown" icon="" mandatory="0" visible="1">
        <loc lang="en-gb" value="Paper size" />
        <options selected="My option">
          <option name="My option"  selected="False" imageurl="">
            <JDF>
 
            </JDF>
          </option>
 
        </options>
      </control>
    </control>
  </part>
</order>

[davef]

This is a very cut down example of the xml I am using. It still doesn't work with this though either...

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<order>
  <JDF></JDF>
  <part caption="" type="body">
    <control  mandatory="0" visible="1">
      <loc lang="en-gb" value="Collated Sets" />
      <control type="dropdown" icon="" mandatory="0" visible="1">
        <loc lang="en-gb" value="Paper size" />
        <options selected="My option">
          <option name="My option"  selected="False" imageurl="">
            <JDF>
 
            </JDF>
          </option>
 
        </options>
      </control>
    </control>
  </part>
</order>

I did a direct update query with this XML via the Mgmt studio no problem at all.

[Bexm]

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!

[davef]

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.