Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

XML Output Templates

Status
Not open for further replies.
JayKappy

JayKappy

Technical User
Mar 23, 2007
29
US
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=" xmlns:mappoint="<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
 
Sort by date Sort by votes
Tutorial here.

Also, you may download a trial version of Stylus Studio or Altova XMLSpy. These are XML toolkits that include XSL creation/edit capability. (My preference is Stylus Studio. It also includes some tutorial material.)

Tom Morrison
 
Upvote 0 Downvote
I tried this example

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl=" match="/">
<html>
<body>
<h2>My CD Collection</h2>
<table border="1">
<tr bgcolor="#9acd32">
<th>Title</th>
<th>Artist</th>
</tr>
<tr>
<td><xsl:value-of select="catalog/cd/title"/></td>
<td><xsl:value-of select="catalog/cd/artist"/></td>
</tr>
</table>
</body>
</html>
</xsl:template></xsl:stylesheet>


I CAN CHANGE THE TITLE AND ARTIST TO FIELD VALUES IN THE XML FILE< BUT WHAT ABOUT THE REFERENCE ABOVE:
xsl:value-of select="catalog/cd/artist"/

IS THIS SUPPOSED TO BE A REFERENCE TO THE XML FILE?

SO THIS CREATES A TEMPLATE FILE THAT READ THE XML CORRECT?

THERE IS NO XML CREATED FROM THIS WRITE?

IF I CAN JSUT GET THIS XSL FILE TO READ THE XML I WOULD BE HAPPY....

I JUST DONT SEE HOW IT REFERENCES THE XML FILE...

THANK YOU FOR YOUR THOUGHTS
 
Upvote 0 Downvote

OK I think I got somthing working....

I am sure I will be asking a couple questions in a bit..

thanks for your help....



 
Upvote 0 Downvote
First of all, please stop typing in ALL CAPS!

Assuming you are using Windows here is a starting point for information about using MSXML from the command line.

Tom Morrison
 
Upvote 0 Downvote
Geese sorry....didnt think this would bother you all that much...

I am successfully using the Style Sheet to show the XML in the format I want

That is opening it in a WebPage

My issue is that I need to create an XML in that format. I need to export and create an XML...all the Style shee seems to be doing is organizing the XMl and displayign it a certain way....

How do I get it to create another XML file.

Thank you all again fro yoru help it is very appreciated

 
Upvote 0 Downvote
I am trying to understand exactly what you wish to do. My current guess is that you wish to have an XML document (file) saved by Access, and you wish that document to have a specific format which differs from the default Access layout. Is this correct?

My version of Access (2003) would have you add the PresentationTarget parameter, which specifies the XSL transformation to apply as the document is being saved.

So, how do you create one of these XSL stylesheets? I would again refer you to Stylus Studio, which has a graphical XSLT editor which is fairly easy to use. It is driven by having an exemplar input and output document, and you can use drag-and-drop to make connections from the input document to the output document.

Tom Morrison
 
Upvote 0 Downvote
1. I have an Access Database.
2. I have a table in that database that I want to export to XML
3. This XML has to be in a specific format. (exaclty like my first entry)

So there is a beginning part that is written once.
Then all the items (records in the Database)
Ended with </channel> and </rss>

Right now I can export by right clicking on the table in Access or by using some code. But the default format that gets exported out of Access does not work for me. So this requires me to cut and past the beginning part and then modify a couple other things....

I need this to be automated because I have a few tables and do this on a daily basis.

What I would love is to to be able to export out of Access right into an XML with the format like in my first entry above.

All of this from within Access. This way I can set it up to run every few hours...this way it will be pretty close to real time..

I want to stay away from third party software because that requires time and manually work....I am looking to automate this....

I do appreciate you help here...it is very appreciated...I hope that you can help me here....

THanks again


 
Upvote 0 Downvote

How do you add the Presentation Tag Parameter....that seems like it would do exaclty what I am trying to do...

How do I do that?

DO you have an example?

 
Upvote 0 Downvote

This gets me an XML file...but no formating...

DO you create an XLS file and then point to it somewhere in this code?

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


Thanks again for your help...
 
Upvote 0 Downvote
Here is what I find in the Access 2003 help file, for instructions to do this from the menus.

Search for the help topic titled Export Access data as XML.

Click on the 'Show All' icon to expand the entire help topic.

Search for the phrase 'Export Transforms'.

This is also described, with an example, on the MS site here.

Once you have the manual method working, then you can go to work on your VBA. That same MS site indicates that you need to use the TransformXML method on your object.

Tom Morrison
 
Upvote 0 Downvote
Man I am looking right att he example...

I exaclty mimiced the code example for the .xsl
changing the names of the table and fields.

I went into Access and right clicked ont eh table and clicked export and followed the instructions and I get an error...

ugggggggggggggggggggggggggggggggggg

========================================================
ERROR:

Error loading the XSL transofmration file. Make sure the file is properly formatted XSL file and is the correct fuile to use for the data supplied///

End tag 'xsl:template' does not match the start tag 'html'

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



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

