XML Output Templates

[JayKappy]

I am at my end....and need help...I appoligize for my ignorance but I am very new to this.

What I need is to create an XML file from Access with a Specific Format. I can export out of Access now with this:

=====================================================
CODE:

Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="Events", _
DataTarget:="Events.xml", _
SchemaTarget:="EventsSchema.xml"

======================================================


Although this does not satisfy the format that I need. I have read about XSL style sheets etc
1. But cannot figure out how create them.
2. How to invoke them.

This is the format that I am tryign to get my XML file into.


========================================================
XML FORMAT:

<?xml version="1.0" encoding="utf-8" ?>
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel>
<title>Mount Saint Helens - Mount Margaret Trail</title>
<link></link>
<description>Trailheads and campsites in the Mount Margaret area of Mount Saint Helens, WA</description>
<mappointIntlCode>cht</mappointIntlCode>
<item>
<title>THIS IS THE TITLE</title>
<description> THIS IS THE DESCRIPTION</description>
<geo:lat>45.09485</geo:lat>
<geo:long>-93.44696</geo:long>
<icon>PUSHPIN.gif</icon>
</item>
</channel>
</rss>

===========================================================


IS THERE ANYONE OUT THERE THAT CAN HELP ME:

How to create a template
How to invoke it
How to create an XML file in the format above

I WOULD BE SO VERY MUCH APPRECIATIVE....

THANK YOU

 

[k5tm]

Didnt see the link "Process TGML"

Look immediately below the box in which you entered your response. Or just click here.

Try this:

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

<xsl:template match="dataroot">
   <rss version="2.0" xmlns:geo="[URL unfurl="true"]http://www.w3.org/2003/01/geo/wgs84_pos#"[/URL] xmlns:mappoint="[URL unfurl="true"]http://virtualearth.msn.com/apis/annotate#">[/URL]   
       <channel>  
            <xsl:apply-templates select="item"/>
       </channel>
   </rss>
</xsl:template>

<xsl:template match="item">
     <item>
         <title><xsl:value-of select="Title"/></title>
         <description><xsl:value-of select="Description"/></description>
         <icon><xsl:value-of select="Icon"/></icon>
     </item>
</xsl:template>

</xsl:stylesheet>

Tom Morrison

http://www.liant.com

 

[JayKappy]

ahhhhhhhhhhhhh I was so close...I was horsing around with the rss adfter you mentioned it. ALthough I was placing it near the <xsl:stylesheet and it was giving me errors.

Ok now...I saw that you repeated the encoding="UTF-8" twice. My Output 1 did not have this represented.

I then deleted it from after the indent="yes" and then I got the OUTPUT 2 below...why is now saying 16 instead of 8

OUTPUT 1:
<?xml version="1.0"?>
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel>
<item>
<title>01NE-049</title>
<description>COLLECT DATA</description>
<icon>ddd.jpg</icon>
</item>



OUTPUT 2:
<?xml version="1.0" encoding="UTF-16"?>
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel>
<item>
<title>01NE-049</title>
<description>COLLECT DATA</description>
<icon>ddd.jpg</icon>
</item>
</channel>
</rss>




I am now going to try and test the geo:lat geo:long issue

THank you very much

 

[JayKappy]

Well I am getting there.....

I reference the field geolat (my latitude coordinate) and I am getting an error....I appolgize for my ignorance....

Error:
Reference to undeclared namespace prefix:'geo'

See Below
<geo:lat><xsl:value-of select="geolat"/></geo:lat>

geolat is the field name
I need the tag to read geo:lat


XSL:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="

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

<xslutput method="xml" version="1.0" indent="yes"/>

<xsl:template match="dataroot">
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel>
<xsl:apply-templates select="item"/>
</channel>
</rss>
</xsl:template>

<xsl:template match="item">
<item>
<title><xsl:value-of select="Title"/></title>
<description><xsl:value-of select="Description"/></description>
<icon><xsl:value-of select="Icon"/></icon>
<geo:lat><xsl:value-of select="geolat"/></geo:lat>
</item>
</xsl:template>

