Creating Image Reference upon XML Export in MS Access

Question

I'm automating catalog creation with a MS Access database and Adobe InDesign. I would like to save the file paths to images in the database, and have them become meaningful tags in XML upon export from MS Access.

As of now, Access just creates an XML tag for each column, giving something like this:

<IMG>href="file:///folder/image.jpg</IMG>

However, I need the following in order to insert these images in my final document:

<IMG href="file:///folder/image.jpg">

As of now, I am manually doing a Find+Replace on the generated XML file to fix these tags. However, I would like to be able to automate this workflow somehow. Is there an appropriate way to achieve this result from within MS Access, or must it be taken care of in the XML file itself?

Answer 1

Well, I received my "Tumbleweed" badge so I suppose I can post my workaround for the benefit of any others who may find themselves trying to solve this issue.

I was unable to achieve the desired result from within MS Access, but instead automated the find-remove process somewhat with the help of Sublime Text 2 (although Sublime Text 3 should work just as well).

After installing Sublime Text 2, I added the RegReplace plugin in order to create a custom command for finding and replacing text in the XML file that MS Access exports. For additional information on installing RegReplace, you can consult this blog post.

Foremost, we must delete the href= from the IMG column in the MS Access database, as the RegReplace command will be adding them automatically. Within a row in Access, image paths should appear as follows:

"file:///folder/image.jpg"

In order to then go from

<IMG>"file:///folder/image.jpg"</IMG>

in the exported XML from Access to

<IMG href="file:///folder/image.jpg">

We will need to write a find-replace command that finds the characters <IMG> and </IMG>, replacing them with <IMG href= and /> respectively.

To do so, the following must be entered and saved under Preferences > Package Settings > Reg Replace > Settings - Default

//Reformat IMG tags
    "IMG_reg_replace": {
        "find": "<IMG>",
        "replace": "<IMG href=",
        "find": "</IMG>",
        "replace": "/>"
    }

Then, under Preferences > Package Settings > Reg Replace > Commands - Default enter the following and then save:

//Reformat IMG Tags
    {
        "caption": "Reg Replace: Reformat IMG Tags",
        "command": "reg_replace",
        "args": {"replacements": ["IMG_reg_replace"]}
    },

Now that the command is defined and referenced in the Defaults for the RegEdit plugin, we can use it on the XML file exported from MS Access. Open the XML file in Sublime Text and press Ctrl+Shift+P to open the Command Palette and type in IMG. The command will appear, and upon being clicked the XML file will have all instances of the <IMG> replaced with a meaningful file path reference that can be used to load images as anchored objects in Adobe InDesign.