Newbie: XML Date FormatException

  • Thread starter Thread starter Beth C
  • Start date Start date
B

Beth C

I am using DTS to create an XML file including a DateTime field from a
SQL Server 2000 database. I'm trying to use ReadXMLSchema and ReadXML
to load a dataset based on this file. I need the date field to be
recognized as a date field in the new dataset so that I can sort by an
actual date instead of the date's string value. (Pretty standard
stuff.) I created the schema by reading in the data without a schema
and then writing the schema of the dataset using WriteXMLSchema. As
suspected, all fields were inferred to be strings. When I read the
data in using this schema, the data is read as expected. If I change
the element containing the date field to a dateTime type, I get a
FormatException error.

I've included the pertinent code below. If this isn't sufficient, I
can post whatever is necessary to track the problem.

Any insights appreciated.



Code that creates XML file:

Set elDataSet = xmlDoc.createElement("NewDataSet")
xmlDoc.AppendChild elDataSet

Set elFProjInfo = xmlDoc.createElement("FProjInfo")
elDataSet.AppendChild elFProjInfo

Set elProjDate = xmlDoc.createElement("ProjDate")
elFProjInfo.appendChild elProjDate
Set txtProjDate = xmlDoc.createTextNode(oRS("ProjDate")
elProjDate.AppendChild txtProjDate


Schema file:

<xs:element name="ProjDate" type="xs:dateTime" minOccurs="0" />


XML file:

- <FProjInfo>
<FProjID>1700</FProjID>
<BlockID>3</BlockID>
<ProjDate>12/28/2003</ProjDate>
<GWSS>True</GWSS>
<Bins>150</Bins>
<Util>0.8</Util>
<Export>0</Export>
<Fancy>0.8</Fancy>
<Choice>0.2</Choice>
<Notes />
<size36 />
<size40 />
<size48>0.15</size48>
<size56>0.2</size56>
<size72>0.3</size72>
<size88>0.2</size88>
<size113>0.15</size113>
<size138>0</size138>
<size163>0</size163>
<size180 />
<Brix />
<Ratio />
<Location />
<FldBoxes />
</FProjInfo>


Code that Reads XML:

Public Const gsInfoFile =
"\\Svr\ClientFiles\Client\TestXML\HHFieldOpsInfo.xml"
Public Const gsInfoSchema =
"\\SvrName\Clientfiles\Client\TestXML\HHSchema.xsd"


ds.ReadXmlSchema(gsInfoSchema)
ds.ReadXml(gsLocalInfoFile)
 
I wonder, what happens if you change the schema element to
<xs:element name="ProjDate" type="xs:date" minOccurs="0" />
 
Alex,

Thanks for the suggestion, but I'm afraid the results are the same:
"FormatException"
 
I'm not sure if it's documented anywhere, but when you specify xs:date or
xs:dateTime, internally DataSet.ReadXml ends up using
XmlConvert.ToDateTime() with default set of date formats. The default set of
date formats is hardcoded and looks like this:

"yyyy-MM-dd"
"yyyy-MM-ddZ"
"yyyy-MM-ddzzzzzz"
"yyyy-MM-ddTHH:mm:ss"
"yyyy-MM-ddTHH:mm:ss.f"
"yyyy-MM-ddTHH:mm:ss.ff"
"yyyy-MM-ddTHH:mm:ss.fff"
"yyyy-MM-ddTHH:mm:ss.ffff"
"yyyy-MM-ddTHH:mm:ss.fffff"
"yyyy-MM-ddTHH:mm:ss.ffffff"
"yyyy-MM-ddTHH:mm:ss.fffffff"

Try changing date format to "2003-12-28"
 
Alex,

Thanks so much. Changing the date format in the XML file did the trick.

Do you know if there's a way to force a particular date format when the
dataset is read or displayed? I'm using "type = xs:date" in the schema.
I'm using the yyyy-MM-dd format in the XML file, but when I use the
acquired dataset as a datasource for my combobox, the time portion of
the date is displayed as well as the date portion.

Thanks for any suggestions.
 
Back
Top