Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Moving around in XML using XSL

Posted on 2014-03-19
11
Medium Priority
?
231 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
[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
  • 5
  • 3
  • 2
11 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 1600 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Author Comment

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

Assisted Solution

by:mccarl
mccarl earned 400 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 36

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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