Hi all,

DB2 V9.5 FP5.

In my XML query, I've a table multi valued like the following example:

[db2inst1@JULIANA-PIRES ~]$ db2 "select * from T"

ID MAC_ADDRESS
----------- -------------------------
1 00:12:13:14:15:16
1 00:22:23:24:25:26

2 record(s) selected.


Using this statement with XMLGROUP (db2 "select
XMLSERIALIZE(XMLGROUP(M.ID, M.HOSTNAME, T.MAC_ADDRESS OPTION ROOT
"desktop") AS CLOB (50M)) FROM M INNER JOIN T on (M.ID = T.ID)"), I've
got this result set ...

<DESKTOP>
<row>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
</row>
<row>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
</row>
</DESKTOP>


I need an output that looks the following:


<DESKTOP>
<row>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
</row>
</DESKTOP>

Can you any ideas?

Thanks in advance.

Here is an example.
But, I wondered if there would be other shorter ways.

WITH
T( id , mac_address ) AS (
VALUES
( 1 , '00:12:13:14:15:16' )
, ( 1 , '00:22:23:24:25:26' )
, ( 2 , '00:32:33:34:35:36' )
)
, M( id , hostname ) AS (
VALUES
( 1 , 'MACHINE_ONE' )
, ( 2 , 'Machine Two' )
)
SELECT '<DESKTOP>' ||
XMLSERIALIZE(
XMLAGG(
XMLELEMENT(
NAME row
, XMLFOREST(id , hostname)
, mac_address
)
)
AS CLOB (50M)
)
, XMLAGG( XMLFOREST(T.mac_address) ) AS mac_address
FROM M
INNER JOIN
T
ON M.ID = T.ID
GROUP BY
M.id , M.hostname
) s
;

The result of the query will look like the following:
<DESKTOP>
<ROW>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
</ROW>
<ROW>
<ID>2</ID>
<HOSTNAME>Machine Two</HOSTNAME>
<MAC_ADDRESS>00:32:33:34:35:36</MAC_ADDRESS>
</ROW>
</DESKTOP>

My some other trial queries produced extra tags or less tags than
showed above.

On 15 jun, 12:08, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

Hi Tonkuma,

Thanks for the post.

I had to add a new column with multi valued values ... and the XML
Output created a cartesian product, like the following:

WITH
T( id , mac_address ) AS (
VALUES
( 1 , '00:12:13:14:15:16' ),
( 1 , '00:22:23:24:25:26' ),
( 2 , '00:32:33:34:35:36' )
)

, S ( id, sw_name) AS (
VALUES
(1, 'Automatos'),
(1, 'Adobe'),
(1, 'Office')
)

, M( id , hostname ) AS (
VALUES
( 1 , 'MACHINE_ONE' ),
( 2 , 'Machine Two' )
)


SELECT '<DESKTOP>' ||
XMLSERIALIZE(
XMLAGG(
XMLELEMENT(
NAME row,
XMLFOREST(id , hostname),
mac_address,
sw_name
)
)
AS CLOB (50M)
)
FROM (SELECT M.id , M.hostname,
XMLAGG( XMLFOREST(T.mac_address) ) AS mac_address,
XMLAGG( XMLFOREST(S.sw_name) ) AS sw_name
FROM M
INNER JOIN T ON (M.ID = T.ID)
INNER JOIN S ON (M.ID = S.ID)
GROUP BY
M.id , M.hostname
) s
;

The result of the query will look like the following:

<DESKTOP>
<ROW>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
<SW_NAME>Automatos</SW_NAME>
<SW_NAME>Automatos</SW_NAME>
<SW_NAME>Adobe</SW_NAME>
<SW_NAME>Adobe</SW_NAME>
<SW_NAME>Office</SW_NAME>
<SW_NAME>Office</SW_NAME>
</ROW>
</DESKTOP>

Any ideas?

Please try:

WITH
T( id , mac_address ) AS (
VALUES
( 1 , '00:12:13:14:15:16' )
, ( 1 , '00:22:23:24:25:26' )
, ( 2 , '00:32:33:34:35:36' )
)

, S ( id, sw_name ) AS (
VALUES
(1 , 'Automatos' )
, (1 , 'Adobe' )
, (1 , 'Office' )
)

, M( id , hostname ) AS (
VALUES
( 1 , 'MACHINE_ONE' )
, ( 2 , 'Machine Two' )
)

SELECT '<DESKTOP>' ||
XMLSERIALIZE(
XMLAGG(
XMLELEMENT(
NAME row
, XMLFOREST(id , hostname)
, mac_address
, sw_name
)
)
AS CLOB (50M)
)
, XMLAGG( XMLFOREST(T.mac_address) ) AS mac_address
, XMLAGG( XMLFOREST(S.sw_name ) ) AS sw_name
FROM M
INNER JOIN
(SELECT id , mac_address
, ROW_NUMBER()
OVER(PARTITION BY id) AS rn
FROM T
) AS T
FULL OUTER JOIN
(SELECT id , sw_name
, ROW_NUMBER()
OVER(PARTITION BY id) AS rn
FROM S
) AS S
ON S.id = T.id
AND S.rn = T.rn
ON M.id = COALESCE(S.id , T.id)
GROUP BY
M.id , M.hostname
) s
;

