oracle query to get list of xml nodes and their values using oracle

Question

I have a XML column and i need help to write the query to display the nodes and their values.

below is the data from my xml column:

<item_content>
  <stimulus_reference>
    <table_wrapper>
      <table frame="all" colsep="1" rowsep="1" pgwide="0">
        <tgroup cols="3">
          <thead>
            <row>
              <entry />
              <entry align="center">Male</entry>
              <entry align="center">Female</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry align="left">Juniors</entry>
              <entry align="right">12</entry>
              <entry align="right">3</entry>
            </row>
            <row>
              <entry align="left">Seniors</entry>
              <entry align="right">9</entry>
              <entry align="right">21</entry>
            </row>
          </tbody>
        </tgroup>
      </table>
    </table_wrapper>
    <rationale>This is a rationale paragraph</rationale>
  </stimulus_reference>
  <task>
    <item_stem>
      <stem_paragraph>The table above shows the distribution of students that attended a concert, by class and gender.</stem_paragraph>
    </item_stem>
    <item_response>
      <response_choices>
        <columnar_choice_list>
          <columns align="character" align_character="1">
            <choice_row numeric_identifier="1">
              CHROW1
              <choice_cell>3</choice_cell>
            </choice_row>
            <choice_row numeric_identifier="2">
              CHROW2
              <choice_cell>15</choice_cell>
            </choice_row>
            <choice_row numeric_identifier="3">
              CHROW3
              <choice_cell>2102</choice_cell>
            </choice_row>
            <choice_row numeric_identifier="4">
              CHROW4
              <choice_cell>321</choice_cell>
            </choice_row>
            ColumnsData
          </columns>
        </columnar_choice_list>
      </response_choices>
    </item_response>
  </task>
  <math_expression>1+2=3</math_expression>
</item_content>

i want the output in the below format

Node_Name               Node_val

stimulus_reference
table_wrapper
table
tgroup
thead
row
entry
entry                   Male
entry                   Female
tbody
row
entry                   Juniors
entry                   12
entry                   3
row
entry                   Seniors 
entry                   9
entry                   21
task
item_stem
stem_paragraph              The table above shows the distribution of students that attended a concert, by class and gender. 
item_response
response_choices
columnar_choice_list
columns                 ColumnsData
choice_row              CHROW1
choice_cell             3
choice_row              CHROW2
choice_cell             15
choice_row              CHROW3
choice_cell             2102
choice_row              CHROW4
choice_cell             321

appreciate your help on this.

Arkadiusz Łukasiewicz · Accepted Answer · 2017-08-30 06:24:28Z

//* returns all nodes on all levels.
name() returns node name
text() returns node value

If you want only text nodes you have to replace //* with //*[text()]

select * from xmltable('//*' passing  xmltype ('
<item_content>
  <stimulus_reference>
    <table_wrapper>
      <table frame="all" colsep="1" rowsep="1" pgwide="0">
        <tgroup cols="3">
          <thead>
            <row>
              <entry />
              <entry align="center">Male</entry>
              <entry align="center">Female</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry align="left">Juniors</entry>
              <entry align="right">12</entry>
              <entry align="right">3</entry>
            </row>
            <row>
              <entry align="left">Seniors</entry>
              <entry align="right">9</entry>
              <entry align="right">21</entry>
            </row>
          </tbody>
        </tgroup>
      </table>
    </table_wrapper>
    <rationale>This is a rationale paragraph</rationale>
  </stimulus_reference>
  <task>
    <item_stem>
      <stem_paragraph>The table above shows the distribution of students that attended a concert, by class and gender.</stem_paragraph>
    </item_stem>
    <item_response>
      <response_choices>
        <columnar_choice_list>
          <columns align="character" align_character="1">
            <choice_row numeric_identifier="1">
              CHROW1
              <choice_cell>3</choice_cell>
            </choice_row>
            <choice_row numeric_identifier="2">
              CHROW2
              <choice_cell>15</choice_cell>
            </choice_row>
            <choice_row numeric_identifier="3">
              CHROW3
              <choice_cell>2102</choice_cell>
            </choice_row>
            <choice_row numeric_identifier="4">
              CHROW4
              <choice_cell>321</choice_cell>
            </choice_row>
            ColumnsData
          </columns>
        </columnar_choice_list>
      </response_choices>
    </item_response>
  </task>
  <math_expression>1+2=3</math_expression>
</item_content>')
columns 
 node_name varchar2(100) path 'name()',
 node_value varchar2(100) path 'text()' 
)

Collectives™ on Stack Overflow

oracle query to get list of xml nodes and their values using oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related