update SQL Server columns using XML file -

update SQL Server columns using XML file -

i can correctly insert info tables using xml file, no want know if can update columns using xml file. have insert, works:

if @last_question <> @questiontext begin insert [tempdatabase].[dbo].testquestion (questiontype, question_text, questionaire_id, filter) values (@questiontype, @questiontext, @questionaire_id,@filter) select @newquestion_id = scope_identity() end insert [tempdatabase].[dbo].testpossible(q_id, expl, rev, p_a_t, qtype) values (@newquestion_id, @requiresexplanation, @reviewrequired, @value, @questiontype) set @last_question = @questiontext

but when seek , update, take lastly value in xml

update [tempdatabase].[dbo].testpossible set expl = @requiresexplanation, rev = @reviewrequired, p_a_t = @value, qtype = @questiontype q_id = @id

now have cursor defined , other statements create insert , update run there no need suggest add together those, didn't add together them save congestion on page. can show xml if need to. main thing have multiple @value , inserting first 1 required amount of times(two first question , 3 second) in update

does have suggestion accomplish this?

edit: added xml

<questions> <question> <id>2422</id> <questiontext>did update work correctly?</questiontext> <questiontype>1</questiontype> <questionaireid>2</questionaireid> <filter>31</filter> <possibleanswers> <possibleanswer> <value>yes did</value> <requiresexplanation>1</requiresexplanation> <reviewrequire>t</reviewrequire> </possibleanswer> <possibleanswer> <value>no did not</value> <requiresexplanation>1</requiresexplanation> <reviewrequire>t</reviewrequire> </possibleanswer> </possibleanswers> </question> <question> <id>2423</id> <questiontext>how today?</questiontext> <questiontype>1</questiontype> <questionaireid>2</questionaireid> <filter>127</filter> <possibleanswers> <possibleanswer> <value>great</value> <requiresexplanation></requiresexplanation> <reviewrequire></reviewrequire> </possibleanswer> <possibleanswer> <value>good</value> <requiresexplanation>1</requiresexplanation> <reviewrequire>t</reviewrequire> </possibleanswer> <possibleanswer> <value>bad</value> <requiresexplanation>1</requiresexplanation> <reviewrequire>t</reviewrequire> </possibleanswer> </possibleanswers> </question> </questions>

and here how i'm getting xml data

declare @xmlstring xml select @xmlstring = cast(x xml) openrowset(bulk 'c:\xml_id.xml',single_blob) t(x) --set cursor walk through uploaded xml table declare cur cursor local select question_id = xtbl .value('(../../id)[1]', 'bigint'), questiontext = xtbl.value('(../../questiontext)[1]', 'varchar(200)'), questiontype = xtbl.value('(../../questiontype)[1]', 'bigint'), questionaire_id = xtbl.value('(../../questionaireid)[1]', 'bigint'), filter = xtbl.value('(../../filter)[1]', 'bigint'), value = xtbl.value('(value)[1]', 'varchar(400)'), requiresexplanation = xtbl.value('(requiresexplanation)[1]', 'int'), reviewrequired = xtbl.value('(reviewrequire)[1]', 'char(1)') @xmlstring.nodes('/questions/question/possibleanswers/possibleanswer') xd(xtbl)

the update table testpossible done using question_id

if take sample input below, there 2 values same question id , 1 row i.e. lastly row matching , getting updated.

question_id value 2422 "yes did" 2422 "no did not"

one alternative remove rows questions , update new possible values or need have unique key identify individual possible reply value.

sql-server xml sql-update