Prev: Data Logistics of RFID
Next: Create Role as db2inst1 Gives Error
From: Tonkuma on 19 Jun 2010 11:25
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>


From: Tonkuma on 21 Jun 2010 05:48
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)
)
|| '</DESKTOP>'
FROM M
, 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
;

From: brunoalsantos on 22 Jun 2010 13:15
On 21 jun, 06:48, Tonkuma <tonk...(a)fiberbit.net> wrote:
> 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)
>        )
>        || '</DESKTOP>'
>   FROM M
>      , 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
> ;

Hi Tonkuma,

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

Bruno.
From: Tonkuma on 22 Jun 2010 21:43
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)
)
|| '</DESKTOP>'
FROM M
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
;

First  |  Prev  | 
Pages: 1 2
Prev: Data Logistics of RFID
Next: Create Role as db2inst1 Gives Error