?
Solved

MySQL - search xml field for multiple occurrences of specific text

Posted on 2016-10-12
6
Medium Priority
?
74 Views
Last Modified: 2016-11-04
I have a database that contains an XML field that is stored as longtext. The field contains XML that I need to search for specific occurrences of words, for example:
Interaction>
I need to know if that text occurs multiple times in the field, and I ultimately need to use that fact as part of logic to determine fields to update.
For example, I need to make an update to every field, but only if the XML field contains one and only one instance of Interaction>
0
Comment
Question by:musickmann
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41841319
Can you provide an example of your data and the expected result?
0
 
LVL 2

Author Comment

by:musickmann
ID: 41842218
I don't have data that I can share easily here, the xml fields can be 100+ lines, but I'm looking to identify how main times this tag appears in the xml field:

<choiceInteraction class="align-type-none" id="I1" maxChoices="2" minChoices="1" orientation="vertical" responseIdentifier="response" shuffle="false">

I'm only interested in the beginning of the tag:
<choiceInteraction
As the additional tags can have a multitude of values.

What I need to do is search through a database of N number of records in which one field is XML, and in that one XML field, I need to get a count of how many times <choiceInteraction occurs, and return a result set of all records that have more than 1 occurrence of that.
Since the tag also has a closing piece, I could also look for multiple occurrences of </choiceInteraction>

Does that help? Since the code contains property secure testing data, I would need to clean a record first, but here is a basic template that I'd be looking at:

<?xml version="1.0" encoding="UTF-8"?><assessmentItem xmlns="http://www.imsglobal.org/xsd/imsqti_v2p1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" adaptive="false" identifier="" timeDependent="false" title="" xml:lang="en" xsi:schemaLocation="http://www.imsglobal.org/xsd/imsqti_v2p1 http://www.imsglobal.org/xsd/qti/qtiv2p1/imsqti_v2p1p1.xsd">
  <responseDeclaration baseType="identifier" cardinality="multiple" identifier="response">
    <correctResponse>
      <value>A</value>
      <value>D</value>
    </correctResponse>
  </responseDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="SCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="MAXSCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="SCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="SCORE">
    <defaultValue>
      <value>1</value>
    </defaultValue>
  </outcomeDeclaration>
  <stylesheet href="styles.css" type="text/css"/>
  <itemBody>
    <div class="default">
      <choiceInteraction class="align-type-none" id="I1" maxChoices="2" minChoices="1" orientation="vertical" responseIdentifier="response" shuffle="false">
        <prompt>
          <div>
            <p></p>
          </div>
        </prompt>
        <simpleChoice class="block_choice" identifier="A">
          <div class="item_options">
            <div class="para_left">328</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="B">
          <div class="item_options">
            <div class="para_left">239</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="C">
          <div class="item_options">
            <div class="para_left">351</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="D">
          <div class="item_options">
            <div class="para_left">264</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="E-899762">
          <div class="item_options">
            <div class="para_left">360</div>
          </div>
        </simpleChoice>
      </choiceInteraction>
     
            <choiceInteraction class="align-type-none" id="I1" maxChoices="2" minChoices="1" orientation="vertical" responseIdentifier="response" shuffle="false">
        <prompt>
          <div>
            <p></p>
          </div>
        </prompt>
        <simpleChoice class="block_choice" identifier="A">
          <div class="item_options">
            <div class="para_left">123</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="B">
          <div class="item_options">
            <div class="para_left">456</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="C">
          <div class="item_options">
            <div class="para_left">789</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="D">
          <div class="item_options">
            <div class="para_left">000</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="E-899762">
          <div class="item_options">
            <div class="para_left">999</div>
          </div>
        </simpleChoice>
      </choiceInteraction>
      <!--rubricBlock  moved to directly inside itemBody, the only valid location for rubricBlocks in assessmentItems in this APIP/QTI version.-->
    </div>
    <rubricBlock class="DistractorRationale" label="1" view="author">
      <div class="distractor_analysis">
        <table>
          <tbody>
            <tr>
              <td>A</td>
              <td>
                <div class="para_left">Key</div>
              </td>
            </tr>
            <tr>
              <td>B</td>
              <td>
                <div class="para_left">Student looks at 9 in ones place.</div>
              </td>
            </tr>
            <tr>
              <td>C</td>
              <td>
                <div class="para_left">Student looks at 1 in ones place.</div>
              </td>
            </tr>
            <tr>
              <td>D</td>
              <td>
                <div class="para_left">Key</div>
              </td>
            </tr>
            <tr>
              <td>E</td>
              <td>
                <div class="para_left">Student looks at 0 in ones place.</div>
              </td>
            </tr>
          </tbody>
        </table>
      </div>
    </rubricBlock>
  </itemBody>
  <responseProcessing>
    <responseCondition>
      <responseIf>
        <isNull>
          <variable identifier="response"/>
        </isNull>
        <setOutcomeValue identifier="SCORE">
          <baseValue baseType="float">0</baseValue>
        </setOutcomeValue>
      </responseIf>
      <responseElseIf>
        <match>
          <variable identifier="response"/>
          <correct identifier="response"/>
        </match>
        <setOutcomeValue identifier="SCORE">
          <variable identifier="SCORE"/>
        </setOutcomeValue>
      </responseElseIf>
      <responseElse>
        <setOutcomeValue identifier="SCORE">
          <baseValue baseType="float">0</baseValue>
        </setOutcomeValue>
      </responseElse>
    </responseCondition>
    <setOutcomeValue identifier="SCORE">
      <sum>
        <variable identifier="SCORE"/>
      </sum>
    </setOutcomeValue>
    <setOutcomeValue identifier="MAXSCORE">
      <sum>
        <variable identifier="SCORE"/>
      </sum>
    </setOutcomeValue>
  </responseProcessing>
  <apipAccessibility xmlns="http://www.imsglobal.org/xsd/apip/apipv1p0/imsapip_qtiv1p0"/>
