[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Moving around in XML using XSL

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
qz8dsw
Asked:
qz8dsw
  • 5
  • 3
  • 2
2 Solutions
 
Geert BormansInformation ArchitectCommented:
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
 
Geert BormansInformation ArchitectCommented:
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
 
qz8dswAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
qz8dswAuthor Commented:
Attached is a new XML file with multiple file nodes.
Statements-20140225131628-index.xml
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
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
 
qz8dswAuthor Commented:
Thank you VERY much for your help and patience.
0
 
qz8dswAuthor Commented:
Fast and great answer.
Thank you VERY much, by this simple effort by you have have learned quite a lot.
0
 
qz8dswAuthor Commented:
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
 
Geert BormansInformation ArchitectCommented:
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
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
No worries, glad to help! :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now