Search code examples
c#xmlgridviewtimewritexml

Xml file show incorrect time data after exported it from gridview

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 : My time data in gridview

This is what it look like in xml file :

enter image description here

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 + "'
Solution

  • 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 :

    enter image description here

    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”) :

    enter image description here

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

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