Sorting XML in VB

The question is pretty self explanatory. How do you or can you sort a XML document with VB code ONLY? No xsl:sort routine.

I need a shorter way, if possible, to do this. I am working on a way right now that opens up the xml document and then writes the values out to arrays for each node, then those arrays go through a bubble sort. A lot of code right now, which is what I do not want. Still a novice to VB and XML programming. Is there a easier way. Thanks.

slimbx
slimbxAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
It works fine for me.  So just to make sure we are on the same page, let's go one step at a time.

1. This is my xml document (notice that I have added to additional "Person" elements with FIRSTNAME abc and xyz):
<?xml version="1.0"?>
<PEOPLE>
<PERSON PERSONID="1">
    <GROUP>Group Name</GROUP>
    <PREFIX>Name prefix</PREFIX>
    <FIRSTNAME>first name</FIRSTNAME>
    <LASTNAME>Last name</LASTNAME>
    <SUFFIX>Name suffix</SUFFIX>
    <ADDITIONALNAME>Additional names</ADDITIONALNAME>
    <NICKNAME>Nickname</NICKNAME>
    <TITLE>Title</TITLE>
    <COMPANY>Company</COMPANY>
    <DEPT>Department</DEPT>
    <POSITION>Position</POSITION>
    <ADDRESS>Address</ADDRESS>
    <CITY>City</CITY>
    <STATE>State</STATE>
    <ZIPCODE>Zip Code</ZIPCODE>
    <COUNTY>County</COUNTY>
    <COUNTRY>Country</COUNTRY>
    <PERSONAL>Personal Number</PERSONAL>
    <BUSINESS>Business Number</BUSINESS>
    <CELL>Mobile Cell Number</CELL>
    <PAGER>Pager Number</PAGER>
    <FAX>Fax Number</FAX>
    <BBS>Bulletin Board</BBS>
    <EMAIL>Email Address</EMAIL>
    <WEBSITE>Website</WEBSITE>
    <NOTES>Notes</NOTES>
    <COMMENTS>Comments</COMMENTS>
    <IMAGE>Contact Image</IMAGE>
</PERSON>
<PERSON PERSONID="2">
    <GROUP>Group Name</GROUP>
    <PREFIX>Name prefix</PREFIX>
    <FIRSTNAME>abc</FIRSTNAME>
    <LASTNAME>Last name</LASTNAME>
    <SUFFIX>Name suffix</SUFFIX>
    <ADDITIONALNAME>Additional names</ADDITIONALNAME>
    <NICKNAME>Nickname</NICKNAME>
    <TITLE>Title</TITLE>
    <COMPANY>Company</COMPANY>
    <DEPT>Department</DEPT>
    <POSITION>Position</POSITION>
    <ADDRESS>Address</ADDRESS>
    <CITY>City</CITY>
    <STATE>State</STATE>
    <ZIPCODE>Zip Code</ZIPCODE>
    <COUNTY>County</COUNTY>
    <COUNTRY>Country</COUNTRY>
    <PERSONAL>Personal Number</PERSONAL>
    <BUSINESS>Business Number</BUSINESS>
    <CELL>Mobile Cell Number</CELL>
    <PAGER>Pager Number</PAGER>
    <FAX>Fax Number</FAX>
    <BBS>Bulletin Board</BBS>
    <EMAIL>Email Address</EMAIL>
    <WEBSITE>Website</WEBSITE>
    <NOTES>Notes</NOTES>
    <COMMENTS>Comments</COMMENTS>
    <IMAGE>Contact Image</IMAGE>
