Another question XML Output

On 15 jun, 12:08, Tonkuma wrote:
> 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)
>        )
>        || '</DESKTOP>'
>   FROM (SELECT M.id , M.hostname
>              , 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.


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)
)
|| '</DESKTOP>'

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?
 >> Stay informed about: Another question XML Output 

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)
)
|| '</DESKTOP>'
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
(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>
 >> Stay informed about: Another question XML Output 

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)
)
|| '</DESKTOP>'
FROM M
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
;
 >> Stay informed about: Another question XML Output 

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>
 >> Stay informed about: Another question XML Output 

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
;
 >> Stay informed about: Another question XML Output 

On 21 jun, 06:48, Tonkuma 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.
 >> Stay informed about: Another question XML Output 

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
;
 >> Stay informed about: Another question XML Output