You can turn the results of OPENXML into a derived table and then use the UPDATE-FROM command to create a join and copy the values from one table to another, something like this:
PRE
UPDATE MyTable1
SET ContactName = MyTable2.ContactName
FROM MyTable1,
(SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))) AS MyTable2
WHERE MyTable1.CustomerID = MyTable2.CustomerID
/PRE
Roman
>Hello All,
>I am currently deleting rows to insert rows into a table using an XML file as the source, my question is, is there an easy way to perform a TABLE UPDATE using the XML file as the source? Deleting rows and inserting new ones, IMO, is not the best solution, however, it solved the immediate problem. But, now I want to find the best solution. TIA
>
>Current Code:
>
>DELETE FROM dbo.myTable WHERE EvaluationID = @EvaluationID
>EXEC sp_xml_preparedocument @xmlDocument OUTPUT, @commitXml
>INSERT INTO dbo.myTable
> (EvaluationID, QuestionID, QuestionOrder)
> SELECT @EvaluationID, *
> FROM OPENXML( @xmlDocument, '/e/prop/p')
> WITH (
> QuestionID int './@qId',
> QuestionOrder int './@qOrder' )
>EXEC sp_xml_removedocument @xmlDocument
>
>
>Thank you,
>Rhonda
Roman Rehak, MCSD, MCDBA, MCSA
Competitive Computing
354 Mountain View Drive
Colchester, VT 05446-5824
802-764-1729