</PERSON>
<PERSON PERSONID="3">
    <GROUP>Group Name</GROUP>
    <PREFIX>Name prefix</PREFIX>
    <FIRSTNAME>xyz</FIRSTNAME>
    <LASTNAME>Last name</LASTNAME>
    <SUFFIX>Name suffix</SUFFIX>
    <ADDITIONALNAME>Additional names</ADDITIONALNAME>
    <NICKNAME>Nickname</NICKNAME>
    <TITLE>Title</TITLE>
    <COMPANY>Company</COMPANY>
    <DEPT>Department</DEPT>
    <POSITION>Position</POSITION>
    <ADDRESS>Address</ADDRESS>
    <CITY>City</CITY>
    <STATE>State</STATE>
    <ZIPCODE>Zip Code</ZIPCODE>
    <COUNTY>County</COUNTY>
    <COUNTRY>Country</COUNTRY>
    <PERSONAL>Personal Number</PERSONAL>
    <BUSINESS>Business Number</BUSINESS>
    <CELL>Mobile Cell Number</CELL>
    <PAGER>Pager Number</PAGER>
    <FAX>Fax Number</FAX>
    <BBS>Bulletin Board</BBS>
    <EMAIL>Email Address</EMAIL>
    <WEBSITE>Website</WEBSITE>
    <NOTES>Notes</NOTES>
    <COMMENTS>Comments</COMMENTS>
    <IMAGE>Contact Image</IMAGE>
</PERSON>
</PEOPLE>

2. This is my xsl:
<PEOPLE xsl:version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:for-each select="/PEOPLE/PERSON">
     <xsl:sort order="ascending" data-type="text" select="FIRSTNAME"/>
     <xsl:copy-of select="."/>
  </xsl:for-each>
</PEOPLE>

3. New project
4. Add a Command Button (Command1)
5. Make a reference to XML version 3 or 4
6. Paste this code:
Private Sub Command1_Click()
Dim xmlDoc As MSXML2.DOMDocument
Dim xslDoc As MSXML2.DOMDocument
Dim xmlResult As MSXML2.DOMDocument

Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.Load "d:\temp\person.xml"

Set xslDoc = New MSXML2.DOMDocument
xslDoc.Load "d:\temp\person.xsl"

Set xmlResult = New MSXML2.DOMDocument
xmlDoc.transformNodeToObject xslDoc, xmlResult

xmlResult.save "d:\temp\PersonNew.xml"

Set xmlDoc = Nothing
Set xslDoc = Nothing
Set xmlResult = Nothing

End Sub