</xsl:stylesheet>

 

[k5tm]

Put the namespace declarations on the xsl:stylesheet element.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
     xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL]
     xmlns:geo="[URL unfurl="true"]http://www.w3.org/2003/01/geo/wgs84_pos#"[/URL]                                    
     xmlns:mappoint="[URL unfurl="true"]http://virtualearth.msn.com/apis/annotate#">[/URL]
<xsl:output method="xml" version="1.0" indent="yes" encoding="utf-8" />
...

Tom Morrison

http://www.liant.com

 

[JayKappy]

I was confused because we just placed the namespace in the RSS section I didnt think it had to be repeated in the xml:stylesheet section.....

Sweet....Im up and running...now for the automated part of this...going to look at that link you put on above nad see if I can figure this out....for a nubee with XML I have to say that I plead my ignorance....and have learned tons so far....getting easier every day....

I thank you very much for your patience and help....it is very appreciated....

What does the encoding="UTF-8" do??????

 

[k5tm]

This seems to be a decent write-up.

Tom Morrison

http://www.liant.com

 

[JayKappy]

This is what I am trying for the Automatic Export.

Private Sub Command2_Click()
Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="ITEM", _
PresentationTarget:="F:\TEMP\TemplateNew_WORKING_Test.xsl", _
DataTarget:="F:\TEMP\ExportsXML.xml"

End Sub



RESULTS:
1. I get an XML output for not in the format that I get when I run it manually. For soem reason It goes back to the Dataroot stuff...so that seems to me that it is not using the XSL file.
2. In addition It overwrites the XSL file that I am using to format the output.

Any thoughts? I looked at the examples on that site and this is about as close as I have come....Maybe you can put your eyes on it adn figure out why its not using the XSL file and why it is overwriting the XSL file?



XML OUTPUT:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata" generated="2007-03-26T16:22:33">
<ITEM>
<Title>01NE-049</Title>
<Description>COLLECT DATA</Description>
<Icon>ddd.jpg</Icon>
<geolat>45.124</geolat>
<geolong>-93.223</geolong>
</ITEM>


XSL OUTPUT: I CANT PUT IT ALL IN HERE IS WAY TO MUCH

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

http://www.w3.org/1999/XSL/Transform"

xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:fx="#fx-functions" exclude-result-prefixes="msxsl fx">
<xslutput method="html" version="4.0" indent="yes" xmlns:xsl="

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

<xsl:template match="//dataroot" xmlns:xsl="

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

<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/>
<title>ITEM</title>
<style type="text/css"></style>
</head>
<body link="#0000ff" vlink="#800080">
<table border="1" bgcolor="#ffffff" cellspacing="0" cellpadding="0" id="CTRL1">
<colgroup>
<col style="WIDTH: 1.302in"/>
<col style="WIDTH: 1.5416in"/>
<col style="WIDTH: 0.9375in"/>
<col style="TEXT-ALIGN: right; WIDTH: 0.9375in"/>
<col style="TEXT-ALIGN: right; WIDTH: 0.9375in"/>
</colgroup>
<tbody>
<tr>
<td>
<div align="center">
<strong>Title</strong>
</div>
</td>
<td>
<div align="center">
<strong>Description</strong>
</div>
</td>
<td>
<div align="center">
<strong>Icon</strong>
</div>
</td>
<td>
<div align="center">
<strong>geolat</strong>
</div>
</td>
<td>
<div align="center">
<strong>geolong</strong>
</div>
</td>
</tr>
</tbody>

 

[k5tm]

You do not want to use ExportXML. Please read the above-cited (23 Mar 07 16:19 post) Microsoft reference, and find near the bottom of the page, TransformXML.

Tom Morrison

http://www.liant.com

 

[JayKappy]

Yea I was reading that section...No real example...its hard when you are new at something and they dont show examples...

Am I correct to think that because it is not in the Export/Import Method that I have to run an Export, create the XML and then use the TransformXML Method?

1. I ran a basic export to create the XML
2. I then used the code below to point to that XML and apply the Transformation

The XML file that is created again references Dataroot etc. Do I have to rewrite my XSL file to strip all of that?

