Prev: Data Logistics of RFID
Next: Create Role as db2inst1 Gives Error
From: brunoalsantos on 14 Jun 2010 14:08
Hi all,

DB2 V9.5 FP5.

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

[db2inst1(a)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.
From: Tonkuma on 15 Jun 2010 11:08
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.


From: brunoalsantos on 18 Jun 2010 16:19
On 15 jun, 12:08, Tonkuma <tonk...(a)fiberbit.net> 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?
From: Tonkuma on 18 Jun 2010 18:10
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>
From: Tonkuma on 19 Jun 2010 11:11
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
;

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