7. Here is the result:
<?xml version="1.0" encoding="UTF-16"?>
<PEOPLE>
   <PERSON PERSONID="2">
      <GROUP>Group Name</GROUP>
      <PREFIX>Name prefix</PREFIX>
      <FIRSTNAME>abc</FIRSTNAME>
      <LASTNAME>Last name</LASTNAME>
      <SUFFIX>Name suffix</SUFFIX>
      <ADDITIONALNAME>Additional names</ADDITIONALNAME>
      <NICKNAME>Nickname</NICKNAME>
      <TITLE>Title</TITLE>
      <COMPANY>Company</COMPANY>
      <DEPT>Department</DEPT>
      <POSITION>Position</POSITION>
      <ADDRESS>Address</ADDRESS>
      <CITY>City</CITY>
      <STATE>State</STATE>
      <ZIPCODE>Zip Code</ZIPCODE>
      <COUNTY>County</COUNTY>
      <COUNTRY>Country</COUNTRY>
      <PERSONAL>Personal Number</PERSONAL>
      <BUSINESS>Business Number</BUSINESS>
      <CELL>Mobile Cell Number</CELL>
      <PAGER>Pager Number</PAGER>
      <FAX>Fax Number</FAX>
      <BBS>Bulletin Board</BBS>
      <EMAIL>Email Address</EMAIL>
      <WEBSITE>Website</WEBSITE>
      <NOTES>Notes</NOTES>
      <COMMENTS>Comments</COMMENTS>
      <IMAGE>Contact Image</IMAGE>
   </PERSON>
   <PERSON PERSONID="1">
      <GROUP>Group Name</GROUP>
      <PREFIX>Name prefix</PREFIX>
      <FIRSTNAME>first name</FIRSTNAME>
      <LASTNAME>Last name</LASTNAME>
      <SUFFIX>Name suffix</SUFFIX>
      <ADDITIONALNAME>Additional names</ADDITIONALNAME>
      <NICKNAME>Nickname</NICKNAME>
      <TITLE>Title</TITLE>
      <COMPANY>Company</COMPANY>
      <DEPT>Department</DEPT>
      <POSITION>Position</POSITION>
      <ADDRESS>Address</ADDRESS>
      <CITY>City</CITY>
      <STATE>State</STATE>
      <ZIPCODE>Zip Code</ZIPCODE>
      <COUNTY>County</COUNTY>
      <COUNTRY>Country</COUNTRY>
      <PERSONAL>Personal Number</PERSONAL>
      <BUSINESS>Business Number</BUSINESS>
      <CELL>Mobile Cell Number</CELL>
      <PAGER>Pager Number</PAGER>
      <FAX>Fax Number</FAX>
      <BBS>Bulletin Board</BBS>
      <EMAIL>Email Address</EMAIL>
      <WEBSITE>Website</WEBSITE>
      <NOTES>Notes</NOTES>
      <COMMENTS>Comments</COMMENTS>
      <IMAGE>Contact Image</IMAGE>
   </PERSON>
   <PERSON PERSONID="3">
      <GROUP>Group Name</GROUP>
      <PREFIX>Name prefix</PREFIX>
      <FIRSTNAME>xyz</FIRSTNAME>
      <LASTNAME>Last name</LASTNAME>
      <SUFFIX>Name suffix</SUFFIX>
      <ADDITIONALNAME>Additional names</ADDITIONALNAME>
      <NICKNAME>Nickname</NICKNAME>
      <TITLE>Title</TITLE>
      <COMPANY>Company</COMPANY>
      <DEPT>Department</DEPT>
      <POSITION>Position</POSITION>
      <ADDRESS>Address</ADDRESS>
      <CITY>City</CITY>
      <STATE>State</STATE>
      <ZIPCODE>Zip Code</ZIPCODE>
      <COUNTY>County</COUNTY>
      <COUNTRY>Country</COUNTRY>
      <PERSONAL>Personal Number</PERSONAL>
      <BUSINESS>Business Number</BUSINESS>
      <CELL>Mobile Cell Number</CELL>
      <PAGER>Pager Number</PAGER>
      <FAX>Fax Number</FAX>
      <BBS>Bulletin Board</BBS>
      <EMAIL>Email Address</EMAIL>
      <WEBSITE>Website</WEBSITE>
      <NOTES>Notes</NOTES>
      <COMMENTS>Comments</COMMENTS>
      <IMAGE>Contact Image</IMAGE>
   </PERSON>
</PEOPLE>

Let me know what I am missing.
Anthony
0
 
Anthony PerkinsCommented:
Yes a lot easier. Use xsl:sort <smile>

Anthony
0
 
Dave_GreeneCommented:
XSL will let you sort a document that is already created.   Comment further If you need to do it with VB code.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
slimbxAuthor Commented:
The application that I have in written in Visual Basic. I know that I could probably use xsl style sheets, but my program in not setup to use anything outside of VB, which is how I want to keep it.
0
 
Anthony PerkinsCommented:
I guess I am being dense, but is not the XML document "outside of VB".  So what would be the problem of addig a Stylesheet to it?
0
 
slimbxAuthor Commented:
I am using MSXML to traverse a XML document. The XML document is being treated as my main source of data for this program. Instead of using Access or SQL on the backend. This application does not call any html, asp, xsl pages/sheets or nothing. It is just a VB form that you can open an xml file with, which I have already coded.

If you can use xsl:sort in VB, then I can use that. I know that if you use xsl, I would need to pass the xml to the style sheet, hwich is okay, but I do not want to do that. Plus, I have a lot more functionality that I want to do with this. If I could at least get the basic structure of how to sort in VB without using xsl:sort, if there is a way, then that would be great. If not, then lets see if someone has an ingenious idea, I am always up for those.

slimbx
0
 
Anthony PerkinsCommented:
My very biased opinion: If you are using XML and not using Stylesheets you are not using XML to its full potential. It may even help you to filter out the data you don't need, rather than traversing the document node by node.

Now I will get off my high horse. <smile>

If you can create a stylesheet that gets the nodes you need and sorts them, than all you need to do in your VB code is to load the XSL document and use the transformNode method to convert the XML document to the nodes and order you need.

On the other hand, if you are satisfied using code to sort the data, than go for it.

