How to insert child elements into mysql table from xml data?

Hello All,

Can you please answer my question.

Satish,

Good afternoon.  It would help to understand what other technologies are involved (Perl, PHP, Java, JavaScript, C#, VB, etc...).  What are you using to parse the XML to insert the data into your database.  What does your database schema look like?

I'm guessing you might be struggling with the XML traversal (how to access the question data). You didn't mention a specific language, but here's a sample using PHP's SimpleXML extension:

<?php
$xmlstr = '<Details>
    <Levels>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>0</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>1</MAT>
            <SDV>1</SDV>
            <Questions>
                <QuestionId>1186</QuestionId>
                <QuestionId>1187</QuestionId>
            </Questions>
        </Level>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>1</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>2</MAT>
            <SDV>2</SDV>
            <Questions>
                <QuestionId>1186</QuestionId>
                <QuestionId>1187</QuestionId>
            </Questions>
        </Level>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>2</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>1</MAT>
            <SDV>3</SDV>
            <Questions>
                <QuestionId>1209</QuestionId>
                <QuestionId>1210</QuestionId>
            </Questions>
        </Level>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>3</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>2</MAT>
            <SDV>1</SDV>
            <Questions>
                <QuestionId>1186</QuestionId>
                <QuestionId>1187</QuestionId>
                <QuestionId>1209</QuestionId>
                <QuestionId>1210</QuestionId>
            </Questions>
        </Level>
    </Levels>
</Details>';

// Parse the XML with SimpleXML
$xmlRoot = new SimpleXMLElement($xmlstr);

// Loop through each <Level>
$counter = 0;
foreach($xmlRoot->Levels->Level as $Level)
{
  $counter++;
  echo "Level #{$counter}:\n";
  echo "  SectionId = " . $Level->SectionId . "\n";
  echo "  WAC = " . $Level->WAC . "\n";
  echo "  Questions:\n";
  
  // Loop through Questions
  foreach($Level->Questions->QuestionId as $QuestionId)
  {
    echo "    QuestionId = " . $QuestionId . "\n";
  }
  
  echo "\n";
}

Select allOpen in new window

The resulting output from that script will look like this:

Level #1:
  SectionId = 681
  WAC = Test
  Questions:
    QuestionId = 1186
    QuestionId = 1187

Level #2:
  SectionId = 681
  WAC = Test
  Questions:
    QuestionId = 1186
    QuestionId = 1187

Level #3:
  SectionId = 681
  WAC = Test
  Questions:
    QuestionId = 1209
    QuestionId = 1210

Level #4:
  SectionId = 681
  WAC = Test
  Questions:
    QuestionId = 1186
    QuestionId = 1187
    QuestionId = 1209
    QuestionId = 1210

Select allOpen in new window

Hello @Jim Riddles,
I am using C# and MYSQL in C# everything is working fine. I have a problem with MYSQL Storedprocedure.
Can you please help with this.
set @xml ='<Details>
    <Levels>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>0</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>1</MAT>
            <SDV>1</SDV>
            <Questions>
                <QuestionId>1186</QuestionId>
                <QuestionId>1187</QuestionId>
            </Questions>
        </Level>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>1</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>2</MAT>
            <SDV>2</SDV>
            <Questions>
                <QuestionId>1186</QuestionId>
                <QuestionId>1187</QuestionId>
                        <QuestionId>1188</QuestionId>
                <QuestionId>1189</QuestionId>
            </Questions>
        </Level>
        <Level>
            <SectionId>681</SectionId>
            <RiskId>2</RiskId>
            <WAC>Test </WAC>
            <DOCReview>Test</DOCReview>
            <MAT>1</MAT>
            <SDV>3</SDV>
            <Questions>
                <QuestionId>1209</QuestionId>
                <QuestionId>1210</QuestionId>
                 <QuestionId>1199</QuestionId>
            </Questions>
        </Level>
      </Levels>
</Details>';


DECLARE tSectionId varchar(20),tWAC varchar(20),tDOCReview varchar(20),tRiskId varchar(20),tMATId varchar(20),tSDVId varchar(20),pUserId varchar(20),tInputLength int,tRiskScoreLength int;
DECLARE loopCnt int default 1,tQustCnt int default 0;
        
SET tInputLength=ExtractValue(@xml,'count(//Details//Level)');
       WHILE loopCnt <= tInputLength DO

                     SET tSectionId=ExtractValue(@xml,'//SectionId[1][$loopCnt]');
                     SET tWAC=ExtractValue(@xml,'//WAC[1][$loopCnt]');
                     SET tDOCReview=ExtractValue(@xml,'//DOCReview[1][$loopCnt]');
                     SET tRiskId= ExtractValue(@xml,'//RiskId[1][$loopCnt]');
                     SET tMATId= ExtractValue(@xml,'//MAT[1][$loopCnt]');
                     SET tSDVId= ExtractValue(@xml,'//SDV[1][$loopCnt]');
 
                     #inserting data into the first table
               insert into tbl_details(SectionId,WAC,DOCReview,RiskScoreId,MAT,SDV,UserId)
                   values(tSectionId,tWAC,tDOCReview,tRiskId,tMATId,tSDVId,pUserId);  # I am able to insert data into first table without fail.

               WHILE tQustCnt <= tRiskId DO
           SET tQuestionId=ExtractValue(@xml,'//Level[$loopCnt]//Questions//QuestionId[$tQustCnt]'); #  this is problematic area I need to separate questions based on the level.
               # Above xml data has 3 levels each level has different number of question now I have to separate questions based on level and insert into another table.
                set @tSDVDetailsId=101;
               #inserting data into second table
           insert into tbl_sdvquestions(QuestionId,SDVDetailsId)
               values(tQuestionId,@tSDVDetailsId);
               SET tQustCnt = tQustCnt + 1;
           
               END WHILE;


            SET loopCnt = loopCnt + 1;
      END WHILE;

Hello @gr8gonzo,

Thanks for the reply. Here the problem with MYSQL only,  in my procedure there are two insert statements with two diff. tables.
Below is the XML Schema my previous comment has XML data.

SET tInputLength=ExtractValue(@xml,'count(//Details//Level)');
       WHILE loopCnt <= tInputLength DO

                     SET tSectionId=ExtractValue(@xml,'//SectionId[1][$loopCnt]');
                     SET tWAC=ExtractValue(@xml,'//WAC[1][$loopCnt]');
                     SET tDOCReview=ExtractValue(@xml,'//DOCReview[1][$loopCnt]');
                     SET tRiskId= ExtractValue(@xml,'//RiskId[1][$loopCnt]');
                     SET tMATId= ExtractValue(@xml,'//MAT[1][$loopCnt]');
                     SET tSDVId= ExtractValue(@xml,'//SDV[1][$loopCnt]');
 
                     #inserting data into the first table
               insert into tbl_details(SectionId,WAC,DOCReview,RiskScoreId,MAT,SDV,UserId)
                   values(tSectionId,tWAC,tDOCReview,tRiskId,tMATId,tSDVId,pUserId);  # I am able to insert data into first table without fail.

               WHILE tQustCnt <= tRiskId DO
           SET tQuestionId=ExtractValue(@xml,'//Level[$loopCnt]//Questions//QuestionId[$tQustCnt]'); #  this is problematic area I need to separate questions based on the level.
               # Above XML data has 3 levels each level has a different number of the question now I have to separate questions based on level and insert into another table.
                set @tSDVDetailsId=101;
               #inserting data into second table
           insert into tbl_sdvquestions(QuestionId,SDVDetailsId)
               values(tQuestionId,@tSDVDetailsId);
               SET tQustCnt = tQustCnt + 1;
           
               END WHILE;


            SET loopCnt = loopCnt + 1;
      END WHILE;