Solved

Moving around in XML using XSL

Posted on 2014-03-19
11
222 Views
Last Modified: 2014-03-20
Hi all,

I am attempting to transform an XML file to CSV using XSL.
As you can see in the attached XML we have a file node we have to go through. (although that sample XML does not contain more than one file node there can be).
From there I need the file key and file_name.
Then for each file node I need to go down and do a for each on the document nodes grabbing key, duplex, envelope_number values and also start_page and page_count, also grabbing values from all the nodes in the print node.

I am hoping for output like the following.
file,file_name,document_key,duplex,envelope_number,start_page,page_count,perf_sheet,add_name,add_1,add_2,add_3,add_4,add_5,add_6
1,Statements-20140225131628-1.pdf,1,false,1,1,1,1,Test User 1,4 abc Drive,somewhere,Someplace,SomeHow,,
1,Statements-20140225131628-1.pdf,1b,true,1,2,3,1,Test User 1,4 abc Drive,somewhere,Someplace,SomeHow,,
1,Statements-20140225131628-1.pdf,2,false,2,5,1,1,Test User 2,12 abc Drive,somewhere else,Someplace else,Somewho,,
1,Statements-20140225131628-1.pdf,2b,true,2,6,2,1,Test User 2,12 abc Drive,somewhere else,Someplace else,Somewho,,

Open in new window


I have got part way there, but now I'm having trouble going into the document nodes of each file node.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="/">
  <xsl:apply-templates select="/root/files/file"/>
</xsl:template>
<xsl:template match="/root/files/file">  
 <xsl:value-of select="'file_number,file_name,document_number,duplex,envelope_number,start_page,page_count'"/>
    <xsl:text>
</xsl:text>
<xsl:for-each select="/root/files/file/documents/document">
	<xsl:for-each select="/root/files/file">
	<xsl:value-of select="@key"/>
	<xsl:value-of select="','"/>
	<xsl:value-of select="file_name"/>
	<xsl:value-of select="','"/>
Here is the problem, I'm currently in the file node
			<xsl:value-of select="@key"/>
			<xsl:value-of select="','"/>
			<xsl:value-of select="@duplex"/>
			<xsl:value-of select="','"/>
			<xsl:value-of select="@envelope_number"/>
			<xsl:value-of select="','"/>
			<xsl:value-of select="start_page"/>
			<xsl:value-of select="','"/>
			<xsl:value-of select="page_count"/>
			<xsl:value-of select="','"/>
		<xsl:text>
</xsl:text>
	</xsl:for-each>
</xsl:for-each>
   <xsl:text>
</xsl:text>
</xsl:template>
</xsl:stylesheet>

Open in new window


Any help you could provide would be appreciated.
Statements-20140225131628-index.xml
0
Comment
Question by:qz8dsw
  • 5
  • 3
  • 2
11 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 400 total points
ID: 39941359
If you need to flatten an XML, for instance when doing CSV,
make sure you get to the deepest point that is repetitive (document in your case)
and get the higher up values through the ancestor:: axis

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="/root/files/file"/>
    </xsl:template>

    <xsl:template match="/root/files/file">  
        <xsl:value-of select="'file_number,file_name,document_number,duplex,envelope_number,start_page,page_count'"/>
        <xsl:text>
</xsl:text>
        <xsl:apply-templates select="documents/document"/>
        <xsl:text>
</xsl:text>
    </xsl:template>

    <xsl:template match="document">
        <xsl:value-of select="ancestor::file/@key"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="ancestor::file/file_name"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="@key"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="@duplex"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="@envelope_number"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="start_page"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="page_count"/>
        <xsl:value-of select="','"/>
        <xsl:text>
</xsl:text>
    </xsl:template>
</xsl:stylesheet>

Open in new window


So process the line from document level, not from file level
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39941365
Note that when I need CSV, it usually is for importing in excel.
If I do that, I transform to html tables instead
If I do so, I don't have to worry about character encoding and I don't have to take care of potential " or newlines inside datafields

If you transform to HTML table instead of csv, and you name the output file with .xls extension, excel will import it with no worries

Just in case you are aiming for excel or similar, don't do CSV

but the comment about digging for the deepest repetitive element, still holds of course
0
 