Anthony
0
 
slimbxAuthor Commented:
acperkins,

Sounds like that is a more efficient way to go. So can you give me a brief snippet of what I need to do to do that. I can create the xsl style, but just the VB code.

Dave Greene

This is probably what you were speaking of. My ignorance in the XML, XSL, XSLT and VB realm is still young. My apologies

slimbx
0
 
Anthony PerkinsCommented:
Without seeing your xml document and what you are trying to achieve, it is difficult to give you a meaningful example, that will impress you with the advantages of XSL.

Having said that, here is a very simple XML document using the NorthWind data:
<customers>
     <customer>
          Rattlesnake Canyon Grocery"
     </customer>
     <customer>
          Old World Delicatessen
     </customer>
     <customer>
          Save-a-lot Markets
     </customer>
     <customer>
          The Cracker Box
     </customer>
     <customer>
          Hungry Coyote Import Store
     </customer>
     <customer>
          Great Lakes Food Market
     </customer>
     <customer>
          Trail's Head Gourmet Provisioners
     </customer>
     <customer>
          Split Rail Beer &amp; Ale
     </customer>
     <customer>
          Lonesome Pine Restaurant
     </customer>
     <customer>
          The Big Cheese
     </customer>
     <customer>
          Let's Stop N Shop  
     </customer>
     <customer>
          White Clover Markets
     </customer>
     <customer>
          Lazy K Kountry Store
     </customer>
</customers>

Here is a stylesheet I created to sort the data:

<customers xsl:version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:for-each select="/customers/customer">
          <xsl:sort order="ascending" data-type="text" select="."/>
          <xsl:copy>
               <xsl:value-of select="."/>
          </xsl:copy>
     </xsl:for-each>
</customers>

The VB code would look like this:

Dim xmlDoc As MSXML2.DOMDocument
Dim xslDoc As MSXML2.DOMDocument
Dim xmlResult As MSXML2.DOMDocument

Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.Load "customers.xml"

Set xslDoc = New MSXML2.DOMDocument
xslDoc.Load "customers.xsl"

Set xmlResult = New MSXML2.DOMDocument
xmlDoc.transformNodeToObject xslDoc, xmlResult

Debug.Print xmlResult.xml

Set xmlDoc = Nothing
Set xslDoc = Nothing
Set xmlResult = Nothing

Notes:
- In order to use XSLT at least MSXML version 3.0 is required.
- I am using the stylesheet to sort the XML document to produce the same data, but in ascending mode.  Typically, you would also filter out the nodes you don't need and you may not have any need for a resulting XML document.  If this is the case than you can use the transformNode Method which returns a string.

Anthony
0
 
slimbxAuthor Commented:
I havent tried it yet, but I am sure that it will work.

Quick question and we are done here, once the data is sorted, then how will it be copied back to the xml file?

I essentially want to sort the data (via the xsl style file) and then ship it back where it came from and resave that xml file for the user to do whatever with. Does this cover that as well?

slimbx

0
 
slimbxAuthor Commented:
for acperkins
In regards to this comment,

I guess I am being dense, but is not the XML document "outside of VB".  So what would be the problem
of addig a Stylesheet to it?

sorry I did not forget about you :)

What I am doing in a nut shell
The xml document that I have gets generated on the fly. What I am doing is kind of like an automatic collection of data, sort of like a ticker that displays stock signs. Well I am generating a new xml document with data from an access database, which is not in order according to a person last name. Being that there are thousands of records, I am trying to automate this process so that the data can be stored in xml files. My interface then which I have already coded, opens up these xml files and allows the user to add, update, delete, maage their records.

0
 
Anthony PerkinsCommented:
Add this line:
xmlResult.save "customers2.xml"
after transformNodeToObject method is called.  This will give you the sorted nodes you are looking for.  You will probably have to play around with your stylesheet as no doubt your XML document is more complex.

Also, just be aware that you should add appropriate error handling.

It also may be more efficient if you pull the data from the Access database using the Order By clause.  In which case xsl-sort issue is moot.

Anthony
0
 
slimbxAuthor Commented:
I could do that, but once this data is sorted, it is no longer in the database. This is pretty much and export without using the xml tools that SQL and Access have to generate xml data.
0
 
