Solved

Help with deleting data in xml file

Posted on 2014-11-19
15
96 Views
Last Modified: 2014-11-22
Hi,


I have multiple xml files in multiple folders in my application's "Data" sub folder. How do I loop through each folder and delete all records in the xml files except for records with <ID>0</ID>. I am trying the clear values in xml files in multiple folders.

Thanks,

Victor
0
Comment
Question by:vcharles
  • 8
  • 4
  • 3
15 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
What is the structure of the XML, and what denotes a "record" in your document?
0
 

Author Comment

by:vcharles
Comment Utility
Hi,

The structure looks like the following:

<Root>
<TableNSC>
<ID>0</ID>
<ItemA></ItemA>
<ItemA></ItemA>
<ItemA></ItemA>
</TableNSC>
<TableNSC>
<ID></ID>
<ItemA>1</ItemA>
<ItemA></ItemA>
<ItemA></ItemA>
</TableNSC>
<TableNSC>
<ID>2</ID>
<ItemA></ItemA>
<ItemA></ItemA>
<ItemA></ItemA>
</TableNSC>
</Root>

Victor
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Assuming your XML structures are all the same you could try:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string rootDirectory = @"C:\temp\xml";

            var files = from file in Directory.GetFiles(rootDirectory, "*.xml", SearchOption.AllDirectories)
                        select file;

            foreach(var file in files)
            {
                XDocument doc = XDocument.Load(file);
                var nodes = from node in doc.Descendants("TableNSC")
                            where node.Element("ID").Value != "0"
                            select node;

                nodes.Remove();
                doc.Save(file);
            }
        }
    }
}

Open in new window

0
 

Author Comment

by:vcharles
Comment Utility
Hi,

Can you please send me the solution in VB.NET?.

Thanks,

Victor
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Sorry, thought i'd got here from a C# topic for some reason.

VB version below:
Imports System.Data
Imports System.Linq
Imports System.Xml.Linq
Imports System.IO

Module Module1

    Sub Main()

        Dim rootDirectory As String = "C:\temp\xml"

        Dim files = From file In Directory.GetFiles(rootDirectory, "*.xml", SearchOption.AllDirectories)
                    Select file

        For Each file In files
            Dim doc As XDocument = XDocument.Load(file)
            Dim nodes = From node In doc.Descendants("TableNSC")
                        Where node.Element("ID") <> "0"
                        Select node

            nodes.Remove()
            doc.Save(file)
        Next

    End Sub

End Module

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Hi Victor;

Are all the table names in the XML document in the folder have the node name TableNSC or is it more like TableXXX where XXX is some other characters?
0
 

Author Comment

by:vcharles
Comment Utility
Hi Fernando,

It is more like TableXXX, for example I have a folder named MasterNSC and files with name LinkFinalNSCUSA.xml,  LinkFinalNSCBEL.xml,and the table name for both files is NSC.

Thanks,

Victor
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:vcharles
Comment Utility
Hi,

I will try your code and get back to you.

Thanks for converting to VB.NET,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
So all XML files in the folder/s have the table names as <TableNSC />?
0
 

Author Comment

by:vcharles
Comment Utility
No,  table NSC is only for folder name MasterNSC, I also have folders such as MasterNSN, MasterFIF etc.. where tables names are NSN, FIF.
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 250 total points
Comment Utility
Hi Victory;

See if this is what you are looking for.

'' The main directory that holds all the other directories
'' Modify the directory path to fit your needs
Dim mainDirPath As String = "C:\Working Directory\temp\"
'' Get an array of directories that you will look into 
'' to modify the XML files
Dim dirs = Directory.GetDirectories(mainDirPath)

'' Iterate through all the directories
For Each thisDir In dirs
    '' Select all the XML files and modify them
    For Each xmlFile In Directory.GetFiles(thisDir, "*.xml")
        '' Open each document
        Dim xdoc As XDocument = XDocument.Load(xmlFile)
        '' Query the document and find all elements that are not "ID' = to 0
        Dim tables As List(Of XElement) = (From ele In xdoc.Descendants()
                                           Where ele.Name.LocalName.StartsWith("Table") AndAlso ele.Element("ID").Value <> "0"
                                           Select ele).ToList()

        '' Remove all unwanted elements from the main document
        tables.Remove()
        '' Save the modified file back to the file system
        xdoc.Save(xmlFile)
    Next
Next

Open in new window

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
Comment Utility
You will want to be slightly more general with the selection then.
Change the code to:
Imports System.Data
Imports System.Linq
Imports System.Xml.Linq
Imports System.IO

Module Module1

    Sub Main()

        Dim rootDirectory As String = "C:\temp\xml"

        Dim files = From file In Directory.GetFiles(rootDirectory, "*.xml", SearchOption.AllDirectories)
                    Select file

        For Each file In files
            Dim doc As XDocument = XDocument.Load(file)
            Dim nodes = From node In doc.Root.Elements()
                        Where node.Element("ID").Value <> "0"
                        Select node

            nodes.Remove()
            doc.Save(file)
        Next

    End Sub

End Module

Open in new window

0
 

Author Comment

by:vcharles
Comment Utility
Thank you both for your solutions will run a test and get back to you.
0
 

Author Closing Comment

by:vcharles
Comment Utility
Thank You!
0
 

Author Comment

by:vcharles
Comment Utility
Hi,

How do you modify the code to only apply  to files which end with a particular country (i.e. LinkTemUSA.xml). I also need to clear data for a specific country rather than all the files in the folder. The country will be selected from a combobox.

Thanks,

Victor
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now