For the part of FULL OUTER JOIN,
please see my example in this thread:
http://www.dbforums.com/db2/1657599-query-required.html

The result of the query will look like the following:

<DESKTOP>
<ROW>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
<SW_NAME>Automatos</SW_NAME>
<SW_NAME>Adobe</SW_NAME>
<SW_NAME>Office</SW_NAME>
</ROW>
<ROW>
<ID>2</ID>
<HOSTNAME>Machine Two</HOSTNAME>
<MAC_ADDRESS>00:32:33:34:35:36</MAC_ADDRESS>
</ROW>
</DESKTOP>

This is shorter and may be easier to understand.

SELECT '<DESKTOP>' ||
XMLSERIALIZE(
XMLAGG(
XMLELEMENT(
NAME row
, XMLFOREST(id , hostname)
, mac_address
, sw_name
)
)
AS CLOB (50M)
)
LEFT OUTER JOIN
LATERAL
(SELECT XMLAGG( XMLFOREST(mac_address) ) AS mac_address
FROM T
WHERE T.id = M.id
) AS T
ON 0=0
LEFT OUTER JOIN
LATERAL
(SELECT XMLAGG( XMLFOREST(sw_name) ) AS sw_name
FROM S
WHERE S.id = M.id
) AS S
ON 0=0
;

This is more short, but produced extra tags.

SELECT XMLSERIALIZE(
XMLGROUP(
id , hostname
, mac_addresses
, sw_names
OPTION ROOT "DESKTOP"
)
AS CLOB (50M)
)
FROM M
LEFT OUTER JOIN
LATERAL
(SELECT XMLAGG( XMLFOREST(T.mac_address) ) AS mac_addresses
FROM T
WHERE T.id = M.id
) AS T
ON 0=0
LEFT OUTER JOIN
LATERAL
(SELECT XMLAGG( XMLFOREST(S.sw_name) ) AS sw_names
FROM S
WHERE S.id = M.id
) AS S
ON 0=0
;

The result of the query looks like the following:
(Extra tags <MAC_ADDRESSES> and <SW_NAMES> are included.)

<DESKTOP>
<row>
<ID>1</ID>
<HOSTNAME>MACHINE_ONE</HOSTNAME>
<MAC_ADDRESSES>
<MAC_ADDRESS>00:12:13:14:15:16</MAC_ADDRESS>
<MAC_ADDRESS>00:22:23:24:25:26</MAC_ADDRESS>
</MAC_ADDRESSES>
<SW_NAMES>
<SW_NAME>Automatos</SW_NAME>
<SW_NAME>Adobe</SW_NAME>
<SW_NAME>Office</SW_NAME>
</SW_NAMES>
</row>
<row>
<ID>2</ID>
<HOSTNAME>Machine Two</HOSTNAME>
<MAC_ADDRESSES>
<MAC_ADDRESS>00:32:33:34:35:36</MAC_ADDRESS>
</MAC_ADDRESSES>
</row>
</DESKTOP>

INNER JOIN with traditional syntax will be enough(OUTER JOIN will be
not neccessary).

SELECT '<DESKTOP>' ||
XMLSERIALIZE(
XMLAGG(
XMLELEMENT(
NAME row
, XMLFOREST(id , hostname)
, mac_address
, sw_name
)
)
AS CLOB (50M)
)
, LATERAL
(SELECT XMLAGG( XMLFOREST(mac_address) ) AS mac_address
FROM T
WHERE T.id = M.id
) AS T
, LATERAL
(SELECT XMLAGG( XMLFOREST(sw_name) ) AS sw_name
FROM S
WHERE S.id = M.id
) AS S
;

or, if extra tags were allowed:

SELECT XMLSERIALIZE(
XMLGROUP(
id , hostname
, mac_addresses
, sw_names
OPTION ROOT "DESKTOP"
)
AS CLOB (1M)
)
FROM M
, LATERAL
(SELECT XMLAGG( XMLFOREST(mac_address) ) AS mac_addresses
FROM T
WHERE T.id = M.id
) AS T
, LATERAL
(SELECT XMLAGG( XMLFOREST(sw_name) ) AS sw_names
FROM T
WHERE S.id = M.id
) AS S
;

On 21 jun, 06:48, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
Hi Tonkuma,

Now the output is perfect.
Thank you very much for the tips.

Bruno.

One supplement.

Because you are using DB2 V9.5,
you can use CROSS JOIN instead of tarditional comma syntax for join.

Like this:
SELECT '<DESKTOP>' ||
XMLSERIALIZE(
XMLAGG(
XMLELEMENT(
NAME row
, XMLFOREST(id , hostname)
, mac_address
, sw_name
)
)
AS CLOB (50M)
)
CROSS JOIN
LATERAL
(SELECT XMLAGG( XMLFOREST(mac_address) ) AS mac_address
FROM T
WHERE T.id = M.id
) AS T
CROSS JOIN
LATERAL
(SELECT XMLAGG( XMLFOREST(sw_name) ) AS sw_name
FROM S
WHERE S.id = M.id
) AS S
;