MS Access XML Export Query Setup Multiple Tag Values

You ImageURLReplace values need to be part of a Many-to-One relationship with your product item

http://en.tekstenuitleg.net/articles/software/create-a-one-to-many-relationship-in-access

Thanks Shaun. These table relationships are set up like that. SKUs Table SkuID PK to ProductImages Table SkuID FK.

Are you willing to give VBA a go?

http://stackoverflow.com/questions/10486948/exporting-multiple-access-tables-to-single-xml

Dim objOtherTbls As AdditionalData


Set objOtherTbls = Application.CreateAdditionalData

'Identify the tables or querys to export
objOtherTbls.Add "internet"
objOtherTbls.Add "mokaleme"

'Here is where the export takes place
Application.ExportXML ObjectType:=acExportTable, _
DataSource:="internet", _
DataTarget:="C:\myxml.xml", _
AdditionalData:=objOtherTbls

MsgBox "Export operation completed successfully."

Select allOpen in new window

you have to update these values:

   objOtherTbls.Add "internet"
    objOtherTbls.Add "mokaleme"

    DataSource:="internet"

Select allOpen in new window

I really appreciate the help.  I am familiar with VBA and how to use it. I am not very good coming up with it my self quickly. But let me try to clear the air a little.

If I export my query this is what I get when I have multiple images for a single product:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Query1.xsd" generated="2016-12-26T13:58:33">
<Query1>
<SKU>15QWNWD8B4U</SKU>
<SkuNm>YELLOW SCHOOL BUS TOY TEST ITEM</SkuNm>
<SkuMPN>YELBUS32</SkuMPN>
<ProductImageFileNm>3253545436.jpg</ProductImageFileNm>
<dtmAdd>2016-12-26T13:52:54</dtmAdd>
</Query1>
<Query1>
<SKU>15QWNWD8B4U</SKU>
<SkuNm>YELLOW SCHOOL BUS TOY TEST ITEM</SkuNm>
<SkuMPN>YELBUS32</SkuMPN>
<ProductImageFileNm>3253545436(2).jpg</ProductImageFileNm>
<dtmAdd>2016-12-26T13:52:54</dtmAdd>
</Query1>
</dataroot>

Select allOpen in new window

How can i get it to export like this:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Query1.xsd" generated="2016-12-26T13:58:33">
<Query1>
<SKU>15QWNWD8B4U</SKU>
<SkuNm>YELLOW SCHOOL BUS TOY TEST ITEM</SkuNm>
<SkuMPN>YELBUS32</SkuMPN>
<ProductImageFileNm>3253545436.jpg</ProductImageFileNm>
<ProductImageFileNm>3253545436(2).jpg</ProductImageFileNm>
<dtmAdd>2016-12-26T13:52:54</dtmAdd>
</Query1>
</dataroot>

Select allOpen in new window

It seems the only way is to have the second value in the field ProductImageFileNm is to be on a seperate row. But in order to do that I would have to do a union query in which it shows all the other fields also in the XML export but no value.

Say if I was to make a second query and put SkuMPN:"" it would show <SkuMPN></SkuMPN>

But I only want it to show the ones with a value. Sorry if this is confusing as it is for me. I know what I need I just don't know how to explain it.

Think you need to apply an XLST to transform XML into that

I was just looking over some of that. i have never used it before. I am not quite sure of the terminology with XML but I believe I am looking for a nested Node or something like that. I need to maybe flatten the file. If that makes sense.

target schema (you also have this, you created it manually)

What do you mean?

This is your target schema

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Query1.xsd" generated="2016-12-26T13:58:33">
<Query1>
<SKU>15QWNWD8B4U</SKU>
<SkuNm>YELLOW SCHOOL BUS TOY TEST ITEM</SkuNm>
<SkuMPN>YELBUS32</SkuMPN>
<ProductImageFileNm>3253545436.jpg</ProductImageFileNm>
<ProductImageFileNm>3253545436(2).jpg</ProductImageFileNm>
<dtmAdd>2016-12-26T13:52:54</dtmAdd>
</Query1>
</dataroot>

Select allOpen in new window

ok thanks I just wanted to make sure. I just downloaded the free trial from altova to see if this is what I need. Thanks.

Ok I don't know if I am doing something wrong or what! This tool seems to be excellent as in a mapper.  BUT in my schema I have multiple lines with the same tag line name. They have to have the same tag name. But no matter what I do it only reconizes them all as a single tag line. It doesn't show me multiple lines.

Multiple lines with the same tag name is where my major problem is.

Yeah the Mapper isn't what I need. It maps great but what I need to know is how to nest many values to one value when exporting to XML.

Thanks

You should be able to map repeating elements to a record element that contains these repeating elements

Thanks. I will try and work on this this weekend and see what I can come up with. Thanks for the help!