CODEWITHSUNDEEP
sqlxmloracle-database
Saritha
Saritha

Reputation: 89

Need help in writing oracle query from XML Column

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).

Upvotes: 1

Views: 63

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

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

Upvotes: 2

Related Questions