Solved

How do I sort an XML File?

Posted on 2014-03-19
17
1,403 Views
Last Modified: 2014-03-20
Hi Experts,

Our phone system stores the phone book in XML.  Trouble is, the names are out of sort.

Is there any application that I can use to open the file, sort it, and save it again as XML?

If not, is there any other way to sort the file on name?

Thanks for your help.

-TH
Master-Phonebook.xml
0
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39940341
I think the easiest way to do this is using an XSLT transformation.

XSLT can be used to convert XML into text, HTML or to modify XML.  In this case you are wanting to re-order the xml.

That being said however, you first have to have valid xml file to start with.   The "&" character has a special meaning, and so I changed that to "&" in your xml file.  In another random spot, there was a hyphen that was not part of any xml element.  So I also removed that.

There is a program that easily allows you to use a xsl file to transform your xml file.  That is called Kernow and can be found here.

http://kernowforsaxon.sourceforge.net/

I have attached the xsl file I created to sort the names (last name/first name) ascending...
I have also attached the updated Master-Phonebook.xml

Both of these files can be used as input to the Kernow program, which can then be used to create a new modified / sorted output xml.

I used the first two files as input to the transform, and the 3rd file is the sorted result.

I attached a screenshot of how I set up Kernow.   Just make sure "Send to Output file" is checked, and then hit run button.
resortByName.xsl
Master-Phonebook.xml
Master-Phonebook-sorted.xml
Kernow.jpg
0
 

Author Comment

by:thutchinson
ID: 39940543
Thank you so much.  I really appreciate it. XML is so totally out of my league!
0
 

Author Comment

by:thutchinson
ID: 39940694
Hi Ken,

The new file won't upload into our phones although the old, presorted one does.  Any ideas?
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 19

Expert Comment

by:Ken Butters
ID: 39940708
Are you getting an error code of any kind?

Did you name the new xml file the same as the existing file?

Possible that you need to delete the original before uploading the new one?

could be that "&" could be causing an issue... might want to try replacing that string back to "&".
0
 

Author Comment

by:thutchinson
ID: 39941082
No error.  The file just acts like it imports but nothing updates.  I tried with same name and different name. The interface is crap and you have to delete the entries one at a time.  

I can't really understand how to use that Kernow app.  I did create a table in access and ran an output query as xml.  Couldn't import that file into the phones either.

Attached is that Access output file.

Would you care to look and tell me why the Access xml won't upload but the xml created by export from the phone uploads fine?
Contact.xml
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39941141
Is the original xml file you posted a direct export from the phone?
0
 

Author Comment

by:thutchinson
ID: 39942192
The original is the direct export from the phone.  We need the ability to work with the file and import the new file with changes.  The phone system only seems to accept the file that was exported from the phone.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39942284
I looked at your access export, not surprised that did not import.

The access file created had an outer structure named <dataroot>
The original file exported directly from the phone had an outer structure named <AddressBook>

if the file must be in a particular format, then the access file is not in the correct format. (at least the outer element structure is wrong).

What kind of phone / system are you working with?  
Are you certain that the phone is importing the original file?
 

You mentioned that when you used the updated sorted file
The file just acts like it imports but nothing updates.
My first thought would be to question whether or not any imports are actually working.  If you think it is doing an import with unchanged data... how can you tell if it is really importing anything at all?

Try this as a first step... take the original export file, change something very minor, like a phone number or a single name, and try to re-import the original file to test whether or not that works.  That way we can at least tell for sure whether or not you are really importing anything at all.
0
 

Author Comment

by:thutchinson
ID: 39942338
Hi Ken,

This is a lot of work for you for 500 pts.  Sorry for that.

Anyway, the phone system is Grandstream.  There is a XML editor on their site but it doesn't install and won't run.  I tried on several operating systems- both 32 bit and 64 bit. It's totally buggy junk.

I am certain that the original file that was output from the phone works because I am able to import it to other phones.  There is a web interface for importing these files to individual phones so they are easy to test.  The goal is to leave the file on our server or website and let the individual phones update every nth day from the specified location.  This file needs to be edited/maintained.  Instead, the only file that works is one that is created by painstaking entering the names and numbers into the phone by the phone keypad and then exporting the file.  Really ugly procedure if you need to maintain a large file.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39942367
I think that it may be a struggle, but we have to first figure out what is causing it to not import.

could be the editor is changing something weird like character encoding of the file or something.

The other thing that nags at me is why we have that dash sitting there in the middle of nowhere.  I've attached a screenshot of what I'm referring to.  I had to remove that dash in order to be able to read the file with Kernow.