XSL FILE:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl=" version="1.0">
<xsl:eek:utput method="html" version="4.0" indent="yes" />

<xsl:for-each select="dataroot">
<html>
<body>
<table>
<xsl:apply_templates select="item"/>
<table>
<body>
<html>
</xsl:template>

<xsl:template: match="item">
<tr>
<td><xsl:value-of select="Title"/></td>
<td><xsl:value-of select="Description"/></td>
</tr>
</xsl:template>

</xsl:stylesheet>







ANY THOUGHTS:......I am so close....yet so far away....
 
Upvote 0 Downvote
<xsl:for-each select="dataroot">
<html>
<body>
<table>
<xsl:apply_templates select="item"/>
</table>
</body>
</html>
</xsl:template>

Tom Morrison
 
Upvote 0 Downvote
I cant thank you enough for the help here...always seems like it ends up being a syntax issue, although that did not fully fix my problem....

I know get this error:
Specified transform failed to successfully transform your data
Keyword: xsl:apply_templates may not be used here.


Any thoughts to this....again I am using the example from this page:



item is the naem of my table
Title and Description are fields in that table



THIS IS THE CODE EXACTLY HOW I HAVE IT.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
xmlns:xsl="version="1.0">
<xsl:eek:utput method="html" version="4.0" indent="yes" />

<xsl:template match="dataroot">
<html>
<body>
<table>
<xsl:apply_templates
select="item"/>
</table>
</body>
</html>
</xsl:template>

<xsl:template match="item">
<tr>
<td><xsl:value-of select="Title"/></td>
<td><xsl:value-of select="Description"/></td>
</tr>
</xsl:template>

</xsl:stylesheet>
 
Upvote 0 Downvote
It seems the example I am trying to use is referencing HTML output not an XML output....I do not know why I am gettign an error on the apply_templates

This is very simplistic and if I can just get this to run I can modify it to include other fields....

Thanks again and I hope to hear from soemone soon....your help is most appreciated.
 
Upvote 0 Downvote
Damn Syntax again.....ONE last question....

Is there a way I can get this to appear at the begining of the Output XML....I just dont know how to tag it out so it appears exactly as I have shown below...

<?xml version="1.0" encoding="utf-8" ?>
<rss version="2.0" xmlns:geo="xmlns:mappoint="
I CANT THANK YOU ENOUGH....have to use caps....I appreciate your help here more than you know...very appreciative...

After I get this above to work my next step is to get this to run automatically...calling it from code...I can already call the output so I will just need to find out how to incorporate the reference to the XSL file....
This is the code I am usign to programmaically export the XML...If you can help with both questions that would be great...Man I owe you a cold beverage or two...


EXPORT CODE TO XML:

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


THANK YOU VERY MUCH
 
Upvote 0 Downvote
JayKappy said:
I CANT THANK YOU ENOUGH....have to use caps...
Click to expand...
No you don't have to use caps, there is another way. :)


Have you tried placing the rss element in your stylesheet?

You should be getting the <?xml...?> processing instruction already. We are happy that you find Tek-Tips useful. Please click on the "Process TGML" link below to learn how to insert code. It is time to show: a sample input document (perhaps trimmed down a bit), your XSL, and your output.

WRT automating, please see my previous comment about the TransformXML method.

As a courtesy to others, let's try to keep this all in this thread.

Tom Morrison
 
Upvote 0 Downvote
Boy I tell you...now I am starting to confuse myself...ok one question at a time....

Didnt see the link "Process TGML"

1. I changed the Method to XML and the Version to 1.0 and I am getting a good output...this seems to fix the issue on the other entry about everythign being on the same line.

2. The : thing I still dont understand..

3. I will work on the Automating issue later

4. It seems now all I have to figure out is how to get the begining of the XML output to look like the example at the end of this particular entry



XSL FILE:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="<xsl:eek:utput method="xml" version="1.0" indent="yes" />

<xsl:template match="dataroot">
<rss>
<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>



OUTOUT:
<?xml version="1.0" encoding="UTF-16"?>
<rss>
<channel>
<item>
<title>01NE-049</title>
<description>COLLECT DATA</description>
<icon>ddd.jpg</icon>
</item>
<item>
<title>01NW-005</title>
<description>Replace Support</description>
<icon>ddd.jpg</icon>
</item>

</channel>
</rss>


ALTOUGH:
I need the begining to look like this:
<?xml version="1.0" encoding="utf-8" ?>
<rss version="2.0" xmlns:geo=" xmlns:mappoint="
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

smak1
  • Locked
  • Question
add structure to XML
Replies
0
Views
672
smak1
smak1
Bijan Borazjani
  • Locked
  • Question
XML to PDF
Replies
0
Views
761
Bijan Borazjani
Bijan Borazjani
cdr_greg
  • Locked
  • Question
Display XML Line Breaks in Microsoft Word Content Controls
Replies
0
Views
982
cdr_greg
cdr_greg
198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
717
atlopes
atlopes
dawna3
  • Locked
  • Question
exporting blog posts to XML but how to read?!
Replies
0
Views
667
dawna3
dawna3

Part and Inventory Search

Sponsor

Back
Top