db2 XML combine fields

Question

I need a xml to create a view. Having table with xml data : tbl1 (xml datas)

I want to make a view on it so that the data will be in relational form. Structure of the xml is the folowing:

<persons>
<person>
<name>BLA1</name>
<parameters>
<param>
<key>KKK1</key>
<value>VVV1</value>
</param>
<param>
<key>KKK2</key>
<value>VVV2</value>
</param>
</parameters>
</person>
<person>
<name>BLA2</name>
<parameters>
<param>
<key>KKK21</key>
<value>VVV21</value>
</param>
<param>
<key>KKK22</key>
<value>VVV22</value>
</param>
<param>
<key>KKK23</key>
<value>VVV23</value>
</param>

</parameters>
</person>
</persons>

and the desired model in new table would be: (NAME, PARAMS) where PARAMS should be in the form of key=value;key=value;

i.e sample row would be : 'BLA1' 'KKK1=VVV1;KKK2=VVV2;'

Answer 1

select name, listagg(key||'='||value, ';') params
from 
xmltable( '$doc/persons/person/parameters/param' passing 
 XMLPARSE(DOCUMENT '
<persons>
  <person>
    <name>BLA1</name>
    <parameters>
      <param>
        <key>KKK1</key>
        <value>VVV1</value>
      </param>
      <param>
        <key>KKK2</key>
        <value>VVV2</value>
      </param>
    </parameters>
  </person>
  <person>
    <name>BLA2</name>
    <parameters>
      <param>
      <key>KKK21</key>
      <value>VVV21</value>
      </param>
      <param>
        <key>KKK22</key>
        <value>VVV22</value>
      </param>
      <param>
        <key>KKK23</key>
        <value>VVV23</value>
      </param>
    </parameters>
  </person>
</persons>
') as "doc"
    COLUMNS "NAME"  VARCHAR(64)  PATH '../../name'
    ,       "KEY"   VARCHAR(64)  PATH './key'    
    ,       "VALUE" VARCHAR(64)  PATH './value'
    ) as x
group by name;

Answer 2

There is probably a way to do the list aggregation in xquery, but I'm no expert in that, and this is as far as I can get

select  *
from 
xmltable( '$doc/persons/person' passing 
 XMLPARSE(DOCUMENT '<persons>
<person><name>BLA1</name><parameters>
<param><key>KKK1</key><value>VVV1</value></param>
<param><key>KKK2</key><value>VVV2</value></param></parameters></person>
<person><name>BLA2</name><parameters>
<param><key>KKK21</key><value>VVV21</value></param>
<param><key>KKK22</key><value>VVV22</value></param>
<param><key>KKK23</key><value>VVV23</value></param></parameters></person></persons>') as "doc"
    COLUMNS "NAME" VARCHAR(64)  PATH './name'
    ,       "PARAMS" VARCHAR(64)  PATH 'fn:string-join(./parameters/param/key,";")'    
    ,       "VALUES" VARCHAR(64)  PATH 'fn:string-join(./parameters/param/value,";")'       
    ) as x

returns

 NAME PARAMS            VALUES
 ---- ----------------- -----------------
 BLA1 KKK1;KKK2         VVV1;VVV2
 BLA2 KKK21;KKK22;KKK23 VVV21;VVV22;VVV23

I'm not sure how to merge the PARAMS and VALUES into a single column in xquery

Answer 3

For the record, this is how you would use Mark's answer in a view

create table tbl1 (datas xml) organize by row;
INSERT INTO tbl1 VALUES '<persons>
<person><name>BLA1</name><parameters>
<param><key>KKK1</key><value>VVV1</value></param>
<param><key>KKK2</key><value>VVV2</value></param></parameters></person>
<person><name>BLA2</name><parameters>
<param><key>KKK21</key><value>VVV21</value></param>
<param><key>KKK22</key><value>VVV22</value></param>
<param><key>KKK23</key><value>VVV23</value></param></parameters></person></persons>'
;

CREATE VIEW vw1 AS
select  NAME
,       listagg(key||'='||value, ';') params
from tbl1
, xmltable( '$doc/persons/person/parameters/param' passing datas as "doc"
    COLUMNS "NAME" VARCHAR(64)  PATH '../../name'
    ,       "KEY" VARCHAR(64)  PATH './key'    
    ,       "VALUE" VARCHAR(64)  PATH './value'     
    ) 
group by NAME