10 Replies Latest reply: Jun 17, 2012 2:28 PM by odie_63 RSS

    assign value while parsing XML data into oracle table

    user12236189
      Hello,

      Database Version: Oracle 1g R2

      I have been working to extract with this following data using XMLTABLE, putting multiple XMLTABLE to get each block, but I could not make any order out of it.
      Could you please check this out?
      <?xml version="1.0" encoding="WINDOWS-1252"?>
      <item title="0009789" maxattempts="0">
        <itemmetadata>
          <object_id>_140945_1</object_id>
          <type>Item</type>
          <assessmenttype>ASDF</assessmenttype>
          <sectiontype>Subsection</sectiontype>
          <questiontype>Ordering</questiontype>
          <is_from_cartridge>false</is_from_cartridge>
          <is_disabled>false</is_disabled>
          <numbertype>arabic</numbertype>
          <partialcredit>true</partialcredit>
          <orientationtype>vertical</orientationtype>
          <is_extracredit>false</is_extracredit>
          <absolutescore_max>10.0</absolutescore_max>
          <weighting>0.0</weighting>
          <instructornotes/>
        </itemmetadata>
        <presentation>
          <flow class="Block">
            <flow class="QUESTION_BLOCK">
              <flow class="FORMATTED_TEXT_BLOCK">
                <material>
                  <mat_extension>
                    <mat_formattedtext type="HTML">XYZ’s normal range is not known.&lt;br /&gt;          </mat_formattedtext>
                  </mat_extension>
                </material>
              </flow>
            </flow>
            <flow class="RESPONSE_BLOCK">
              <response_lid ident="response" rcardinality="Ordered" rtiming="No">
                <render_choice shuffle="No" minnumber="0" maxnumber="0">
                  <flow_label class="Block">
                    <response_label ident="a73c9ca485ee483b83d9f6d279a3c8c0" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Inflate the column while.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
                  </flow_label>
                  <flow_label class="Block">
                    <response_label ident="5e35eed9bb1c4d80b62127d84e5b9ed1" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Slowly the.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
                  </flow_label>
                  <flow_label class="Block">
                    <response_label ident="5ad7fc4289b74c7894ef029451824e11" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Deflate the.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
                  </flow_label>
                  <flow_label class="Block">
                    <response_label ident="093d2c040e05472285a0ffb4f1f9d033" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Inflate the.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
                  </flow_label>
                </render_choice>
              </response_lid>
            </flow>
          </flow>
        </presentation>
        <resprocessing scoremodel="SumOfScores">
          <outcomes>
            <decvar varname="SCORE" vartype="Decimal" defaultval="0.0" minvalue="0.0" maxvalue="10.0"/>
          </outcomes>
          <respcondition title="correct">
            <conditionvar>
              <and>
                <varequal respident="response" case="No">a73c9ca485ee483b83d9f6d279a3c8c0</varequal>
                <varequal respident="response" case="No">5ad7fc4289b74c7894ef029451824e11</varequal>
                <varequal respident="response" case="No">093d2c040e05472285a0ffb4f1f9d033</varequal>
                <varequal respident="response" case="No">5e35eed9bb1c4d80b62127d84e5b9ed1</varequal>
              </and>
            </conditionvar>
            <setvar variablename="SCORE" action="Set">SCORE.max</setvar>
            <displayfeedback linkrefid="correct" feedbacktype="Response"/>
          </respcondition>
          <respcondition title="incorrect">
            <conditionvar>
              <other/>
            </conditionvar>
            <setvar variablename="SCORE" action="Set">0.0</setvar>
            <displayfeedback linkrefid="incorrect" feedbacktype="Response"/>
          </respcondition>
        </resprocessing>
      </item>
      That means

      when I extract value on each of these four blocks
      <response_label ident="a73c9ca485ee483b83d9f6d279a3c8c0" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Inflate the column while.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
      
      <response_label ident="5e35eed9bb1c4d80b62127d84e5b9ed1" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Slowly the.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
      
      <response_label ident="5ad7fc4289b74c7894ef029451824e11" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Deflate the.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
      
      <response_label ident="093d2c040e05472285a0ffb4f1f9d033" shuffle="Yes" rarea="Ellipse" rrange="Exact">
                      <flow_mat class="FORMATTED_TEXT_BLOCK">
                        <material>
                          <mat_extension>
                            <mat_formattedtext type="HTML">Inflate the.&lt;br /&gt;          </mat_formattedtext>
                          </mat_extension>
                        </material>
                      </flow_mat>
                    </response_label>
      I need to assign a value as a,b,c,d( OR 0,1,2,3), when I extract value on these */response_label/@ident* from above blocks


      Then assign these values into the following block
          <respcondition title="correct">
            <conditionvar>
              <and>
                <varequal respident="response" case="No">a73c9ca485ee483b83d9f6d279a3c8c0</varequal>
                <varequal respident="response" case="No">5ad7fc4289b74c7894ef029451824e11</varequal>
                <varequal respident="response" case="No">093d2c040e05472285a0ffb4f1f9d033</varequal>
                <varequal respident="response" case="No">5e35eed9bb1c4d80b62127d84e5b9ed1</varequal>
              </and>
            </conditionvar>
            <setvar variablename="SCORE" action="Set">SCORE.max</setvar>
            <displayfeedback linkrefid="correct" feedbacktype="Response"/>
          </respcondition>
      The final value extract value will be like this a,c,d,b (OR 0,2,3,1) on the column varequal

      Any help would be greatly appreciated.

      Thanks,
        • 1. Re: assign value while parsing XML data into oracle table
          odie_63
          Hi,

          First step could be done like this :
          SQL> select x.*
            2  from tmp_xml t
            3     , xmltable(
            4       '/item/presentation/flow/flow[@class="RESPONSE_BLOCK"]/response_lid/render_choice/flow_label/response_label'
            5       passing t.object_value
            6       columns rn    for ordinality
            7             , ident varchar2(80) path '@ident'
            8       ) x
            9  ;
           
                  RN IDENT
          ---------- --------------------------------------------------------------------------------
                   1 a73c9ca485ee483b83d9f6d279a3c8c0
                   2 5e35eed9bb1c4d80b62127d84e5b9ed1
                   3 5ad7fc4289b74c7894ef029451824e11
                   4 093d2c040e05472285a0ffb4f1f9d033
           
          I'm not sure what you want after :
          Then assign these values into the following block
          Do you want to get the whole <respcondition> fragment with varequal values updated in the new order, or output varequal in separate rows in the new order?
          • 2. Re: assign value while parsing XML data into oracle table
            user12236189
            Thank you for your time. Yes, I need to pull all data along with the matching this key value. by all means actual data in all columns with the whole <respcondition> fragment with varequal values updated in the new order.

            Thanks,
            • 3. Re: assign value while parsing XML data into oracle table
              odie_63
              OK, if I correctly understand what you're saying (to be honest that's not easy), you basically want to update the XML document and return the new version?

              Please give your database version, all digits from : SELECT * FROM v$version.
              Starting with 11.2.0.3, XQuery Update Facility can do the job nicely. On previous versions, you'll need updateXML() function.
              • 4. Re: assign value while parsing XML data into oracle table
                odie_63
                Some examples.

                1) updateXML() - it's an SQL function but this case requires a PL/SQL-loop processing
                DECLARE
                
                  doc xmltype;
                  
                BEGIN
                  
                  select object_value into doc from tmp_xml;
                  
                  for r in (
                    select x.rn, x.ident
                    from tmp_xml t
                       , xmltable(
                         '/item/presentation/flow/flow[@class="RESPONSE_BLOCK"]/response_lid/render_choice/flow_label/response_label'
                         passing t.object_value
                         columns rn    for ordinality
                               , ident varchar2(80) path '@ident'
                         ) x
                   )
                   loop
                     select updateXML(
                              doc
                            , '/item/resprocessing/respcondition[@title="correct"]/conditionvar/and/varequal[' || to_char(r.rn) || ']/text()'
                            , r.ident
                            )
                     into doc
                     from dual;
                   end loop;
                   
                   dbms_output.put_line(doc.getclobval());
                
                END;
                /
                2) XQuery Update - 11.2.0.3 only
                select xmlquery(
                       'copy $d := .
                        modify (
                         let $ident := $d/item/presentation/flow/flow[@class="RESPONSE_BLOCK"]/response_lid/render_choice/flow_label/response_label/@ident
                         for $i at $p in $d/item/resprocessing/respcondition[@title="correct"]/conditionvar/and/varequal
                         return replace value of node $i with $ident[$p]
                        )
                        return $d'
                       passing object_value
                       returning content
                       )
                from tmp_xml
                ;
                3) XDiff and XMLPatch SQL interface
                select xmlpatch(
                         object_value
                       , xmlquery(
                         'declare namespace xd = "http://xmlns.oracle.com/xdb/xdiff.xsd"; (::)
                          element xd:xdiff 
                          {
                            attribute              xsi:schemaLocation { "http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" }
                          , processing-instruction oracle-xmldiff     { ''operations-in-docorder="true" output-model="current"'' }
                          , for $i at $p in /item/presentation/flow/flow[@class="RESPONSE_BLOCK"]/response_lid/render_choice/flow_label/response_label
                            return element xd:update-node 
                            {
                              attribute xd:node-type { "text" }
                            , attribute xd:xpath     { concat( "/item/resprocessing/respcondition[@title=''correct'']/conditionvar/and/varequal[", $p, "]/text()" ) }
                            , element   xd:content   { data($i/@ident) }
                            }
                          }'
                         passing object_value
                         returning content
                         )
                       )
                from tmp_xml
                ;
                This method generates an XDiff instance document containing the necessary update-node directives :
                <xd:xdiff xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" 
                          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                          xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd">
                  <?oracle-xmldiff operations-in-docorder="true" output-model="current"?>
                  <xd:update-node xd:node-type="text" xd:xpath="/item/resprocessing/respcondition[@title=&apos;correct&apos;]/conditionvar/and/varequal[1]/text()"><xd:content>a73c9ca485ee483b83d9f6d279a3c8c0</xd:content></xd:update-node>
                  <xd:update-node xd:node-type="text" xd:xpath="/item/resprocessing/respcondition[@title=&apos;correct&apos;]/conditionvar/and/varequal[2]/text()"><xd:content>5e35eed9bb1c4d80b62127d84e5b9ed1</xd:content></xd:update-node>
                  <xd:update-node xd:node-type="text" xd:xpath="/item/resprocessing/respcondition[@title=&apos;correct&apos;]/conditionvar/and/varequal[3]/text()"><xd:content>5ad7fc4289b74c7894ef029451824e11</xd:content></xd:update-node>
                  <xd:update-node xd:node-type="text" xd:xpath="/item/resprocessing/respcondition[@title=&apos;correct&apos;]/conditionvar/and/varequal[4]/text()"><xd:content>093d2c040e05472285a0ffb4f1f9d033</xd:content></xd:update-node>
                </xd:xdiff>
                which is then applied to the original document.


                All three methods return the original XML with varequal elements in the new order.
                Is that what you want or do you require something different?
                • 5. Re: assign value while parsing XML data into oracle table
                  user12236189
                  Sorry got late to give this to you...please see the following
                  SQL> select * from v$version;
                  
                  BANNER
                  -------------------------------------------------------------------------------
                  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                  PL/SQL Release 11.2.0.2.0 - Production
                  CORE    11.2.0.2.0      Production
                  TNS for Linux: Version 11.2.0.2.0 - Production
                  NLSRTL Version 11.2.0.2.0 - Production
                  
                  SQL> 
                  thanks
                  • 6. Re: assign value while parsing XML data into oracle table
                    user12236189
                    Hi,

                    This seems will be working, but is it possible to check this value,, I need to do the following will be applicable only for Ordering on this tag
                    <questiontype>Ordering</questiontype>
                    All the other I will put in to the regular XMLTABLE, hope fully it'll work.
                    Please let me know if possible for to check the value and extract and also the rn instead of going on to the next line is it possible for us to get like
                    {}
                    1,2,3,4
                    {}

                    Thanks for your time .

                    Thanks,
                    • 7. Re: assign value while parsing XML data into oracle table
                      odie_63
                      >
                      Please let me know if possible for to check the value and extract and also the rn instead of going on to the next line is it possible for us to get like
                      {}
                      1,2,3,4
                      {}
                      >
                      Sorry, again, I have no idea of what you want to do.

                      Could you explain more, and give a clear explanation of the output you require?
                      • 8. Re: assign value while parsing XML data into oracle table
                        user12236189
                        Please refer the main (original post) XML doc for this reference
                        In the main
                        *<response_label ident=*
                             as per the order I need to assign the rn like for example
                        -- this is from the main block
                        <response_label ident="a73c9ca485ee483b83d9f6d279a3c8c0"          rn should be 1
                        <response_label ident="5e35eed9bb1c4d80b62127d84e5b9ed1"          rn should be 2
                        <response_label ident="5ad7fc4289b74c7894ef029451824e11"          rn should be 3
                        <response_label ident="093d2c040e05472285a0ffb4f1f9d033"          rn should be 4
                        Then in the following
                              <conditionvar>
                                <and>
                                  <varequal respident="response" case="No">a73c9ca485ee483b83d9f6d279a3c8c0</varequal>
                                  <varequal respident="response" case="No">5ad7fc4289b74c7894ef029451824e11</varequal>
                                  <varequal respident="response" case="No">093d2c040e05472285a0ffb4f1f9d033</varequal>
                                  <varequal respident="response" case="No">5e35eed9bb1c4d80b62127d84e5b9ed1</varequal>
                                </and>
                              </conditionvar>
                        I need to get the out put like

                        *1,3,4,2* or *1342*

                        First one is question's order answer, the second one is actual answer.

                        Is it possible for us to extract the value in this format,

                        AND this case is only applicable for certain question types
                        which is defined in the
                        <questiontype>     </questiontype>
                        Based on the type, i need to extract the value if other than this ordering remaining will be stright as no conitionvar/varequal not exists

                        Any help would be greatly appreciated.

                        Thanks,
                        • 9. Re: assign value while parsing XML data into oracle table
                          odie_63
                          I need to get the out put like

                          *1,3,4,2* or *1342*
                          OK, so you actually need a comma-separated list of numbers... ??

                          What I still don't get so far is how the result set should look like. How does this list should appear besides other values you want to extract?

                          So one last effort from your part, please : post the exact output you require for both cases you described (questiontype=Ordering or other).
                          If you don't do that, I'm afraid I can't help you.
                          • 10. Re: assign value while parsing XML data into oracle table
                            odie_63
                            Maybe this?
                            SQL> select x1.title, x2.*
                              2  from tmp_xml t
                              3     , xmltable(
                              4       '/item[itemmetadata/questiontype="Ordering"]'
                              5       passing t.object_value
                              6       columns title           varchar2(15) path '@title'
                              7             , response_labels xmltype      path 'presentation/flow/flow[@class="RESPONSE_BLOCK"]/response_lid/render_choice/flow_label/response_label'
                              8             , varequals       xmltype      path 'resprocessing/respcondition[@title="correct"]/conditionvar/and/varequal'
                              9       ) x1
                             10     , xmltable(
                             11       'string-join(
                             12          for $r in $vareqs/varequal
                             13          return
                             14            for $i at $p in $labels/response_label
                             15            where $i/@ident = $r/text()
                             16            return string($p)
                             17        , ","
                             18        )'
                             19       passing x1.response_labels as "labels"
                             20             , x1.varequals as "vareqs"
                             21       columns res_value varchar2(80) path '.'
                             22       ) (+) x2
                             23  ;
                             
                            TITLE           RES_VALUE
                            --------------- --------------------------------------------------------------------------------
                            0009789         1,3,4,2