XML insert into MS SQL

I know there are different ways to import an XML file into a MS SQL database, but i haven't found THE WAY. Do you have some good (or bad) expirience with this?

special requirments:
- XML files are not always flat files, we have also complicated/structured files
- If a record exists (identified by PK) the record should be updated, if not the record should be inserted
- XML item can effect more than one table/object in the data
- Insert should start the triggers

sometimes we need LOOKUPs, i.e. we get a NAME and a GROUPNAME:
1. check if GROUPNAME exists in table GROUPS then ok, else insert GROUPNAME with new GROUPID
2. the NAME should be inserted/updated in another table, together with the GROUPID


thanks a lot for your help

Hi,
You can create a xsd file which is xml schema definition which you can validate against the
xml file and based on it you can define relationships and export data into sql.

Also check this article which discusses about SQL XMLbulk load.
http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=13&rl=1

thanks, but i can see a solution with this for the LOOKUP or Update/insert problem.

If you're still in SQL 7.0 or 2000, then I would suggest that you use a client application for transforming XML into table data. There is very little XML-oriented syntax in SQL 2000, and it is a nightmare to get it to work.

Let the client app upload the data into a staging table in SQL Server.

Then SQL Server can first do an update on matching rows, and second do an insert for new rows.

With more complex data (foreign keys) you have to be careful about the order in which you transfer from the staging table(s) to the production table(s).