Transforming XML to HTML in Access with VBA

Banana said:
I'm glad you got it to work, but why the acImportHTML? Why not just Transform then Import? If you use the XSLT to change the containing node, it'll append the data to the previously created tables without missing element (columns)?
Click to expand...

I have no idea why. :) Just seemed logical. I just have no idea what you mean.

Here's part of my xsl:

<?xml version='1.0'?>
<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html" version="4.0" encoding="ISO-8859-1" indent="yes"/>

<xsl:template match="/">


<html>
<body>

<table>
<tr>
<th>Debiteurnummer</th>
<th>Naam</th>
<th>Adres</th>
<th>Huisnummer</th>
Click to expand...

and

</tr>
<xsl:for-each select="Factuur/TY_OUTPUT">
<tr>

<td><xsl:value-of select="normalize-space(ZP_NR)"/></td>
<td><xsl:value-of select="normalize-space(ZP_NAAM)"/></td>
<td><xsl:value-of select="normalize-space(ZP_ADRES)"/></td>
<td><xsl:value-of select="normalize-space(ZP_HN)"/></td>
<td><xsl:value-of select="normalize-space(ZP_POSTC)"/></td>
<td><xsl:value-of select="normalize-space(ZP_PLAATS)"/></td>
Click to expand...

But now I've run into a different snag. negatives are displayed like this in de XML: 1234.15- Now I can use an import specification to replace the . with a , But it doesn't reckognize the negatives as numbers, thus import errors. :(
 
Joyce said:
I have no idea why. :) Just seemed logical. I just have no idea what you mean.
Click to expand...

Maybe an example will help.

If I have this input XML:
Code: 
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<soap:Body>
		<soap:Fault>
			<soap:Code>
				<soap:Value>soap:Receiver</soap:Value>
			</soap:Code>
			<soap:Reason>
				<soap:Text xml:lang="en">Server was unable to process request. ---> There is no Unicode byte order mark. Cannot switch to Unicode.</soap:Text>
			</soap:Reason>
			<soap:Detail />
		</soap:Fault>
	</soap:Body>
</soap:Envelope>

I run it through my XSLT which changes how the nodes are defined:
Code: 
<?xml version="1.0"?>
<tmpSoapResponse xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
	<Success>false</Success>
	<ErrorMessage>Server was unable to process request. ---> There is no Unicode byte order mark. Cannot switch to Unicode.</ErrorMessage>
	<ErrorCode>soap:Receiver</ErrorCode>
        <ErrorDetail></ErrorDetail>
</tmpSoapResponse >

Note that ErrorDetail is empty but is contained in the XML. The "tmpSoapResponse" is actually also the name of a table in my database and it has all those columns already defined. I then run this VBA code:

Code: 
    Application.TransformXML XMLFilePath, XSLTFilePath, XMLFilePath, True, acDisableScript
    Application.ImportXML XMLFilePath, acAppendData

This will insert the transformed XML's data into the table directly. No exporting to HTML then importing it into table needed. Did it make sense now?

But now I've run into a different snag. negatives are displayed like this in de XML: 1234.15- Now I can use an import specification to replace the . with a , But it doesn't reckognize the negatives as numbers, thus import errors. :(
Click to expand...

Is that due to importing as HTML? I'm sure you can avoid it if you import the XML directly as I shown above?
 
Hmm, my XML looks totally different.

This is what we receive from our suppliers (data has been altered):

- <Factuur>
- <TY_OUTPUT>
<ZP_NR Datatype="C" Length="10">123</ZP_NR>
<ZP_NAAM Datatype="C" Length="40">Joyce</ZP_NAAM>
<ZP_ADRES Datatype="C" Length="25">Postbus</ZP_ADRES>
<ZP_HN Datatype="C" Length="10">1</ZP_HN>
<ZP_POSTC Datatype="C" Length="10">1234 AB</ZP_POSTC>
<ZP_PLAATS Datatype="C" Length="25">Amsterdam</ZP_PLAATS>
<EANCODE Datatype="C" Length="50">123456789101112</EANCODE>
<EAN_NAAM Datatype="C" Length="40">Joyce</EAN_NAAM>
<EAN_ADRES Datatype="C" Length="25">Hoofdstraat</EAN_ADRES>
<EAN_HN Datatype="C" Length="10">1</EAN_HN>
<EAN_POSTC Datatype="C" Length="10">1234 AB</EAN_POSTC>
<EAN_PLAATS Datatype="C" Length="25">AMSTERDAM</EAN_PLAATS>
<CONTR_NR Datatype="C" Length="10">000012345</CONTR_NR>
<PRODUKT Datatype="C" Length="2">02</PRODUKT>
<INH_DATUM Datatype="D" Length="8">20070101</INH_DATUM>
<UITH_DATUM Datatype="D" Length="8">99991231</UITH_DATUM>
<FACTUURNUMMER Datatype="C" Length="12">000045799080</FACTUURNUMMER>
<FACT_DATUM Datatype="D" Length="8">20100426</FACT_DATUM>
<VERZ_FACT_NR Datatype="C" Length="12">000031088583</VERZ_FACT_NR>
<GEBR_VOORSCH Datatype="P" Length="7" Decimals="2">100.15</GEBR_VOORSCH>
<GEBR_VOORSCH_BTW Datatype="P" Length="7" Decimals="2">15.152</GEBR_VOORSCH_BTW>
<FACT_BEDR_EX_BTW Datatype="C" Length="18">85.15</FACT_BEDR_EX_BTW>
<FACT_BTW_BEDR Datatype="C" Length="18">15.15</FACT_BTW_BEDR>
<FACT_BEDR_TOT Datatype="C" Length="18">300.00</FACT_BEDR_TOT>
</TY_OUTPUT>
Click to expand...

Now there are around 80 of these in one XML, this one has only a few nodes, there are some invoices with twice as many nodes. When importing just one invoice I have no problems, when trying to import the entire XML Access doesn't reckognize all nodes. Now I would love to try and use xls to transform from xml to xml, but its not very clear till now.
 
namliam said:
xslt is Excel offcourse, why utilize a outside source when access itself can do it just fine (even if with a little help :) )
The "best" format of xml would be somthing like so:
<name>namliam</name>
<Lastname>namliam</Lastname>
<City>namliam</City>
<Workplace>Zeker geen groningen</Workplace>
Nicely one field/column per line
Click to expand...
If this is what your data actually look like I would 'simply' make my own reading code.

Simply open the file, read it line by line, check what the first value is (i.e.
<ZP_POSTC Datatype="C" Length="10"> ) and handle accoordingly

Pseudo code:
if zp_nr then make new record, table.zp_nr = this value
if ZP_POSTC then Table.Postcode = this value
If Fact_datum then table.fact_datum = this value
If ...
Next line
 
You must log in or register to reply here.

Similar threads

J
XLS File required to Transform XML File
Replies
0
Views
731
JohanDT
J
B
Add-In Load Time Registry Binary Value Conversion to Decimal
2
Replies
35
Views
6,096
Bilbo_Baggins_Esq
B
Rx_
Use Access VBA to import XML and Stylesheet to local drive
Replies
1
Views
25,993
Rx_
Rx_
D
XSL Transform only allows Access to import the last XML Value
Replies
2
Views
4,415
ddeaton1987
D
D
Problem importing .XML file into Access 2010 with a .XSL via transform
Replies
3
Views
3,617
ddeaton1987
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom