sql server - Stored procedure to read xml file and Scope_identity error -

sql server - Stored procedure to read xml file and Scope_identity error -

i reading xml file in stored procedure insert info existing table. have read xml in little test see if can read think should able , does. want create stored procedure inserts info xml in 'question' table , 'answer' table. thing need primary key question table created insert reply table or else causes problem because of fk.

i have this:

insert questions(question_text, questionaire_id, questiontype, filter) select x.xmldata.query('question').value('.', 'varchar(100)') question_text,--question text x.xmldata.query('questionaireid').value('.', 'varchar(100)') questionaire_id,-- questionaire id x.xmldata.query('type').value('.', 'varchar(100)') wuestionetype,--question type x.xmldata.query('filter').value('.', 'varchar(100)') filter--filter (select cast(x xml) openrowset(bulk 'c:\sqlxml.xml', single_blob) t(x) ) t(x) cross apply x.nodes('data/new') x(xmldata); select @newquestionid = scope_identity() <--i need know current questionid relate next possible answers insert possible_answers(question_id, possible_answer_text, explanation_required, review_required, question_type) select @newquestionid , x.xmldata.query('answerchoice[1]').value('.', 'varchar(100)') possible_answer_text, x.xmldata.query('answerchoice[2]').value('.', 'varchar(100)') possible_answer_text, x.xmldata.query('answerchoice[3]').value('.', 'varchar(100)') possible_answer_text, x.xmldata.query('answerchoice[4]').value('.', 'varchar(100)') possible_answer_text, x.xmldata.query('explanationrequired').value('.', 'varchar(100)') explanation_required, x.xmldata.query('reviewrequired').value('.', 'varchar(100)') review_rewuired ( select cast(x xml) openrowset( mass 'c:\sqlxml.xml', single_blob) t(x) ) t(x) cross apply x.nodes('data/new') x(xmldata);

now wrong because in test read info , didn't insert table.

and xml:

<?xml version="1.0" encoding="utf-8" ?> <data> <new> <questionaireid>2</questionaireid> <type>1</type> <question>does test work?</question> <filter>31</filter> <answerchoice>true</answerchoice> <answerchoice>false</answerchoice> <explanationrequired></explanationrequired> <reviewrequired></reviewrequired> </new> <new> <questionaireid>3</questionaireid> <type>2</type> <question>does test work really?</question> <filter>127</filter> <answerchoice>answer a</answerchoice> <answerchoice>answer b</answerchoice> <answerchoice>answer c</answerchoice> <answerchoice>answer d</answerchoice> <explanationrequired></explanationrequired> <reviewrequired></reviewrequired> </new> </data>

as can see there may question on 2 possible answers or 4 possible answers...

i errors:

an insert statement cannot contain select statement assigns values variable.

a select statement assigns value variable must not combined data-retrieval operations.

i know errors coming wondering if there way this?

sql-server xml stored-procedures