Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I want to get datetime columns as seperate rows which is now present as a single columnin an xmlstring.Given below is the xmlstring.

XML
<DocumentElement>
  <PartInfo>
    <ID>0</ID>
    <Name>QVR</Name>
    <PartNo>A11</PartNo>
    <SerialNo>453</SerialNo>
    <Parameter>C-11</Parameter>
    <InstalledDate>2013-02-04T17:16:56.383+05:30</InstalledDate>
    <InstalledTill>2013-02-15T17:16:56.3830837+05:30</InstalledTill>
  </PartInfo>
  <PartInfo>
    <ID>1</ID>
    <Name>EAFR</Name>
    <PartNo>B07</PartNo>
    <SerialNo>32</SerialNo>
    <Parameter>B-16</Parameter>
    <InstalledDate>2013-02-18T17:17:44.589+05:30</InstalledDate>
    <InstalledTill>2013-02-28T17:17:44.589+05:30</InstalledTill>
  </PartInfo>
</DocumentElement>



here i used forxml clause for extracting the installeddate.Then the installeddate columns are appearing as a single string.but i want them in seperate columns.
like
2013-02-04T17:16:56.383+05:30

2013-02-18T17:17:44.589+05:30


how can i do this using substring within this [PartInfo].query('.//InstalledTill').value('.','datetime') forxml clause..

Thanks in advance..
Lakshmi
Posted
Updated 15-Feb-13 0:59am
v2
Comments
sbromanv 15-Feb-13 11:19am    
you generate the xml from TSQL using for xml clause or you have to process the xml with TSQL and split the date?
Rovin_T_R 17-Feb-13 22:39pm    
Can u explain in detail about this......plzzzzz.......

Thanks in advance..
Lakshmi
Jegan Thiyagesan 18-Feb-13 11:37am    
Hi,
This is SQL time format, when you serialise this to DateTime object, .Net will automatically recognize that. then you can use that serialised DateTime object to get the correct sreing format you want.

Jegan
Rovin_T_R 19-Feb-13 5:57am    
Thank u.But i have to split the values in installeddate..plz tell how can i do this?

1 solution

Hi Here is your answer:

C#
using System;
using System.Xml.Serialization;
using System.IO;
using System.Xml;
using System.Text;

namespace Test
{
    [Serializable]
    [XmlRoot("DocumentElement")]
    public class Documentelement
    {
        [XmlElement]
        public PartInfo[] PartInfo { get; set; }
    }

    public class PartInfo
    {
        [XmlElement]
        public int ID { get; set; }
        public string Name { get; set; }
        public string PartNo { get; set; }
        public int SerialNo { get; set; }
        public string Parameter { get; set; }        
        public DateTime InstallDate { get; set; }
        public DateTime InstallTill { get; set; }
    }

    public class Test
    {

        private PartInfo details_1()
        {
            PartInfo details = new PartInfo
            {
                ID = 0,
                Name = "QVR",
                PartNo = "A11",
                SerialNo = 453,
                Parameter = "C -11",

                // This you should add as date time,  I just used the string to parse your time that you showed in your example.
                InstallDate = DateTime.Parse("2013-02-04T17:16:56.383+05:30"),
                InstallTill = DateTime.Parse("2013-02-15T17:16:56.3830837+05:30")
            };
            return details;
        }

        private PartInfo details_2()
        {
            PartInfo details = new PartInfo
            {
                ID = 1,
                Name = "EAFR",
                PartNo = "B07",
                SerialNo = 32,
                Parameter = "B-16",

                // This you should add as date time,  I just used the string to parse your time that you showed in your example.
                InstallDate = DateTime.Parse("2013-02-18T17:17:44.589+05:30"),
                InstallTill = DateTime.Parse("2013-02-28T17:17:44.589+05:30")
            };
            return details;
        }

        public void setXmlValues()
        {            
            Documentelement testOut = new Documentelement { PartInfo = new[] { details_1(), details_2() }};

            xml_serialise(testOut);

            Documentelement testIn = xml_deserialise();
            int val = testIn.PartInfo[0].ID;
            DateTime dt = testIn.PartInfo[0].InstallDate;
            string shortTime = dt.ToShortTimeString();

        }


        public void xml_serialise(Documentelement test)
        {
            XmlSerializer ser = new XmlSerializer(typeof(Documentelement));


            using (TextWriter writer = new StreamWriter("test.xml"))
            {
                ser.Serialize(writer, test);
            }
        }

        public Documentelement xml_deserialise()
        {
            XmlSerializer ser = new XmlSerializer(typeof(Documentelement));

            Documentelement test;

            using (TextReader writer = new StreamReader("test.xml"))
            {
                test = (Documentelement)ser.Deserialize(writer);
            }

            return test;
        }
    }
}



I hope this helps.

Regards
Jegan
 
Share this answer
 
Comments
Rovin_T_R 22-Feb-13 0:04am    
Thanks..But i dnt want to serialize and deserialize the xmlstring that are already done.i just want to split the values in installed date or installed till tag if more than one installeddate tag is present.using substring in sql query..how can i do this?Plz help me..

Thanks in advance
L@kshmi
Jegan Thiyagesan 22-Feb-13 4:39am    
I didn't quite understand why do you want to split the date in sql query when you already have a deserialized object. you just have to get that object and pick the date format you want such as

DateTime dt = testIn.PartInfo[0].InstallDate;
string shortTime = dt.ToShortTimeString();

If you are not using the deserializer, then sql.SELECT will give you that date value as string, so you can split on string as

string installedDate = "2013-02-04T17:16:56.383+05:30";
string[] parts = installedDate.Split(new char[] { 'T' });
string date = parts[0];
string time = parts[1]; // remember this includes the +05:30 at the end, you have to take care of this.

Regards
Jegan

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900