Updating referential integrity and XML

[Greg Allen]

I am trying to use XML to update customer information in our SQL server
database. The
customer data has some referential integrity. A simple example would be two
tables,
employee and department -- each employee may belong to a department, where
emp.dep_id = dep.id (in other words, emp.dep_id is a foreign key to dep.id).

The problem is the customer (who is supplying the updates) has no idea about
the foreign
key. The XML they provide can (or will have to!) include the FK reference
(as I have
added this relationship to my .xsd. But the values in the incoming XML will
more than
likely *not* match the FK relationships in the database.

Somehow, I need to match up the incoming data and re-set the key/foreign key
fields before
updating the database. This also means if the department doesn't exist (by
name, of course),
it needs to be added first.

This seems like it might be a common problem. Any suggestions for a
solution? And
examples I could use for reference?

Thanks,

-- Greg Allen