slimbxAuthor Commented:
I have implemented the code that you sent, and there is a issue. Here is a sample of my xml document that I am sorting:

<?xml version="1.0"?>
<PEOPLE>
<PERSON PERSONID="1">
     <GROUP>Group Name</GROUP>
     <PREFIX>Name prefix</PREFIX>
     <FIRSTNAME>first name</FIRSTNAME>
     <LASTNAME>Last name</LASTNAME>
     <SUFFIX>Name suffix</SUFFIX>
     <ADDITIONALNAME>Additional names</ADDITIONALNAME>
     <NICKNAME>Nickname</NICKNAME>
     <TITLE>Title</TITLE>
     <COMPANY>Company</COMPANY>
     <DEPT>Department</DEPT>
     <POSITION>Position</POSITION>
     <ADDRESS>Address</ADDRESS>
     <CITY>City</CITY>
     <STATE>State</STATE>
     <ZIPCODE>Zip Code</ZIPCODE>
     <COUNTY>County</COUNTY>
     <COUNTRY>Country</COUNTRY>
     <PERSONAL>Personal Number</PERSONAL>
     <BUSINESS>Business Number</BUSINESS>
     <CELL>Mobile Cell Number</CELL>
     <PAGER>Pager Number</PAGER>
     <FAX>Fax Number</FAX>
     <BBS>Bulletin Board</BBS>
     <EMAIL>Email Address</EMAIL>
     <WEBSITE>Website</WEBSITE>
     <NOTES>Notes</NOTES>
     <COMMENTS>Comments</COMMENTS>
     <IMAGE>Contact Image</IMAGE>
</PERSON>
</PEOPLE>

When the xml is sent to the style sheet and returned (when save is invoked), this is what my xml document turns into:

<?xml version="1.0" encoding="UTF-16"?>
<people><PERSON>PERSONALaaCasey Hillaaaaaaaw***.*****@dsionline.com</PERSON><PERSON>Group NameName prefixfirst nameLast nameName suffixAdditional namesNicknameTitleCompanyDepartmentPositionAddressCityStateZip CodeCountyCountryPersonal NumberBusiness NumberMobile Cell NumberPager NumberFax NumberBulletin Boardw*****@yahoo.comWebsiteNotesCommentsContact Image</PERSON></people>

Why is this?
And is there a way to fix that?

I used the code and implemented word for word.
0
 
Anthony PerkinsCommented:
Post your stylesheet and I will take a look.

Anthony
0
 
slimbxAuthor Commented:
<people xsl:version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:for-each select="/PEOPLE/PERSON">
<xsl:sort order="ascending" data-type="text" select="FIRSTNAME"/>
<xsl:copy>
<xsl:value-of select="."/>
</xsl:copy>
</xsl:for-each>
</people>
0
 
Anthony PerkinsCommented:
Try this stylesheet:
<PEOPLE xsl:version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:for-each select="/PEOPLE/PERSON">
      <xsl:sort order="ascending" data-type="text" select="FIRSTNAME"/>
      <xsl:copy-of select="."/>
   </xsl:for-each>
</PEOPLE>

Anthony
0
 
slimbxAuthor Commented:
Same result occurs. The xsl file is sorting the records properly, but once they are sorted, when I save the file, it does not write out all of the tags associated for a person record - ex <GROUP></GROUP><IMAGE></IMAGE> etc. It only places the following tags:

<PEOPLE><PERSON>Rec1Rec2Rec3......</PERSON><PERSON>Rec1Rec2Rec3</PERSON></PEOPLE>

Any other ideas or suggestions
0
 
slimbxAuthor Commented:
I hosed it up. The prob was I had a beginning <xsl:copy> tag in there right before the <xsl:copy-of select="."/> from the earlier xsl. Hey thanks for the help, the script works good. It takes me a minute to test because I generate the xsl file on the fly and implement it with the xml. For your troubles, I have added a few. Thanks Anthony

slimbx
0
 
Anthony PerkinsCommented:
Thanks.  I am glad you got it sorted out.

Anthony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.