How to import XML data into SQL SERVER with batch?

• glatz

  SSC Journeyman

  Points: 77

  More actions

  May 20, 2008 at 5:07 pm

  #187983

  Hi,

  I am a dummy in programming, but I would need help in inserting multiple datasets into SQL Server.

  The problem is that some XML files are put on the server and can after succesfull storage start a batch. With this batch I would need to insert/update multiple table contents (with a stored procedure?) into SQL Server 2000. I would need to give the name of the XML file to the stored procedure and update (if ID exists) or insert the values to some tables. On the server should no additional software be installed.

  Is there some help or advise for me?

  Thanks a lot,

  Arno

• RynoB

  SSC Eights!

  Points: 802

  More actions

  May 21, 2008 at 1:56 am

  #818390

  Hi,

  You could look into OPENXML which takes a xml document as parameter

  See below for an example to get you started.

  DECLARE @idoc int

  EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

  INSERT INTO uClient

  FROM OPENXML (@idoc, '/r/a',2)

  WITH

  (

  id INT

  ,name VARCHAR(128)

  ,surname VARCHAR(128)

  ,email VARCHAR(128)

  ) alloc

  Hope this helps.

  R