views:

129

answers:

1

Hello all,
I am trying to change an XML file from one format to another and have no clue as to how to write a script for it. Can someone help, please?
The source file looks like this:

<Record>
     <FieldValue fieldName="rapportage_nihil" fieldValue="false" fieldValueIsNull="false" fieldValueNatural="false"/>
     <FieldValue fieldName="periode" fieldValue="2009-23-31" fieldValueIsNull="false" fieldValueNatural="2009-10-31 00:23:23"/>
     <FieldValue fieldName="formulierid" fieldValue="9001HK1V10" fieldValueIsNull="false" fieldValueNatural="9001HK1V10"/>
     <FieldValue fieldName="versie" fieldValue="1" fieldValueIsNull="false" fieldValueNatural="1"/>
     <FieldValue fieldName="frequentie" fieldValue="M" fieldValueIsNull="false" fieldValueNatural="M"/>
     <FieldValue fieldName="variant_type" fieldValue="Landen" fieldValueIsNull="false" fieldValueNatural="Landen"/>
     <FieldValue fieldName="value" fieldValue="5F" fieldValueIsNull="false" fieldValueNatural="5F"/>
     <FieldValue fieldName="post_value" fieldValue="0.00" fieldValueIsNull="false" fieldValueNatural="1.037E-4"/>
     <FieldValue fieldName="cube" fieldValue="c01" fieldValueIsNull="false" fieldValueNatural="c01"/>
     <FieldValue fieldName="rij" fieldValue="r_24_100_1_000_0" fieldValueIsNull="false" fieldValueNatural="r_24_100_1_000_0"/>
     <FieldValue fieldName="kolom" fieldValue="c_2250_SPU" fieldValueIsNull="false" fieldValueNatural="c_2250_SPU"/>
</Record>
<Record>
     <FieldValue fieldName="rapportage_nihil" fieldValue="false" fieldValueIsNull="false" fieldValueNatural="false"/>
     <FieldValue fieldName="periode" fieldValue="2009-23-31" fieldValueIsNull="false" fieldValueNatural="2009-10-31 00:23:23"/>
     <FieldValue fieldName="formulierid" fieldValue="9001HK1V10" fieldValueIsNull="false" fieldValueNatural="9001HK1V10"/>
     <FieldValue fieldName="versie" fieldValue="1" fieldValueIsNull="false" fieldValueNatural="1"/>
     <FieldValue fieldName="frequentie" fieldValue="M" fieldValueIsNull="false" fieldValueNatural="M"/>
     <FieldValue fieldName="variant_type" fieldValue="Landen" fieldValueIsNull="false" fieldValueNatural="Landen"/>
     <FieldValue fieldName="value" fieldValue="5F" fieldValueIsNull="false" fieldValueNatural="5F"/>
     <FieldValue fieldName="post_value" fieldValue="0.00" fieldValueIsNull="false" fieldValueNatural="1.037E-4"/>
     <FieldValue fieldName="cube" fieldValue="c01" fieldValueIsNull="false" fieldValueNatural="c01"/>
     <FieldValue fieldName="rij" fieldValue="r_24_108_0_000_0" fieldValueIsNull="false" fieldValueNatural="r_24_108_0_000_0"/>
     <FieldValue fieldName="kolom" fieldValue="c_2250_SPU" fieldValueIsNull="false" fieldValueNatural="c_2250_SPU"/>
</Record>
<Record>
    <FieldValue fieldName="rapportage_nihil" fieldValue="false" fieldValueIsNull="false" fieldValueNatural="false"/>
    <FieldValue fieldName="periode" fieldValue="2009-23-31" fieldValueIsNull="false" fieldValueNatural="2009-10-31 00:23:23"/>
    <FieldValue fieldName="formulierid" fieldValue="9001HK1V10" fieldValueIsNull="false" fieldValueNatural="9001HK1V10"/>
    <FieldValue fieldName="versie" fieldValue="1" fieldValueIsNull="false" fieldValueNatural="1"/>
    <FieldValue fieldName="frequentie" fieldValue="M" fieldValueIsNull="false" fieldValueNatural="M"/>
    <FieldValue fieldName="variant_type" fieldValue="Landen" fieldValueIsNull="false" fieldValueNatural="Landen"/>
    <FieldValue fieldName="value" fieldValue="5F" fieldValueIsNull="false" fieldValueNatural="5F"/>
    <FieldValue fieldName="post_value" fieldValue="0.00" fieldValueIsNull="false" fieldValueNatural="1.6049E-4"/>
    <FieldValue fieldName="cube" fieldValue="c01" fieldValueIsNull="false" fieldValueNatural="c01"/>
    <FieldValue fieldName="rij" fieldValue="r_06_000_1_010_0" fieldValueIsNull="false" fieldValueNatural="r_06_000_1_010_0"/>
    <FieldValue fieldName="kolom" fieldValue="c_2250_SPU" fieldValueIsNull="false" fieldValueNatural="c_2250_SPU"/>
