sql server - XML generation using c#

Question

Welcome To Ask or Share your Answers For Others

sql server - XML generation using c#

asked Jan 31, 2022 in Technique[技术] by 深蓝 (71.8m points)

sql server - XML generation using c#

My goal is to generate xml structure in this format

<?xml version='1.0' encoding='UTF-8'?>
<return xmlns:rmas="value here" xmlns:xsi="value here" xsi:noNamespaceSchemaLocation="value here">
   <header>
      <return-code>""</return-code>
      <return-desc>""</return-desc>
      <as-at-date>""</as-at-date>
      <operator-code>""</operator-code>
   </header>
   <body>
         <scheme>
           <code>10050</code>
              <employer>
                    <empr-code></empr-code>
                    <data>
                       <serial-no />
                       <pin />
                       <employer-contribution />
                       <employee-contribution />
                       <voluntary-contribution />
                       <total-contribution />
                    </data>
               </employer>
        </scheme>
        <scheme>
           <code>10100</code>
              <employer>
                    <empr-code></empr-code>
                    <data>
                       <serial-no />
                       <pin />
                       <employer-contribution />
                       <employee-contribution />
                       <voluntary-contribution />
                       <total-contribution />
                    </data>
               </employer>
          </scheme>
          [...]
    </body>
  </return>

This is the sample of the data I am consuming

scheme-code emp-code        pin            empr-contr   empyee-contr    total   total-vol-cont
10050       PR0000395010    PEN200386572133 54777.28    43821.82       108599.1  10000  
10050       PR0000679771    PEN200629902715 65528.34    0              215528.34 150000 
10050       PR0000007340    PEN200629902715 0           65528.34       215528.34 150000 
10050       PU000035E001    PEN100786299723 10570.34    10570.34       21140.68  0      
10050       TCF000615630    PEN100786299723 12060.15    12060.16       24120.31  0      
10050       TCF000615630    PEN100786299723 12204.98    12204.99       24409.97  0      
10050       PR0000615630    PEN100144364216 10945.19    13681.49       24626.68  0      
10050       PR0000615630    PEN100453089112 14319.32    17899.15       32218.47  0      
10050       PR0000615630    PEN200742682512 13116.33    16395.41       29511.74  0      
10100       PRTEMP005022    PEN100940140007 792         990            1782      0  
10100       PRTEMP005022    PEN100799131715 2375        2970           5345      0
10100       PRTEMP005022    PEN100799212715 831.6       1039.5         1871.1    0

In the body tag, I'd like to group the data first by the scheme-code and inside the code, group the data by the emp-code in that scheme and then by the data(pin, empr-contr,empyee-contr...) having the empr-code. The serial-no(int) for each data tag will be 0,1,2,3....T999 depending on the number of data tags in the parent employer tag. Please see further example below

<scheme>
  <code>10050</code>
  [...]
  <employer>
      <empr-code>PR0000615630</empr-code>
         <data>
            <serial-no>1</serial-no>
            <pin>PEN100144364216</pin>
            <employer-contribution>10945.19</employer-contribution>
            <employee-contribution>13681.49</employee-contribution>
            <voluntary-contribution>0.00</voluntary-contribution>
            <total>32218.47</total>
         </data>
         <data>
            <serial-no>2</serial-no>
            <pin>PEN100453089112</pin>
            <employer-contribution>14319.32</employer-contribution>
            <employee-contribution>17899.15/employee-contribution>
            <voluntary-contribution>0.00</voluntary-contribution>
            <total>32218.47</total>
         </data>
      <data>
         <serial-no>T9999</serial-no>
         <pin>PEN200742682512</pin>
         <employer-contribution>13116.33</employer-contribution>
         <employee-contribution>16395.41</employee-contribution>
         <voluntary-contribution>0.00</voluntary-contribution>
         <total>29511.74</total>
      </data>
 </employer>
 [...]
</scheme>

How can I generate it using XMLDocument or XMLWriter

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

1 Answer

深蓝 · Answer 1 · 2022-01-31T07:26:46+0000

