How do I sort an XML File?
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
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
ASKER
Thank you so much. I really appreciate it. XML is so totally out of my league!
ASKER
Hi Ken,
The new file won't upload into our phones although the old, presorted one does. Any ideas?
The new file won't upload into our phones although the old, presorted one does. Any ideas?
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 "&".
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 "&".
ASKER
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
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
Is the original xml file you posted a direct export from the phone?
ASKER
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.
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).
You mentioned that when you used the updated sorted file
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.
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.
ASKER
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.
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.
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
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
ASKER
How about your observation about the outer structure? Should I change the <dataroot> to <addressbook>?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes --- changing dataroot to AddressBook might work also.
ASKER
Cool. I'll give it a try later today and let you know how I make out. Thanks for hanging in there with me!
ASKER
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?
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?
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()='Add
<xsl:element name="AddressBook">
<xsl:for-each select="./Contact">
<xsl:sort order='ascending' select="./*[local-name()='
<xsl:sort order='ascending' select="./*[local-name()='
<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-micr
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.
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()='Add
<xsl:element name="AddressBook">
<xsl:for-each select="./Contact">
<xsl:sort order='ascending' select="./*[local-name()='
<xsl:sort order='ascending' select="./*[local-name()='
<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-micr
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.
ASKER
Thank you Ken for your thoroughness and diligence. I really appreciate the extra explanation and sharing your vast knowledge with me. Super job!
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