0

We have the data fetched from sqlserver and been placed in a xml format using C#. But, unexpectedly the duplicate records are inserted into xml node with comma delimiters. I want the duplicate data form as a new node. starting from the parent node. please suggest me some logic.

VRaju
  • 21
  • 1
  • 8

1 Answers1

0

Try this

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //string xml =
            //    "<Root>" +
            //        "<Employee>" +
            //            "<id>001</id>" +
            //            "<Name>Sam</Name>" +
            //        "</Employee>" +
            //        "<Employee>" +
            //            "<id>002</id>" +
            //            "<Name>john,Donald</Name>" +
            //        "</Employee>" +
            //    "</Root>";


            string connStr = "Enter Your connection string here";
            string SQL = "Enter your SQL Here";
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(SQL, conn);

            //if code is a single xml
            string xml = cmd.ExecuteScalar().ToString();

            XDocument doc = XDocument.Parse(xml);

            List<XElement> duplicates = doc.Descendants("Employee").Where(x => x.Element("Name").Value.Contains(",")).ToList();

            foreach (XElement duplicate in duplicates)
            {
                string[] names = duplicate.Element("Name").Value.Split(new char[] { ',' });
                List<XElement> newEmployees = new List<XElement>();
                foreach (string name in names)
                {
                    XElement newEmployee = XElement.Parse(duplicate.ToString());
                    XElement nameElement = newEmployee.Element("Name");
                    nameElement.Value = name;
                    newEmployees.Add(newEmployee);
                }
                duplicate.ReplaceWith(newEmployees);
            }

        }
    }
}
​
jdweng
  • 33,250
  • 2
  • 15
  • 20
  • Hi, The above code's logic would be fine. But, the xml part cannot be hardcoded as it comes from sql data base for us. Can it be made dynamic? – VRaju Dec 16 '15 at 06:21
  • Use XDocument.Load(filename) instead of XDocument.Parse(xml). Or replace string xml with any string. I just hardcoded the string for testing. – jdweng Dec 16 '15 at 08:16
  • Thank you Jdweng. But, my input source is is sqlsever table. – VRaju Dec 16 '15 at 11:56
  • Is the xml stored as a single xml? What table? What column? Updated code. – jdweng Dec 16 '15 at 16:41
  • Hi, we have a validation as shown below which just checks if the string should not be null or empty. can you please suggest me if at all the name node is having the "," seperator script should be able to start writing from the parent node i.e. "Employee" node. if (!string.IsNullOrEmpty(argument.Named)) { xmlNode = doc.CreateNode(XmlNodeType.Element, "name", ""); xmlNode.InnerText = argument.Named; nodeCalcArg.AppendChild(xmlNode); } – VRaju Dec 24 '15 at 05:54
  • I have two suggestions. 1) To recursively parse the xml like on this posting : http://stackoverflow.com/questions/28976601/recursion-parsing-xml-file-with-attributes-into-treeview-c-sharp 2) Use a textreader like this : http://stackoverflow.com/questions/34274568/how-to-read-an-xml-file-by-using-xmlreader-in-c-sharp/34276761#34276761 – jdweng Dec 24 '15 at 06:41