Try following xml linq. I put your input file into a text file and then read into DataTable. Then create XML from table :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {

        const string INPUT_FILENAME = @"c:	emp	est.txt";
        const string OUTPUT_FILENAME = @"c:	emp	est.xml";
        static DataTable dt = new DataTable();
        static XDocument doc;
        static void Main(string[] args)
        {
            ReadData(INPUT_FILENAME);
            dt = dt.AsEnumerable()
                .OrderBy(x => x.Field<string>("scheme-code"))
                .ThenBy(x => x.Field<string>("emp-code"))
                .ThenBy(x => x.Field<string>("pin"))
                .CopyToDataTable();

            CreateXml();
            doc.Save(OUTPUT_FILENAME);

        }
        static void ReadData(string filename)
        {
            int rowNumber = 0;
            string line = "";
            StreamReader reader = new StreamReader(INPUT_FILENAME);

            while ((line = reader.ReadLine()) != null)
            {
                string[] splitData = line.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray();

                if (++rowNumber == 1)
                {
                    for (int i = 0; i < splitData.Length; i++)
                    {
                        if (i < 3)
                        {
                            dt.Columns.Add(splitData[i], typeof(string));
                        }
                        else
                        {
                            dt.Columns.Add(splitData[i], typeof(decimal));
                        }
                    }
                }
                else
                {
                    DataRow newRow = dt.Rows.Add();
                    for (int i = 0; i < splitData.Length; i++)
                    {
                        if (i < 3)
                        {
                            newRow[i] = splitData[i];
                        }
                        else
                        {
                            newRow[i] = decimal.Parse(splitData[i]);
                        }
                    }
                }
            }
            reader.Close();
        }
        static void CreateXml()
        {
            string xmlns_rmas = "value here";
            string xmlns_xsi = "value here";
            string xmlns_noNamespaceSchemaLocation = "value here";
            string xmlIdentFormat =
                "<?xml version='1.0' encoding='UTF-8'?>" +
                "<return" +
                    " xmlns:rmas="{0}"" +
                    " xmlns:xsi="{1}"" +
                    " xsi:noNamespaceSchemaLocation="{2}">" +
                "</return>";
            string xmlIdent = string.Format(xmlIdentFormat, xmlns_rmas, xmlns_xsi, xmlns_noNamespaceSchemaLocation);
            doc = XDocument.Parse(xmlIdent);

            XElement _return = doc.Root;

            string returnCode = "";
            string returnDesc = "";
            DateTime date = DateTime.Now;
            string operatorCode = "";
            XElement header = new XElement("header", new object[] {
                new XElement("return-code", returnCode),
                new XElement("return-desc", returnDesc),
                new XElement("as-at-date", date),
                new XElement("operator-code", operatorCode)
            });
            _return.Add(header);

            XElement body = new XElement("body");
            _return.Add(body);


            foreach(var schemeGroup in dt.AsEnumerable().GroupBy(x => x.Field<string>("scheme-code")))
            {
                XElement scheme = new XElement("scheme");
                body.Add(scheme);
                XElement code = new XElement("code", schemeGroup.Key);
                scheme.Add(code);
                foreach(var empCodeGroup in schemeGroup.GroupBy(y => y.Field<string>("emp-code")))
                {
                    XElement employer = new XElement("employer");
                    scheme.Add(employer);
                    int serialNumber = 0;

                    foreach(var pinGroup in empCodeGroup.GroupBy(y => y.Field<string>("pin")))
                    {
                        if (serialNumber == 0)
                        {
                            XElement emprCode = new XElement("empr-code", empCodeGroup.Key);
                            employer.Add(emprCode);
                        }
                        foreach (DataRow row in pinGroup)
                        {

                            XElement data = new XElement("data");
                            employer.Add(data);
                            if ((empCodeGroup.Count() > 1) && (serialNumber == empCodeGroup.Count() - 1))
                            {
                                data.Add(new XElement("serial-no", "T999"));
                            }
                            else
                            {
                                data.Add(new XElement("serial-no", serialNumber));
                            }

                            data.Add(new XElement("pin", pinGroup.Key));
                            data.Add(new XElement("employer-contribution", row.Field<decimal>("empr-contr")));
                            data.Add(new XElement("employee-contribution", row.Field<decimal>("empyee-contr")));
                            data.Add(new XElement("voluntary-contribution", row.Field<decimal>("total-vol-cont")));
                            data.Add(new XElement("total-contribution", row.Field<decimal>("total")));

                            serialNumber++;
                        }

                    }
                }
            }
        }
    }

}

Categories

sql server - XML generation using c#

sql server - XML generation using c#

Please log in or register to add a comment.

Please log in or register to answer this question.

1 Answer

Please log in or register to add a comment.

Just Browsing Browsing

Most popular tags