Solved

Moving around in XML using XSL

Posted on 2014-03-19
11
218 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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, …
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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

21 Experts available now in Live!

Get 1:1 Help Now