insert/update based upon search result using xml data

• S-322532

  SSCommitted

  Points: 1613

  More actions

  November 23, 2009 at 7:11 pm

  #103857

  Gurus,

  I'm currently working on an application that gets the data from a web service as below

  <root>

  <Ojt cuCode="AUD" score="-0.01" Lastupdate="2009/11/20" gQw="X" />

  <Ojt cuCode="SWK" score="+0.99" Lastupdate="2009/11/20" gQw="C" />

  <Ojt cuCode="YEN" score="+20.10" Lastupdate="2009/11/20" gQw="L"/>

  <Ojt cuCode="GBP" score="+90.10" Lastupdate="2009/11/20" gQw="NA"/>

  <Ojt cuCode="EUR" score="+09.83" Lastupdate="2009/11/20" gQw="X"/>

  </root>

  The same needs to be pushed into a table and i'm achieving it using xml processing in my stored procedure. The following things needs to be taken care

  1) if the cuCode is new it should be inserted(no duplicates are allowed in the column) and if exists the score field should be updated.

  2) based upon the gQw the row should be marked as either active or inactive.

  currently i'm opening a connection to the database for each record and pushing the same using sp_xml_preparedocument and other xml processing stuff.

  Is it possible to pass the entire data to the procedure and carry out the above 2 steps by connecting only once to the database( at a single shot) using xml. I dont prefer to do the same by using cursor or iteration(for the fear i may loose the advantages of xml processing) :unsure: :sick:

  Can anyone suggest/guide me on the pointers.

  thanks

  Sree 🙂

• dmoldovan

  SSCertifiable

  Points: 6334

  More actions

  November 25, 2009 at 12:27 am

  #1083834

  Use the nodes() method of the XML data type. BOL ---> nodes() Method (xml Data Type) is a good starting point.

  You can also consider to use an XML schema collection for your data.