</Record>

This is the format that I need as a result:

<bestand registratienummer="123">
<rapportage nihil="false" periode="2009-23-31" formulierid="9001HK1V10" versie="1" frequentie="M">
        <variant type="Landen" value="5F" />
        <post value="0.00" cube="c01" rij="r_24_100_1_000_0" kolom="c_2250_SPU" />
</rapportage>
<rapportage nihil="false" periode="2009-23-31" formulierid="9001HK1V10" versie="1" frequentie="M">
        <variant type="Landen" value="5F" />
        <post value="0.00" cube="c01" rij="r_24_108_0_000_0" kolom="c_2250_SPU" />
</rapportage>
<rapportage nihil="false" periode="2009-23-31" formulierid="9001HK1V10" versie="1" frequentie="M">
        <variant type="Landen" value="5F" />
        <post value="0.00" cube="c01" rij="r_06_000_1_010_0" kolom="c_2250_SPU" />
</rapportage>
</bestand>  

Thanks very much!

+2  A: 

XSLT is made for exactly that purpose. You could transform any well-defined XML file into any other plain-text format, including a different XML structure. Get e.g. xsltproc, write a script and you're done.

If you need to learn XSLT first, here's a good starting point: http://www.w3schools.com/xsl/ (this tutorial gives examples for a transformation to XHTML, but you just have to use your target XML tags instead of XHTML tags...).

This might be a good script to start from (sorry, but I don't have time to give you a fully-fledged script here):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    <xsl:output method="xml" encoding="utf-8" />
    <xsl:template match="/">
        <bestand registratienummer="123">
            <apply-templates select="//Record"/>
        </bestand>
    </xsl:template>
    <xsl:template match="Record">
        <xsl:variable name="nihil" select="FieldValue[@fieldName='rapportage_nihil']/@fieldValue"/>
        <!-- add more variable lookups here. you need XPath for that. -->
        <rapportage nihil="{$nihil}" periode="{$periode}">
            <!-- add more output here -->
        </rapportage>
    </xsl:template>
</xsl:stylesheet>

You could give xsltproc this stylesheet and your source XML document, it'll give you your new XML file. Maybe you need to make sure your source XML file is well-defined, i.e. contains one root element. Your example does not look like it's got one, you may need to surround it with an arbitrary element.

To elaborate a bit on the XSLT and the script: Imagine you have two pointers. One in the XSLT script, one in your XML source file. XSLT is all about "applying templates" and it would always use the template that is the closest match (Think of templates as some kind of function).

At first, the closest match would be the root element, so the XSLT pointer would start in the template with match="/". The XML pointer is in the root element (alias "tag") of the XML source document. Any element that's not starting with xsl: would be considered as an output element. So, first of all, xsltproc would output the <bestand> element.

Then the XSLT pointer goes further and finds <apply-templates> selecting all elements matching //Record (the double slash denotes "in any depth"). For each <Record> element, it would "call" the matching template. Here the closest match would be <xsl:template match="Record">.

So the XSLT pointer would jump to the template, the XML pointer to the first <Record>. Then a variable (nihil) is declared, containing some information from the record which is selected from the XML pointer onwards deeper into the XML structure of the source document using an XPath expression. The XPath expression says: "Select from the element FieldValue which has an attribute fieldName with the value 'rapportage_nihil' the value of the attribute fieldValue" (Think of it like an SQL WHERE clause for XML data; Attributes are prefixed here with @).

You need to copy, paste and change that line for every information you need in your output document. Then the output element <rapportage> is evaluated, substituting every variable (e.g. {$nihil}) with their respective values set above with <xsl:variable>. You need to add the elements inside the <rapportage> element in the same fashion as the <rapportage> element. Then the template is at it's end and the next <Record> will be put through the template.

maligree
Thanks very much for your help! I have no clue about how to write XSLT scripts, but I'll see if I can modify your example.
JonS
I tried to see if I can understand what your script is doing, but I'm sorry, I have no clue as to what to do with it. Thank you for your time.Can somebody else help with the script?
JonS
@JonS: Ok, I've added a bit of explanation how the script works, but from here on, I think you need to experiment yourself, read how you use XSLT and xsltproc or any other XSLT processor (xsltproc is a command-line utility, no graphical interface). Or maybe you'd have to pay someone to write the script, if you don't have the time to learn XSL(T).
maligree
maligree - I modified your script, had some issues, but was able to overcome them with some help and create the file I need. Your explanation was also very helpful.Thanks very much!
JonS
@JonS: Then please flag the answer as correct. It's just the way stackoverflow.com works :)And thanks for reporting back that I actually could help you :)
maligree