Solved

Sorting XML in VB

Posted on 2001-09-05
20
484 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:slimbx
  • 10
  • 9
20 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6458135
Yes a lot easier. Use xsl:sort <smile>

Anthony
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6459320
XSL will let you sort a document that is already created.   Comment further If you need to do it with VB code.
0
 

Author Comment

by:slimbx
ID: 6460880
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6460968
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
 

Author Comment

by:slimbx
ID: 6461035
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6461264
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
 

Author Comment

by:slimbx
ID: 6461297
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6461918
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
 

Author Comment

by:slimbx
ID: 6462194
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
 

Author Comment

by:slimbx
ID: 6462217
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
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

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6462306
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
 

Author Comment

by:slimbx
ID: 6462319
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
 

Author Comment

by:slimbx
ID: 6465502
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6465514
Post your stylesheet and I will take a look.

Anthony
0
 

Author Comment

by:slimbx
ID: 6465536
<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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6465679
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
 

Author Comment

by:slimbx
ID: 6470726
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
ID: 6470948
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
 

Author Comment

by:slimbx
ID: 6470998
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6471021
Thanks.  I am glad you got it sorted out.

Anthony
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

25 Experts available now in Live!

Get 1:1 Help Now