Exporting a table as xml - Access 2007

[Gesro]

I would like to export 2 access tables as xml, one table holds the header information and the other the details, both need to end up in one xml document, I also need the xsd schema exported, acces gives an example as below but the schema is just a copy of the xml with a different file extension. Any help much appreciated.

access example:

' Create the AdditionalData object.
Set objAD = Application.CreateAdditionalData

' Add the related tables to the object.
With objAD
.Add "Order Details"
objAD(Item:="Order Details").Add "Order Details Details"
.Add "Customers"
.Add "Shippers"
.Add "Employees"
.Add "Products"
objAD(Item:="Products").Add "Product Details"
objAD(Item:="Products")(Item:="Product Details").Add _
"Product Details Details"
.Add "Suppliers"
.Add "Categories"
End With

' Export the Orders table along with the addtional data.
Application.ExportXml acExportTable, "Orders", _
"C:\Orders.xml", "C:\OrdersSchema.xsd", _
"C:\OrdersStyle.xsl", AdditionalData:= objAD
End Sub

 

[henniec]

Good day,

I am no expert at all when it comes to xml. I went through a similar exercise during the year to create on xml file for several tables.

What I have done was to create a union query to combine all the tables as one, but I know that is not your problem as you have two tables. What I also did was to combine the the header file to the other tables. In other words the first few columns will contain the the header information and the rest the data. I am not sure if your header is constant or not. In other words your company detail. Populate all the rows with the same information for those fields.

I do notice you do have an xsl stylesheet and your stylesheet should take care of the header that it appears only once. Alternatively as I said if it is your company detail and will not change, add it to the xsl stylesheet and exclude it from the table.

I cant help you with the other problem.

Hope it helps.


Hennie

 

[Gesro]

Thanks for info Hennie,

I am not to sure either, but I think xml is probably want everyone wants nowadays . .

What I actually have is 2 tables, table one contains everything about a horse race i.e. country, track, total prize money how many runners etc etc, the 2nd table has the individual runners, which position they finished, what prize money, jockey trainer etc, their is a unique key to tie the 2 together, I have also a table as you suggested, repeated 'table one data' against each horse, will the xsl stylesheet differenciate between 2 tables or can you put something in the one table to do this, or even can I export a formatted report as xml somehow ?. I think the person using the xml data wants to use it as a snapshot on a web page, the xml file will be replaced on aregular basis.

regards
Gesro

 

[henniec]

Gesro,

I am afraid I do not know much more as xml does not make sense to me. I find it difficult to understand even after I read several articles about xml/xsl. I find is is not something you can learn like access and you can see a result.

I would suggest you go to one of the xml news groups and some one may even change you xsl file to display your result.

I needed to go through this route to report to some government agency.

Sorry I cannot be of much more help.

Hennie

 

[henniec]

Gesro,

I have been thinking what you need and it is what they refer to as grouping. My original suggestion of combining the two tables is correct. You need to change your xsl file to the format I am posting below. If you go through the style sheet you will see that date, cp_name ect are constants for several records, while the rest are the variables. indicated as

select="*[not(self::sample_date_time or self::cp_name or self::imis_cp_ext or self::dischg_sample_cd or self::sample_comment)]" />

Important is the seperation with the "|".
select="sample_date_time | cp_name | imis_cp_ext | dischg_sample_cd | sample_comment" />

This stylesheet should steer you in the right direction.

Something else, I think the Schema would be more applicable if you exported a form or a report.

Good luck.

Hennie

<xsl:stylesheet version="1.0" xmlns:xsl="

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

<xslutput
method="xml"
indent="yes"
encoding="UTF-8" />

<!-- the key determines (here) what to group by -->
<xsl:key
name="sample"
match="sample"
use="concat(sample_date_time, cp_name, imis_cp_ext, dischg_sample_cd, sample_comment)" />

<xsl:template
match="/">
<submission
imis_company_code="0001843309"
ws_name="LENNOX">
<xsl:apply-templates />
</submission>
</xsl:template>

<xsl:template
match="dataroot">
<!-- the actual 'group by' construct -->
<xsl:for-each
select="sample[generate-id() = generate-id(key('sample', concat(sample_date_time, cp_name, imis_cp_ext, dischg_sample_cd, sample_comment))[1])]">
<xsl:copy>
<!-- date-time must become an attribute -->
<xsl:apply-templates
select="sample_date_time | cp_name | imis_cp_ext | dischg_sample_cd | sample_comment" />

<xsl:apply-templates
select="key('sample', concat(sample_date_time, cp_name, imis_cp_ext, dischg_sample_cd, sample_comment))" />
</xsl:copy>
</xsl:for-each>
</xsl:template>

<!-- match the sample's that are grouped -->
<xsl:template
match="sample">
<result>
<xsl:apply-templates
select="*[not(self::sample_date_time or self::cp_name or self::imis_cp_ext or self::dischg_sample_cd or self::sample_comment)]" />
</result>
</xsl:template>

<!-- any direct child of 'sample' must be turned into an attribute -->
<xsl:template
match="sample/*">
<xsl:attribute
name="{name()}">
<xsl:value-of
select="." />
</xsl:attribute>
</xsl:template>
</xsl:stylesheet>

 

[Gesro]

Hennie,

Thank you again, I'll try this now and let you know the outcome . . it won't beat me . .

regards
Gesro