[RESOLVED] XML Type Change

[Shaggy Hiker]

I'm exporting a dataset as XML, including the schema, using the ACE.OLEDB.12.0 provider. I try to read that same XML into a dataset using the SQLClient provider, and all fields that are GUID fields are not recognized, which results in a "Column Requires a valid DataType" error.

The XML schema shows this for the GUID fields (except that the field names are different):

<xs:element name="SurveyID" msdata:DataType="System.Guid, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e" type="xs:string" minOccurs="0" />

I realize that JSON is the in thing, these days, but I have a couple different programs writing to this service. Changing everything over wouldn't be so easy.

Any suggestions?

[jmcilhinney]

What happens if you export a DataSet with Guids using SqlClient? You might just have to do a 'replace all' on the difference between export and import.

[Shaggy Hiker]

I'll have to give that a try. I'm also going to try exporting without the schema, as I'm not sure how much I really care about that.

[Shaggy Hiker]

The solution was on the reading end, and a bit involved. There are a variety of options for ReadXML in the XMLReadMode Enum. I had been using Auto, which worked well enough so long as the export and import were reasonably compatible. I had been exporting from Access using an older JET OleDB construct (I don't feel like looking up which). For a newer program, I had to switch to ACE.OLEDB.12.0, or perhaps it had to do with the change to Core from Framework. I didn't dig into it enough to be certain which was the more impactful change.

What stood out in the XML was that an Integer was just an Integer, a Boolean was just a Boolean, and so on, but a GUID was NOT just a GUID. There was considerable type information associated with it. That seemed wrong to me, as a GUID really is just a GUID.

Looking at the XMLReadMode Enum, almost every option seemed reasonable aside from the one I had been using. If the schema was ignored, inferred, or infer Type, all seemed likely to result in the reader choosing the right type for the GUID. That proved to be incorrect. In all cases, the type was inferred to be a string. I could have worked with that, as I could have used GUID.Parse to convert those fields to GUIDs, but...that didn't seem ideal.

Instead, I built up a destination Dataset such that it would have the right types, then used the IgnoreSchema option. That would be risky if there was any chance that the incoming data was not the right type. For example, if an incoming field was a different type than the local field of the same name. Fortunately, not only was I sure that I wouldn't have an issue with that, I was also sure that if I DID have an issue with that, the whole thing SHOULD fail. Failure would be the correct response, in that case.

This ended up working just fine. The local dataset had a GUID of whatever form it wanted, the incoming dataset had a GUID, the schema included in the incoming XML was ignored, and the GUID was interpreted to be a GUID, as it should be.