asked on

Updating an Access database using an XML document ...

Hi

I am relatively new to XML so the question should be easy enough.

Given an XML document that has all the tags defined for a corresponding table in Access, how do you UPDATE the database with the data contained in the XML document.

I have been able to ADD a row (for when the primary key data doesn't exist in the database), but how do you UPDATE????

Cheers

sr

Paul Maker

you would need to have a specific column that was the ID of the column to update, for example

<items>
<item>
<name>new name</name>
<id>123</id>
</items>
<item>
<name>new name again</name>
<id>321</id>
</items>
</items>

you would then need to use XPath to select your nodes and exeute SQL based on the value, for example

sql_string = "UPDATE table SET name = '" + name + "' WHERE id = " + id

you could get cleaver and make this generic, for example

<table>my_table_name</table>
<items>
<item>
<name>new name</name>
<id id='yes'>123</id>
</items>
<item>
<name>new name again</name>
<id id='yes'>321</id>
</items>
</items>

now you could build the query dynamically and use the fact that the ID entry is marked as id to know this is the where clause. the node names would be the same as the column names in the database

-P

ASKER CERTIFIED SOLUTION

Paul Maker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial