OPENXML problem

Question

Post reply

OPENXML problem

solus

Hall of Fame

Points: 3495
More actions
September 10, 2010 at 2:19 am

#238047

I have a list id ID numbers contained in an xml document which i pass into a stored procedure.
I want to change the a column to a set value for ALL the id's contained in the xml list, but for some reason I cant get it to work. heres the sql in the stored procedure
EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml
BEGIN TRANSACTION
UPDATE Table1
SET StatusID = @UpdateStatus
FROM OPENXML(@idoc,'/Root/id',1)
WITH (id bigint) ox
WHERE Table1.ID = ox.id
EXEC sp_xml_removedocument @idoc
the sp compiles ok and is created, yet when I pass in the xml list, nothing is updated. Can anyone see what im doing wrong here ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

solus Hall of Fame Points: 3495 More actions · Answer 1

Ive taken another look at the sql and modified it slightly to this

EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml

BEGIN TRANSACTION

UPDATE Table1

SET StatusID = @UpdateStatus

WHERE Table1.ID IN

(SELECT ox.id FROM OPENXML(@idoc,'/Root/id')

WITH (id bigint) ox)

EXEC sp_xml_removedocument @idoc

COMMIT

but it still wont work, anyone have any idea why ?

solus Hall of Fame Points: 3495 More actions · Answer 2

Ive followed the suggestion from microsoft (http://support.microsoft.com/kb/315968/en-gb)

but it just will not change any data. Can anyone help ?

BEGIN TRY

EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml

BEGIN TRANSACTION

UPDATE AdapterTransaction

SET StatusID = @UpdateStatus

FROM OPENXML(@idoc,'/Root/id')

WITH (id bigint) xmlTable

WHERE AdapterTransaction.TransactionID = xmlTable.id

EXEC sp_xml_removedocument @idoc

COMMIT

END TRY

LutzM SSC Guru Points: 107049 More actions · Answer 3

please provide sample data so we have something to test against (xml string, table def of target data, some sample data and your expected output).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

solus Hall of Fame Points: 3495 More actions · Answer 4

Input XML

<Root>

</Root>

Input Status value = 1

the table has a primary key column 'TransactionID' and a statusID column of type integer

Im expecting all rows with a matching id in the xml to have the statusid column set to 1

solus Hall of Fame Points: 3495 More actions · Answer 5

finally managed to solve this, heres the answer

BEGIN TRY

EXEC sp_xml_preparedocument @idoc OUTPUT, @_xml

BEGIN TRANSACTION

UPDATE AdapterTransaction

SET StatusID = @UpdateStatus

FROM OPENXML(@idoc,'/Root/id')

WITH (id bigint '.') xmlTable

WHERE AdapterTransaction.TransactionID = xmlTable.id

EXEC sp_xml_removedocument @idoc

COMMIT

END TRY