在sql中的xmlstring中拆分多个datetime列 [英] to split multiple datetime column in xmlstring in sql

问题描述

我希望将datetime列作为单独的行获取，现在作为xmlstring中的单个列存在。下面是xmlstring。

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.

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

这里我使用forxml子句来解压mountdate.Then installeddate列显示为单个字符串。但是我希望他们分开列。

喜欢

2013-02-04T17：16：56.383 + 05:30



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





如何使用[PartInfo] .query（''.// InstalledTill''）中的子字符串来执行此操作。值（''。'' ，''datetime''）forxml子句..



提前致谢..

Lakshmi

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

推荐答案

您好b $ b以下是您的答案：

Hi Here is your answer:

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;
        }
    }
}

我希望这会有所帮助。



问候

Jegan

I hope this helps.

Regards
Jegan

这篇关于在sql中的xmlstring中拆分多个datetime列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！