Xml file show incorrect time data after exported it from gridview

Question

I have a gridview which display some information. One of those information is a time data like: 19:40:00.0000000. When I tried to export my gridview to xml file using WriteXml I found incorrect data for my time, it is look like : PT7H50M .

--> My time data : 19:40:00.0000000 in gridview converted to : PT7H50M after export it to xml file Why and How can I solve this problem ?

this is a time data in my gridview :

This is what it look like in xml file :

This is the code of export :

DataTable Rdt = new DataTable(); DataSet Rds = new DataSet();

                    Rdt = (DataTable)GV_Report.DataSource;
                    Rds.Tables.Add(Rdt.Copy());

                    Rds.WriteXml(@"c:\Reporting\Work_Hours_Report.xml", System.Data.XmlWriteMode.IgnoreSchema);

                    XmlDocument doc = new XmlDocument();
                    XmlWriterSettings settings = new XmlWriterSettings();
                    settings.Indent = true;
                    XmlWriter writer = XmlWriter.Create(@"c:\Reporting\ReportType.xml", settings);
                    writer.WriteStartDocument();
                    writer.WriteComment("This file is generated by the program...Please do not change this file!!");
                    writer.WriteStartElement("ReportBut");
                    writer.WriteElementString("ButType", "Work_Hours_Report");
                    writer.WriteEndElement();
                    writer.WriteEndDocument();
                    writer.Flush();
                    writer.Close();

I tried to edit my SQL statement by adding Aliases to each selected columns but unfortunately it does not make any improvement :

select CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST(ISNULL([Total_H],'00:00:00') AS DATETIME))),0) AS TIME) as '1',sum([HTotal]) as '2',sum([MTotal]) as '3',CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST(ISNULL([PH_Total],'00:00:00') AS DATETIME))),0) AS TIME) as '4',sum([PH]) as '5',sum([PM]) as '6',CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST(ISNULL([AH_Total],'00:00:00') AS DATETIME))),0) AS TIME) as '7',sum([AH]) as '8',sum([AM]) as '9'  FROM [QAMNI].[dbo].[tbl_WorkHours_Details]  where [Date] between '" + DF + "' and '" + DT + "' and [C_ID] ='" + txt_C_ID.Text + "'

Could you add your code to the question? This way other users will be able to analyse it and help you. — MustangXY, Sep 06 '15 at 22:51
Just to rule out the obvious.. what's the header of that column where the 19:40:00 is showing? Also, change your `XmlWriteMode` to `WriteSchema`, it might give you (and us) some clues. — Flynn1179, Sep 06 '15 at 23:07
My gridview is hidden from user interface the header of that column is unnamed by default it was named as Column1 by MVS...!! — Emperor, Sep 06 '15 at 23:13
Yeah, I thought so. The value of that column based on what I can see there is probably '06/09/2015 19:40:00'. Your grid view is showing the time, but the export is showing the date. As for the 'PT..' value, that's not coming from that column at all. — Flynn1179, Sep 06 '15 at 23:18
Please wait I will try to name all my selected SQL statement using Aliases. Hope this solve my problem. — Emperor, Sep 06 '15 at 23:23
Are you in Pacific Time Zone (PT)? It looks like the code is assuming the textbox is in UTC and converting the time to the timezone setting in your computer. — jdweng, Sep 06 '15 at 23:23
Side note: "P" is prefix for duration (http://www.datypic.com/sc/xsd/t-xsd_dayTimeDuration.html) - this may hint to what actually get serialized. — Alexei Levenkov, Sep 07 '15 at 00:55

score 0 · Accepted Answer · answered Sep 07 '15 at 07:01

According to the advance of Mr. @Flynn1179

I changed the XmlWriteMode from IgnoreSchema to WriteSchema and the result was correct without any xsd: dayTimeDuration represents duration of time expressed as a number of days, hours, minutes, and seconds. The format of xsd: dayTimeDuration is PnDTnHnMnS as in my Reporting file, once I change it I got the time exactly as I select from my database without any change in format. Thank you all of you for help. This is my XML file after changing the XmlWriteMode from IgnoreSchema to WriteSchema :

It still display as xsd format but when I print it out to end user using crystal report it give me correct time format as (“hh:mm:ss”) :

This is what I change in my code:(only changing the XmlWriteMode)

 Rds.WriteXml(@"c:\Reporting\WorkHours_Report.xml", System.Data.XmlWriteMode.WriteSchema);

Xml file show incorrect time data after exported it from gridview

1 Answers1