NO DATA after the Transform

I think this is how its supposed to be done...the web site has no examples....

This is what I am trying:
Application.TransformXML _
DataSource:="F:\TEMP\ExportsXML.xml", _
TransformSource:="F:\TEMP\TemplateNew_WORKING_Test.xsl", _
OutputTarget:="F:\TEMP\ExportsXML_Final.xml"


XML OUTPUT:
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata" xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="ExportsXMLSchema.xml" generated="2007-03-27T08:24:45">
<ITEM>
<Title>01NE-049</Title>
<Description>COLLECT DATA</Description>
<Icon>ddd.jpg</Icon>
<geolat>45.124</geolat>
<geolong>-93.223</geolong>
</ITEM>
</dataroot>


TRANSFORM RESULT:
<?xml version="1.0"?>
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel></channel>
</rss>

 

[tsuji]

Maybe a summary of what you have as source, what result you want to get, by what service/environment all they reside would help. Jumping between tutorial example and the sample data you really working on adds confusion on confusion.

 

[k5tm]

When last seen, your transform was matching <item>, not <ITEM>. These are not the same.

Tom Morrison

http://www.liant.com

 

[JayKappy]

Freakin unbelievable....darn syntax again....

hey tsuji you think you are confused....I do thank you for your thoughts...I am very new to this and trying my best...

k5tm once again thank you very much....that was it...darn syntax..

I look back on this now and it seems to simple....but I did learn a lot and I thank you all....

I am going to post my code and results below...

For those of you interested I am needign this format for Local.Live. I am grabbing data inhouse and mapping them usign Local.Live's mapping....


FROM ACCESS: I am running this programatically

XML OUTPUT:
Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="ITEM", _
DataTarget:="F:\TEMP\ExportsXML.xml", _
SchemaTarget:="F:\TEMP\ExportsXMLSchema.xml"

XSL TRANSFORMATION:
Application.TransformXML _
DataSource:="F:\TEMP\ExportsXML.xml", _
TransformSource:="F:\TEMP\TemplateNew_WORKING_Test.xsl", _
OutputTarget:="F:\TEMP\ExportsXML_Final.xml"

XML OUTPUT RESULTS:
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata" xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="ExportsXMLSchema.xml" generated="2007-03-27T08:24:45">
<ITEM>
<Title>01NE-049</Title>
<Description>COLLECT DATA</Description>
<Icon>ddd.jpg</Icon>
<geolat>45.124</geolat>
<geolong>-93.223</geolong>
</ITEM>
</dataroot>

XML AFTER TRANSFORMATION:
<?xml version="1.0"?>
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel>
<item>
<title>01NE-049</title>
<description>COLLECT DATA</description>
<icon>ddd.jpg</icon>
<geo:lat>45.124</geo:lat>
<geo:long>-93.223</geo:long>
</item>
</channel>
</rss>

 

[JayKappy]

OH I forgot....here is the XSL file that does the Transformation....


XSL FILE:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="

http://www.w3.org/1999/XSL/Transform"

xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<xslutput method="xml" version="1.0" indent="yes" encoding="utf-8"/>

<xsl:template match="dataroot">
<rss version="2.0" xmlns:geo="

http://www.w3.org/2003/01/geo/wgs84_pos#"

xmlns:mappoint="

http://virtualearth.msn.com/apis/annotate#">

<channel>
<xsl:apply-templates select="ITEM"/>
</channel>
</rss>
</xsl:template>

<xsl:template match="ITEM">
<item>
<title><xsl:value-of select="Title"/></title>
<description><xsl:value-of select="Description"/></description>
<icon><xsl:value-of select="Icon"/></icon>
<geo:lat><xsl:value-of select="geolat"/></geo:lat>
<geo:long><xsl:value-of select="geolong"/></geo:long>
</item>
</xsl:template>

</xsl:stylesheet>





Thanks again for all your help....especially k5tm

 

[JayKappy]

you also have to look at the XML output...based on the case of ITEM you might have to modify the above XSL file to replace the two occurances of

ITEM to item