</assessmentItem>
0
 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1000 total points
ID: 41846673
Hi!

You could use the XML Function ExtractValue() and UpdateXML to update the xml.

To count you could do something like this
select ExtractValue(xmlcolumn, 'count(/choiceInteraction)' ) from mytable;

Open in new window



Regards,
     Tomas Helgi
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:musickmann
ID: 41850911
That would be a good step in the right direction, getting a list of counts, then using the IDs returned in that list for next steps.

However, I'm not getting anything but 0 returned in that query. So, I think this is closer to what it needs to be:
ExtractValue(xml, 'count(/assessmentItem/itemBody/choiceInteraction)' )

but I'm still not getting the results expected, I'll get a bunch of 0, some 1. Need to do more testing with it I think.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 41861232
Try like this.

select extractvalue(@xml,'count(//choiceInteraction)');

SET @xml = '<?xml version="1.0" encoding="UTF-8"?><assessmentItem xmlns="http://www.imsglobal.org/xsd/imsqti_v2p1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" adaptive="false" identifier="" timeDependent="false" title="" xml:lang="en" xsi:schemaLocation="http://www.imsglobal.org/xsd/imsqti_v2p1 http://www.imsglobal.org/xsd/qti/qtiv2p1/imsqti_v2p1p1.xsd">
  <responseDeclaration baseType="identifier" cardinality="multiple" identifier="response">
    <correctResponse>
      <value>A</value>
      <value>D</value>
    </correctResponse>
  </responseDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="SCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="MAXSCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="SCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <outcomeDeclaration baseType="float" cardinality="multiple" identifier="SCORE">
    <defaultValue>
      <value>1</value>
    </defaultValue>
  </outcomeDeclaration>
  <stylesheet href="styles.css" type="text/css"/>
  <itemBody>
    <div class="default">
      <choiceInteraction class="align-type-none" id="I1" maxChoices="2" minChoices="1" orientation="vertical" responseIdentifier="response" shuffle="false">
        <prompt>
          <div>
            <p></p>
          </div>
        </prompt>
        <simpleChoice class="block_choice" identifier="A">
          <div class="item_options">
            <div class="para_left">328</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="B">
          <div class="item_options">
            <div class="para_left">239</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="C">
          <div class="item_options">
            <div class="para_left">351</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="D">
          <div class="item_options">
            <div class="para_left">264</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="E-899762">
          <div class="item_options">
            <div class="para_left">360</div>
          </div>
        </simpleChoice>
      </choiceInteraction>
      
            <choiceInteraction class="align-type-none" id="I1" maxChoices="2" minChoices="1" orientation="vertical" responseIdentifier="response" shuffle="false">
        <prompt>
          <div>
            <p></p>
          </div>
        </prompt>
        <simpleChoice class="block_choice" identifier="A">
          <div class="item_options">
            <div class="para_left">123</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="B">
          <div class="item_options">
            <div class="para_left">456</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="C">
          <div class="item_options">
            <div class="para_left">789</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="D">
          <div class="item_options">
            <div class="para_left">000</div>
          </div>
        </simpleChoice>
        <simpleChoice class="block_choice" identifier="E-899762">
          <div class="item_options">
            <div class="para_left">999</div>
          </div>
        </simpleChoice>
      </choiceInteraction>
      <!--rubricBlock  moved to directly inside itemBody, the only valid location for rubricBlocks in assessmentItems in this APIP/QTI version.-->
    </div>
    <rubricBlock class="DistractorRationale" label="1" view="author">
      <div class="distractor_analysis">
        <table>
          <tbody>
            <tr>
              <td>A</td>
              <td>
                <div class="para_left">Key</div>
              </td>
            </tr>
            <tr>
              <td>B</td>
              <td>
                <div class="para_left">Student looks at 9 in ones place.</div>
              </td>
            </tr>
            <tr>
              <td>C</td>
              <td>
                <div class="para_left">Student looks at 1 in ones place.</div>
              </td>
            </tr>
            <tr>
              <td>D</td>
              <td>
                <div class="para_left">Key</div>
              </td>
            </tr>
            <tr>
              <td>E</td>
              <td>
                <div class="para_left">Student looks at 0 in ones place.</div>
              </td>
            </tr>
          </tbody>
        </table>
      </div>
    </rubricBlock>
  </itemBody>
  <responseProcessing>
    <responseCondition>
      <responseIf>
        <isNull>
          <variable identifier="response"/>
        </isNull>
        <setOutcomeValue identifier="SCORE">
          <baseValue baseType="float">0</baseValue>
        </setOutcomeValue>
      </responseIf>
      <responseElseIf>
        <match>
          <variable identifier="response"/>
          <correct identifier="response"/>
        </match>
        <setOutcomeValue identifier="SCORE">
          <variable identifier="SCORE"/>
        </setOutcomeValue>
      </responseElseIf>
      <responseElse>
        <setOutcomeValue identifier="SCORE">
          <baseValue baseType="float">0</baseValue>
        </setOutcomeValue>
      </responseElse>
    </responseCondition>
    <setOutcomeValue identifier="SCORE">
      <sum>
        <variable identifier="SCORE"/>
      </sum>
    </setOutcomeValue>
    <setOutcomeValue identifier="MAXSCORE">
      <sum>
        <variable identifier="SCORE"/>
      </sum>
    </setOutcomeValue>
  </responseProcessing>
  <apipAccessibility xmlns="http://www.imsglobal.org/xsd/apip/apipv1p0/imsapip_qtiv1p0"/>
</assessmentItem>';

select extractvalue(@xml,'count(//choiceInteraction)');

Open in new window

0
 
LVL 2

Author Closing Comment

by:musickmann
ID: 41874902
ExtractValue(xml, 'count(//choiceInteraction)' ) AS InteractionCount

That is giving me what I want I think. The @ in front of xml returns null for that count, so that wasn't working.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question