Stored procedure to read xml file and Scope_identity error

Question

I am reading an xml file in a stored procedure to insert data into an existing table. I have read the xml in a small test to see if I can read it like I think should be able to and it does. I now want to create a stored procedure that inserts data from xml in a 'Question' table and also an 'Answer' table. The thing is I need the primary key from the question table that was just created to insert into the Answer table or else it causes a problem because of the FK.

I have this:

insert into 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
    from 
        (select 
            cast(x as XML)
         from openrowset(bulk 'C:\sqlXML.xml', single_blob) as T(x)
        ) as T(x)
    cross apply 
        x.nodes('data/New') as X(xmlData);

    select @newQuestionId = SCOPE_IDENTITY() <--I need this to know the current 
    questionID that relate to the following possible answers

    insert into 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
    from (
    select cast(x as XML)
    from openrowset(
    bulk 'C:\sqlXML.xml',
    single_blob) as T(x)
    )
    as T(x)
    cross apply x.nodes('data/New') as X(xmlData);

Now this could be all wrong because in my test I just read the data and didn't insert it into a table.

And this is my XML:

    <?xml version="1.0" encoding="UTF-8" ?>
    <data>
    <New>
            <QuestionaireID>2</QuestionaireID>
            <Type>1</Type>
            <Question>Does this 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 this 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 you can see there may be question with on 2 possible answers or 4 possible answers...

I get the errors:

An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I know where the errors are coming from but I was wondering if there was a way to do this?

Why are you trying to assign values to xmlPossibleAnswer variable? I don't even see that variable get declared? — Tab Alleman, Oct 30 '14 at 20:20
I saw that too but in a was another answer on here so I tried it... — Bnaffy, Oct 30 '14 at 20:21
Well the error messages are telling you it can't be done, and without a reason to do it, I'd take it out and do a straight INSERT. — Tab Alleman, Oct 30 '14 at 20:30
I took it out and now it says the select list contains more items then the insert list, but they both have 4?? — Bnaffy, Oct 30 '14 at 20:32
This is pretty much the same question that I answered a few hours ago for someone else: http://stackoverflow.com/questions/26642298/tsql-inserting-records-from-xml-string/26656432#26656432 . The only issue here is that you have a decision to make regarding multiple `AnswerChoice` elements, and that is: do you really want to repeat the `Explanation_Required` and `Review_Required` fields for each `AnswerChoice`? To model this properly, a third table is required to hold the `AnswerChoice` values. Also, use "@username" at the beginning of a comment to let that person know there has been a comment. — Solomon Rutzky, Oct 30 '14 at 20:42
Thanks for the tip, didn't know that about the username..I do want to use explanation and required_review for now...@srutzky I looked at the answer you provided in the other example..Is it pretty much the same if I'm reading from a file? — Bnaffy, Oct 30 '14 at 20:47
`sp_xml_preparedocument` doesn't accept a file, but you could probably `SELECT @XmlVariable = x FROM OPENROWSET(BULK, ..) T(x)` and then use the rest. And I am not saying to not use those two fields, I am saying that the model is wrong. Your second insert statement cannot grab all 4 possible answers as those are each a column and yet there is only a single column to dump into, hence needing to repeat `Explanation_Required` and `Review_Required` for each answer row. And why copy QuestionType into the Answers table? Also, the "@username" needs to be the first few characters of the comment ;-). — Solomon Rutzky, Oct 30 '14 at 20:54
Also, why do you need an IDENTITY field on the Question table? Isn't `QuestionaireID` essentially already that unique value? — Solomon Rutzky, Oct 30 '14 at 21:01
@srutzky First going with what you said I could probably use, I'll try it, second, the QuestionType is in there because there were some samples of a questionaire database so I just followed there lead, and the questionaireID is a not a unique value in the questions table becqause different question could belong to different questionaires — Bnaffy, Oct 30 '14 at 21:09
Still, repeating QuestionType seems odd and incorrect, but ok. So, most importantly here, is there any combination of values in the Question element that could be used to uniquely identify it? It is kinda sounding like "no", yet that is kinda required here ;-). What about the QuestionText itself? — Solomon Rutzky, Oct 30 '14 at 21:19
@srutzky In the Question table I have QuestionID with is a unique number for every question — Bnaffy, Oct 30 '14 at 21:27
Yes, but we need something unique in THIS data to allow us to map to that QuestionID. It could be that you need to use the combination of QuestionaireID, Type, Filter and possibly Question. Would the first 3 of those be unique in the data, and if not, then all 4? — Solomon Rutzky, Oct 30 '14 at 21:30
@srutzky OOHHH :) The question itself will be unique, no point in having the question repeated in the question table — Bnaffy, Oct 30 '14 at 21:35
Well, that doesn't make things any easier because you have QuestionaireID which makes the Question table (the one shown in the question) instances of the questions. are you now saying that there is another table that holds the unique questions? It seems like more design is needed on this before we begin attempting implementation. As I said earlier, the insert into the answers table is not going to work the way you have it structured at the moment (turning rows of possible answers into columns). — Solomon Rutzky, Oct 30 '14 at 21:49
@srutzky I'm getting confused so I'm going to go back and look at what I have and give a better example of what I'm trying to do, thanks for your help so far — Bnaffy, Oct 30 '14 at 21:52
Good idea. I think you should focus on 1) is your first table storing Questions or Questionnaires (which contain questions), and 2) how you want to handle multiple possible answers (as your current insert into `Possible_Answers` will never work as conceived). — Solomon Rutzky, Oct 30 '14 at 22:08

Stored procedure to read xml file and Scope_identity error

0 Answers0