Try removing that dash, and then updating just one other field and see if that update imports ok.
orig-xml.jpg
0
 

Author Comment

by:thutchinson
ID: 39942393
How about your observation about the outer structure?  Should I change the <dataroot> to <addressbook>?
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39942421
Ok... I made a pretty dumb mistake... my new "Sorted file" was missing the outer element structure entirely... that would definitely cause it to not import.

To see what I mean... if you look at the 2nd line in the file... I was missing "<AddressBook>"
and I was missing the ending </AddressBook> on the very last line.

(I'm postive this is critical and is the source of all woes for this question)   :)


<?xml version="1.0" encoding="UTF-8"?>
<AddressBook>

I attached the updated resortByName.xsl that will sort and add this outer element structure correctly.  I'm pretty confident this will import ok.

Once this works we need to be able to get you to be able to use Kernow,. so that you can sort stuff your file going forward.   It is really very easy to use, but let's get the attached file imported correctly first and then go from there.
Master-Phonebook-sorted.xml
resortByName.xsl
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39942422
Yes --- changing dataroot to AddressBook might work also.
0
 

Author Comment

by:thutchinson
ID: 39942559
Cool.  I'll give it a try later today and let you know how I make out.  Thanks for hanging in there with me!
0
 

Author Comment

by:thutchinson
ID: 39942753
Hi Ken,

Your latest file (with the outer element added) imported nicely.  The xml file that Access generated still doesn't work so I'll abandon that route.

For now, I've spent enough time on this. I'll award points and post a new question regarding the use of Kernow.  Could you just tell me: What is the purpose of the xsl file?
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39942854
The xsl file is a style sheet "Extensible StyleSheet Language".

The xsl file basically contains code that instructs how to transform an XML file, into something else, or how to modify it.

The Kernow the easiest stand-alone solution I know of to be able to apply a stylesheet to an existing xml.

Given an XSL file, or stylesheet, and an original XML file, that is enough information to be able to produce an output xml, that is a results of applying the xsl transformation to the original XML file.

In this case:
Original XML + XSL stylesheet = New XML.

If you look at that screenshot of Kernow I posted earlier... here is a description of the fields that need to be filled in on the Single File tab in order to create your new sorted file.

XML File : What you put here is the path to the original XML file.
Stylesheet : this is where you put the complete path to the XSL file I posted.
Ouptut File : This is where you want Kernow to create and store the new XML file that is created.

when I say original XML file, the thing is to be a valid XML file, there are faily strict rules to follow to make sure that it is a valid xml file.

In an xml file, every tag must have an opening and closing tag around it (like parenthesis or brackets).   If something exists outside of the opening / closing tags, then it is not valid xml.

an opening tag is like <AddressBook>   the closing tag or closing parenthesis, has a slash in it and looks like this : </AddressBook>

Everything that is between <AddressBook> and </AddressBook> defines what an addressbook looks like.

the content of an XSL file is sort of a whole programming language all by itself

Here is some of the contents of what the resortByName.xsl file has:

The line items in bold below are the part of the XSL that are actually doing the sorting of each "record".

<xsl:template match="*[local-name()='AddressBook']">
    <xsl:element name="AddressBook">
            <xsl:for-each select="./Contact">
                  <xsl:sort order='ascending' select="./*[local-name()='LastName']"/>
                  <xsl:sort order='ascending' select="./*[local-name()='FirstName']"/>

                   <Contact>
                        <xsl:apply-templates/>
                   </Contact>
            </xsl:for-each>
    </xsl:element>
</xsl:template>


Now that being said... you should be able to get your access file to work too.

In your access file you need to replace ALL of this :

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Contact.xsd" generated="2014-03-19T18:31:29">

with this (it is case sensitive)
<AddressBook>

think of "<AddressBook>" as opening parenthesis

Then at the bottom of the file you have to replace this :
</dataroot>

with exactly this :
</AddressBook>

think of "</AddressBook>" as closing Parenthesis that matches opening parenthesis at the top.

Make sure there is just one address book element at the top, and one at the bottom, like opening and closing parenthesis.... I see an opening <AddressBook > in your access import... just trying to stress that when all is said and done, you should end up with ONLY one at the top, and one at the bottom.  where the one at the bottom has a "/" right after the open angle bracket and right before the word AddressBook.

...with those changes, the access file should import ok.
0
 

Author Closing Comment

by:thutchinson
ID: 39943877
Thank you Ken for your thoroughness and diligence.  I really appreciate the extra explanation and sharing your vast knowledge with me. Super job!
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

695 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