Solved

How do I sort an XML File?

Posted on 2014-03-19
17
876 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

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

Join & Write a Comment

Suggested Solutions

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

20 Experts available now in Live!

Get 1:1 Help Now