Need help in writing oracle query from XML Column

Question

I have the below XML in an oracle table XML column:

<?xml version="1.0" encoding="UTF-8"?>
<Employee>
  <Emp_information emp_name="SSS">
  </Emp_information>
  <emp_content>
    <emp_part part_id="A" >
       <emp_scoring_information equivalent_value="Test" >
        <empset empset_weight="1">
          <emp_key value="1"/>
        </empset>
      </emp_scoring_information>
    </emp_part>
    <emp_part part_id="B" >
      <emp_scoring_information equivalent_value_flag="Test1" >
        <empset empset_weight="1" >
          <emp_key value="1"/>
          <emp_key value="2"/>
        </empset>
      </emp_scoring_information>
    </emp_part>
  </emp_content>
</Employee>

I am trying to display the value in below format using oracle query

A=1,B=1,2

How can I solve this issue to print below format data using SQL query?

I wanted to explain the output value (A=1,B=1,2) in detail (A=1) A is the value from emp_part (part_id value) = is the static value ,1 is the value from empset emp_key value, like that it needs to do the same thing for next emp_part also (part_id B) is having empset with multiple emp_key values, in this case, it needs to display B=1,2 each emppart is separated by comma (A=1, B= 1,2).

Arkadiusz Łukasiewicz · Accepted Answer · 2019-11-26 08:47:26Z

2

with src as (select xmltype('<Employee>
  <Emp_information emp_name="SSS">
  </Emp_information>
  <emp_content>
    <emp_part part_id="A" >
       <emp_scoring_information equivalent_value="Test" >
        <empset empset_weight="1">
          <emp_key value="1"/>
        </empset>
      </emp_scoring_information>
    </emp_part>
    <emp_part part_id="B" >
      <emp_scoring_information equivalent_value_flag="Test1" >
        <empset empset_weight="1" >
          <emp_key value="1"/>
          <emp_key value="2"/>
        </empset>
      </emp_scoring_information>
    </emp_part>
  </emp_content>
</Employee>') xml  from dual)
select xmlquery('string-join($d/Employee/emp_content/emp_part/concat(@part_id,"=",string-join(emp_scoring_information/empset/emp_key/@value,",") ),",")' passing xml as "d" returning content) from src

string-join - concatenate string sequence using separator
concat - concatenate string str1 + str2 + etc..

And it works like two loop, 1st loop is selecting @part_id, 2nd is selecting and aggregating dependent @values

answered Nov 26, 2019 at 8:47

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marcus Over a year ago

have you tested the query?..when i run this query its returning null

Arkadiusz Łukasiewicz Over a year ago

yes, example here dbfiddel. If it's null try to wrap xmlquery with xmlcast( xmlqueryas varchar2(1000))

Saritha Over a year ago

Thank you so much .. I am trying to display in the same column with the below data: one time i need to show not equal to value with part B and other time i need to show not equal to with Part A, below output has to be displayed. A=3,B=1 || A=3,B not equal to1 || A not equal to 3,B=1

Saritha Over a year ago

is it doable in the query?

Arkadiusz Łukasiewicz Over a year ago

Yes it is, You have to add predicates.

Collectives™ on Stack Overflow

Need help in writing oracle query from XML Column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related