LVL 15

Author Comment

by:qz8dsw
ID: 39941422
Thanks VERY much Geert, that has helped immensely.
I have new code based on yours and adding in some other parts of the XML I need, however I have noticed when adding a second file node with different values although it is extracted fine the title detailing the column names is repeated

Here is my new code.
(Would you suggest to go to the bottom level (print) and then use ancestor:: to get back to document).
I realise why the XSL is doing what it is doing, because it has found a new file node however I have been thrown in the deep end as such so any advice would be appreciated re best practices and also the title repeating on subsequent file nodes if they exist,

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="/root/files/file"/>
    </xsl:template>

    <xsl:template match="/root/files/file">  
        <xsl:value-of select="'file_number,file_name,document_number,duplex,envelope_number,start_page,page_count'"/>
        <xsl:text>
</xsl:text>
        <xsl:apply-templates select="documents/document"/>
        <xsl:text>
</xsl:text>
    </xsl:template>
    <xsl:template match="document">
        <xsl:value-of select="ancestor::file/@key"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="ancestor::file/file_name"/>
        <xsl:value-of select="','"/>
		<xsl:value-of select="@key"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="@duplex"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="@envelope_number"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="start_page"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="page_count"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/perf_sheet"/>
		<xsl:value-of select="','"/>				
		<xsl:value-of select="print/insert_bin_1"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_2"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_3"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_4"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_5"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_6"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_name"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_1"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_2"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_3"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_4"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_5"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_6"/>
		<xsl:value-of select="','"/>
		<xsl:text>
</xsl:text>
	</xsl:template>
</xsl:stylesheet>

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 15

Author Comment

by:qz8dsw
ID: 39941456
Attached is a new XML file with multiple file nodes.
Statements-20140225131628-index.xml
0
 
LVL 35

Assisted Solution

by:mccarl
mccarl earned 100 total points
ID: 39941462
You just need to move the lines the output the column headers from where they are now (which repeats for each file node) to the template the manages the entire root element (which only gets executed once), such as this...
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
    
    <xsl:template match="/">  
        <xsl:value-of select="'file_number,file_name,document_number,duplex,envelope_number,start_page,page_count'"/>
        <xsl:text>
</xsl:text>
        <xsl:apply-templates select="/root/files/file"/>
    </xsl:template>

    <xsl:template match="/root/files/file">
        <xsl:apply-templates select="documents/document"/>
        <xsl:text>
</xsl:text>
    </xsl:template>
    <xsl:template match="document">
        <xsl:value-of select="ancestor::file/@key"/>
        <xsl:value-of select="','"/>
        <xsl:value-of select="ancestor::file/file_name"/>
        <xsl:value-of select="','"/>
		<xsl:value-of select="@key"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="@duplex"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="@envelope_number"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="start_page"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="page_count"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/perf_sheet"/>
		<xsl:value-of select="','"/>				
		<xsl:value-of select="print/insert_bin_1"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_2"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_3"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_4"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_5"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/insert_bin_6"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_name"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_1"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_2"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_3"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_4"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_5"/>
		<xsl:value-of select="','"/>
		<xsl:value-of select="print/add_6"/>
		<xsl:value-of select="','"/>
		<xsl:text>
</xsl:text>
	</xsl:template>
</xsl:stylesheet>

Open in new window

0
 
LVL 15

Author Comment

by:qz8dsw
ID: 39941519
Thank you VERY much for your help and patience.
0
 
LVL 15

Author Comment

by:qz8dsw
ID: 39941523
Fast and great answer.
Thank you VERY much, by this simple effort by you have have learned quite a lot.
0
 
LVL 15

Author Closing Comment

by:qz8dsw
ID: 39941545
Ahhh, thank you Mccarl.
Yes, Geert provided the majority of the solution to my issue and some clarification.
Thank you BOTH for your help on this at such short notice.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39941686
Welcome,
(I went to bed before your follow up came in, that is why I left it)
@mccarl, thanks for stepping in, both technically and administratively
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39942156
No worries, glad to help! :)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
c#, split 12 81
Problem to event 3 102
read an xml file in perl 2 49
XML response optional elements 12 49
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 …
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

792 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