This discussion is archived
10 Replies Latest reply: Jun 17, 2012 12:28 PM by odie_63 RSS

assign value while parsing XML data into oracle table

user12236189 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points