XSL transform MS Access 2007 output

Question

I am exporting data from Access on regular basis. So far I used to export it and manually edit some tags to adjust it to Clients needs. Lately I found that that there is posibility to use XSL as a transformation pattern.

I am still beginner when comes to XSL but managed to create something like this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="dataroot/Kwerenda_x0020_Nota_x0020_Kredytowa">
    <xsl:element name="Faktury_od_nas">
        <xsl:apply-templates />
    </xsl:element>
</xsl:template>

</xsl:stylesheet>

Generally it works ok but:
1. Normally when I export data it gives me every tag in a new line, now it is divided only by tag I changed.
2. I do not know how to rename dataroot. I tried to copy/paste same code but then I get dataroot everytime Faktury_od_nas appears...

Transformed sample data:

<dataroot generated="2016-01-12T13:54:11" xmlns:od="urn:schemas-microsoft-com:officedata"><Faktury_od_nas><No>1</No><InvoiceDate>20150715</InvoiceDate><InvoiceNumber>12345</InvoiceNumber><CustVATNumber>LT100004645417</CustVATNumber><E100customerKey>65-92</E100customerKey><CustomerName>Client_name</CustomerName><InvoiceCountry>BE</InvoiceCountry><VATpersent>21</VATpersent><VATBasis>106,36</VATBasis><VATamount>22,34</VATamount><Currency>EUR</Currency><VAT_x0020_recovery_x0020_fee_x0020_rate_x0020__x0028__x0025__x0029_>7.5</VAT_x0020_recovery_x0020_fee_x0020_rate_x0020__x0028__x0025__x0029_><Service_x0020_Type>Express</Service_x0020_Type><InvoiceScanFileName>scan_name</InvoiceScanFileName>
    </Faktury_od_nas></dararoot>

Desired sample data:

<Faktura>
<Faktury_od_nas>
<No>1</No>
<InvoiceDate>20150715</InvoiceDate>
<InvoiceNumber>12345</InvoiceNumber>
<CustVATNumber>LT100004645417</CustVATNumber>
<E100customerKey>65-92</E100customerKey>
<CustomerName>Client_name</CustomerName>
<InvoiceCountry>BE</InvoiceCountry>
<VATpersent>21</VATpersent>
<VATBasis>106,36</VATBasis>
<VATamount>22,34</VATamount>
<Currency>EUR</Currency>
<VAT_x0020_recovery_x0020_fee_x0020_rate_x0020__x0028__x0025__x0029_>7.5</VAT_x0020_recovery_x0020_fee_x0020_rate_x0020__x0028__x0025__x0029_>
<Service_x0020_Type>Express</Service_x0020_Type>
<InvoiceScanFileName>scan_name</InvoiceScanFileName>
</Faktury_od_nas>
</Faktura>

I appreciate any help.

Edit:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" />

<xsl:template match="*">
    <xsl:element name="{local-name()}">
        <xsl:apply-templates/>
    </xsl:element>
</xsl:template>

<xsl:template match="dataroot">
    <Faktura>
        <xsl:apply-templates />
    </Faktura>
</xsl:template>

<xsl:template match="Kwerenda_x0020_Nota_x0020_Kredytowa">
    <Faktury_od_nas>
        <xsl:apply-templates />
    </Faktury_od_nas>
</xsl:template>

</xsl:stylesheet>

Your desired output is not well-formed XML! – michael.hor257k Jan 12 '16 at 13:13 — michael.hor257k, Jan 12 '16 at 13:13

michael.hor257k · Accepted Answer · 2016-01-12T13:30:06.163

2

It would help seeing your source XML, but I believe you could use:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="*">
    <xsl:element name="{local-name()}">
        <xsl:apply-templates/>
    </xsl:element>
</xsl:template>

<xsl:template match="dataroot">
    <Faktura>
        <xsl:apply-templates />
    </Faktura>
</xsl:template>

<xsl:template match="Kwerenda_x0020_Nota_x0020_Kredytowa">
    <Faktury_od_nas>
        <xsl:apply-templates />
    </Faktury_od_nas>
</xsl:template>

</xsl:stylesheet>

edited Jan 12 '16 at 13:30

answered Jan 12 '16 at 13:12

michael.hor257k

113,275
6
33
51

Almost there. As you can see in a `transformed sample data`, `dataroot` has some additional information: `generated=""` and `xmlns:od=""`. When I applied your code I get in every child of `Faktury_od_nas` information `xmlns:od=""`. It should just erased from `dataroot`. – lowak Jan 12 '16 at 13:27
Only one small detail remained. First line now looks like: ``, it misses encoding information: ``. Any chance fixing this? – lowak Jan 12 '16 at 13:44
Are you sure you have the `encoding` attribute on the `xsl:output` element? – michael.hor257k Jan 12 '16 at 13:46
I cannot reproduce your problem. The XSLT 1.0 standard requires the XML declaration to include both version information and an encoding declaration. Perhaps you need to set up your XSLT processor to conform - I am afraid I wouldn't know anything about that. – michael.hor257k Jan 12 '16 at 13:54
Well XSLT processor is MS Access. I click export to XML -> filename -> transform -> select file and done. When I was using my code I received `encoding="UTF-16"` (no idea why UTF-16...). When started using your code encoding part was lost. – lowak Jan 12 '16 at 14:03
I suggest you post a separate question, tagged with MS Access. This is not about XSLT as such and, as I said, I don't know anything about Access. – michael.hor257k Jan 12 '16 at 14:10
It is marked as MS Access from the beginning (title and tag). Anyway thank you for efforts. Maybe I will find out what is wrong later and write here. You were a great help (now only encoding part is missing!). – lowak Jan 12 '16 at 14:12
Ah, right. I would still post this as a separate question. – michael.hor257k Jan 12 '16 at 14:13
Use @michael.hor257k's answer, but separate the processing with VBA code (not via ribbon icon): 1) Export XML with [Application.ExportXML](https://msdn.microsoft.com/en-us/library/office/ff193212.aspx) and 2) Transform XML with [Application.TransformXML](https://msdn.microsoft.com/en-us/library/office/ff844810.aspx) which can be limited, so consider using the [VBA MSMXL](http://stackoverflow.com/questions/33921974/ms-access-to-xml-saved-export-produces-different-xml-structure-when-re-exporte/33947818#33947818) approach (loading your XSLT externally or embedded as a VBA string). – Parfait Jan 12 '16 at 16:29
@Parfait, I do not actually want to use VBA. So far I did all the job but someone is going to replace me (for now this going to be a person having no programming knowledge and so I need to make it in a different way than usual). – lowak Jan 13 '16 at 10:26
I have found similar topic or at least talking about my problem, I guess it is not possible to receive UTF-8 that easily. http://stackoverflow.com/questions/5302858/xslcompiledtransform-uses-utf-16-encoding – lowak Jan 13 '16 at 10:27
It seems like has to be changed manually or using VBA procedure recommended by Parfait. – lowak Jan 13 '16 at 10:28
Anyway, for now I am going to accept your anwser :) – lowak Jan 13 '16 at 11:53

XSL transform MS Access 2007 output

1 Answers1