SQL Server Performance

XML insert into MS SQL

Discussion in 'General Developer Questions' started by geasy, Jun 21, 2007.

  1. geasy New Member

    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
  2. ranjitjain New Member

  3. geasy New Member

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

    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).

Share This Page