Metadata to csv using XSLT

OL Connect Professional Workflow
1

I’m searching for a fast way to write out the xml structure to a accompanying csv.

Source XML (metadata):

<?xml version="1.0" encoding="utf-8"?>
    <ol:Jo Version="2.1" xmlns:ol="olmeta2" xmlns:xs="http://www.w3.org/2001/XMLSchema/">
        <ol:At/>
    	<ol:Fi>
    		<_vger_task_id>1</_vger_task_id>
    	</ol:Fi>
    	<ol:Gr Selected="true">
    		<ol:At/>
    		<ol:Fi>
    			<_vger_recordset_id>3384305</_vger_recordset_id>
    		</ol:Fi>
    		<ol:Do Selected="true">
    			<ol:At/>
    			<ol:Fi>
    				<_vger_record_id>1549540</_vger_record_id>
    				<_vger_fld_KdNr>12091038</_vger_fld_KdNr>
    				<_vger_fld_RechNr>200007063983</_vger_fld_RechNr>
    				<_vger_fld_ExtraData/>
    			</ol:Fi>
    			<ol:Da Offset="" RealIndex="">
    				<ol:At/>
    				<ol:Fi/>
    				<ol:Pa>
    					<ol:At/>
    					<ol:Fi/>
    				</ol:Pa>
    			</ol:Da>
    		</ol:Do>
    		<ol:Do Selected="true">
    			<ol:At/>
    			<ol:Fi>
    				<_vger_record_id>1549541</_vger_record_id>
    				<_vger_fld_KdNr>12091158</_vger_fld_KdNr>
    				<_vger_fld_RechNr>200007063996</_vger_fld_RechNr>
    				<_vger_fld_ExtraData/>
    			</ol:Fi>
    			<ol:Da Offset="" RealIndex="">
    				<ol:At/>
    				<ol:Fi/>
    				<ol:Pa>
    					<ol:At/>
    					<ol:Fi/>
    				</ol:Pa>
    			</ol:Da>
    		</ol:Do>
    		<ol:Do Selected="true">
    			<ol:At/>
    			<ol:Fi>
    				<_vger_record_id>1549542</_vger_record_id>
    				<_vger_fld_KdNr>12091367</_vger_fld_KdNr>
    				<_vger_fld_RechNr>200007064005</_vger_fld_RechNr>
    				<_vger_fld_ExtraData/>
    			</ol:Fi>
    			<ol:Da Offset="" RealIndex="">
    				<ol:At/>
    				<ol:Fi/>
    				<ol:Pa>
    					<ol:At/>
    					<ol:Fi/>
    				</ol:Pa>
    			</ol:Da>
    		</ol:Do>
    	</ol:Gr>
    </ol:Jo>

XSLT:

<xsl:stylesheet version=“1.0” xmlns:xsl=“XSLT Namespace”
xmlns:ol=“XSLT Namespace”>
<xsl:output method=“text” />
<xsl:variable name=“separator” select=“‘;’” />
<xsl:variable name=“newline” select=“’ '” />

<xsl:template match="/Jo/Gr/Do">
    <xsl:text>ID;KndNr;RechNr</xsl:text>
    <xsl:value-of select="$newline" />
    <xsl:for-each select="/Fi">
        <xsl:value-of select="_vger_record_id" />
        <xsl:value-of select="$separator" />
        <xsl:value-of select="_vger_fld_KdNr" />
        <xsl:value-of select="$separator" />
        <xsl:value-of select="_vger_fld_RechNr" />
        <xsl:value-of select="$newline" />
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

What am I doing wrong?

Ralf.

2

Ah, the darn namespaces in XSLT… :stuck_out_tongue:
First, you have to specify in your XSLT the same ol namespace as in the XML file. So in your namespace declaration, you must use xmlns:ol="olmeta2"

Then, in your template match, you also need to specify the namespace for each field, even when they are nested: <xsl:apply-templates select="//ol:Jo/ol:Gr/ol:Do/ol:Fi" />

Finally, no need to use a for-each since the above statement already retrieves all ol:Fi elements.

So the following should work:

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ol="olmeta2">

  <xsl:output method="text" />

  <xsl:template match="/">
    <xsl:text>ID;KndNr;RechNr</xsl:text>
    <xsl:text>&#10;</xsl:text>
    <xsl:apply-templates select="//ol:Jo/ol:Gr/ol:Do/ol:Fi" />
  </xsl:template>

  <xsl:template match="ol:Fi">
    <xsl:value-of select="_vger_record_id" />
    <xsl:text>;</xsl:text>
    <xsl:value-of select="_vger_fld_KdNr" />
    <xsl:text>;</xsl:text>
    <xsl:value-of select="_vger_fld_RechNr" />
    <xsl:text>&#10;</xsl:text>
  </xsl:template>


</xsl:stylesheet>
3

Phil, GREAT!

to be honest I’m at war with XSLT transformations, but once they run, it’s the fastest way…

Thx a lot!

Ralf.

4

one question… do you know If I can use datamappers Metadata AND XML structure the same time?**
I want to use the XML structure for XSLT transformation, keeping the metadata for further output processing.

Ralf

**Addendum: without writing the XML to a temp file re-reading it back…

5

If the XML file is of a reasonable size, you can store it in a variable or a JobInfo. Otherwise, you’ll have to use a temporary file