Sorting XML in VB

Yes a lot easier. Use xsl:sort <smile>

Anthony

XSL will let you sort a document that is already created.   Comment further If you need to do it with VB code.

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.

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?

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

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

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

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

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

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.

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

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.

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.

Post your stylesheet and I will take a look.

Anthony

<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>

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

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

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

Thanks.  I am glad you got it sorted out.

Anthony