Sorting XML - VisualCron

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Welcome Guest! To enable all features please Login or Register.

osirisja
Free support Topic Starter

2014-06-02T14:33:34Z

Hi All

I just wondered if any of you clever individuals out there might know an efficient way of sorting a very large XML file in Visual Cron? I'm guessing the best way is with Powershell?

I have an XML structure similar to:

Quote:

<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
<ROW>
<INVESTOR>
<UNITHOLDERID>
<PORTFOLIONUM>xxxxxx</PORTFOLIONUM>
<INVESTORNAME>
<INVESTORADDRESS>
<ADDRESS1>
<ADDRESS2>
</INVESTORADDRESS>
<FUND SEQUENCE="1">
<FUNDNAME>
</FUND>
<FUND SEQUENCE="2">
<FUNDNAME>
</FUND>
<FUND SEQUENCE="3">
<FUNDNAME>
</INVESTOR>
</ROW>
<ROW>
<INVESTOR>
<UNITHOLDERID>
<PORTFOLIONUM>aaaaaa</PORTFOLIONUM>
<INVESTORNAME>
<INVESTORADDRESS>
<ADDRESS1>
<ADDRESS2>
</INVESTORADDRESS>
<FUND SEQUENCE="1">
<FUNDNAME>
</FUND>
</INVESTOR>
</ROW>

There are typically thousands of <ROWS> but I need to sort them all by the <PORTFOLIONUM> tag.

Any suggestions would be greatfully received :-)

Cheers

Andy

Edited by moderator 2014-06-03T13:00:29Z | Reason: Not specified

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

ErikC
Free support

2014-06-03T06:50:23Z

Hi osirisja,

Looking at your XML, there are some flaws.. missing end tags 😊.
So I modified the XML to this, hope it's right:

<?xml version="1.0" encoding="utf-8"?>
<ROWSET>
  <ROW>
    <INVESTOR>
      <UNITHOLDERID />
      <PORTFOLIONUM>xxxxxx</PORTFOLIONUM>
      <INVESTORNAME />
      <INVESTORADDRESS>
        <ADDRESS1 />
        <ADDRESS2 />
      </INVESTORADDRESS>
      <FUND SEQUENCE="1">
        <FUNDNAME />
      </FUND>
      <FUND SEQUENCE="2">
        <FUNDNAME />
      </FUND>
      <FUND SEQUENCE="3">
        <FUNDNAME />
      </FUND>
    </INVESTOR>
  </ROW>
  <ROW>
    <INVESTOR>
      <UNITHOLDERID />
      <PORTFOLIONUM>aaaaaa</PORTFOLIONUM>
      <INVESTORNAME />
      <INVESTORADDRESS>
        <ADDRESS1 />
        <ADDRESS2 />
      </INVESTORADDRESS>
      <FUND SEQUENCE="1">
        <FUNDNAME />
      </FUND>
    </INVESTOR>
  </ROW>
</ROWSET>

As I am a developer I liked this question, but i solved it for you with a .NET task.

Use the following code:

using System.Xml.Linq;

XElement root = XElement.Load("XMLFile.xml");
var orderedtabs = root.Elements("ROW")
     .OrderBy(xtab => (string)xtab.Element("INVESTOR").Element("PORTFOLIONUM"))
     .ToArray();
root.RemoveAll();
foreach (XElement tab in orderedtabs)
     root.Add(tab);
root.Save("XMLFile.xml");

After the code the XMLFile.xml contains:

<?xml version="1.0" encoding="utf-8"?>
<ROWSET>
  <ROW>
    <INVESTOR>
      <UNITHOLDERID />
      <PORTFOLIONUM>aaaaaa</PORTFOLIONUM>
      <INVESTORNAME />
      <INVESTORADDRESS>
        <ADDRESS1 />
        <ADDRESS2 />
      </INVESTORADDRESS>
      <FUND SEQUENCE="1">
        <FUNDNAME />
      </FUND>
    </INVESTOR>
  </ROW>
  <ROW>
    <INVESTOR>
      <UNITHOLDERID />
      <PORTFOLIONUM>xxxxxx</PORTFOLIONUM>
      <INVESTORNAME />
      <INVESTORADDRESS>
        <ADDRESS1 />
        <ADDRESS2 />
      </INVESTORADDRESS>
      <FUND SEQUENCE="1">
        <FUNDNAME />
      </FUND>
      <FUND SEQUENCE="2">
        <FUNDNAME />
      </FUND>
      <FUND SEQUENCE="3">
        <FUNDNAME />
      </FUND>
    </INVESTOR>
  </ROW>
</ROWSET>

Hope this helps.

Regards,
Erik

Edited by user 2014-06-03T08:35:39Z | Reason: Not specified

Uses Visualcron since 2006.

osirisja
Free support Topic Starter

2014-06-03T08:16:15Z

Hi Erik

Thanks you so much for your suggestion, it could be a very simple solution for a lot of similar tasks we need to perform on XML.

Unfortunately I'm not familiar with .NET code so I will need to get my head around that, however I have come up against an issue when running the code you sent (cannot load file or assembly) - see attached screen shot. Clearly i'm doing something wrong, but any ideas what?

Thanks again

Andy

[EDIT] p.s. I knew about the XML tags but just wanted to show what I was trying to sort.

Edited by user 2014-06-03T08:18:09Z | Reason: Not specified

osirisja attached the following image(s):

ErikC
Free support

2014-06-03T08:52:14Z

Hi,

This C# code should do the trick (replace all the code with this code):

using System;
using System.Linq;
using System.Xml.Linq;
 
public class XMLsort
{
 public static void Sort()
 {
	XElement root = XElement.Load("c:\\axml\\ciam.xml");
	var orderedRows = root.Elements("ROW")
		.OrderBy(xrow => (string)xrow.Element("INVESTOR").Element("PORTFOLIONUM"))
		.ToArray();
	root.RemoveAll();
	foreach (XElement row in orderedRows)
		root.Add(row);
	root.Save("c:\\axml\\ciamsorted.xml");
 }
}

What you also need to do is edit the references (click the button Edit references).
Have these dll's in there:

mscorelib.dll
System.Core.dll
System.dll
System.Xml.dll
System.Xml.Linq.dll

Now when you compile and refresh the methods, you can select the Sort() Method and run your task.

Regards,
Erik

Edited by user 2014-06-03T09:34:03Z | Reason: Not specified

Uses Visualcron since 2006.

osirisja
Free support Topic Starter

2014-06-03T10:05:00Z

Hi Erik

Fabulous! It does the job perfectly. Interestingly, we have a similar piece of XML for another customer which is sorted outside of VC, using PERL script. That takes ~20 minutes to process. This takes just a couple of seconds!!! Now I will need to try and get my head around .NET :-)

Thanks once again Erik

Cheers

Andy

ErikC
Free support

2014-06-03T13:54:04Z

Hi Andy,

Glad to hear this piece of code helped you out.
I do Perl too, but I prefer the .NET environment.

Regards,
Erik

Uses Visualcron since 2006.