Topic
  • 2 replies
  • Latest Post - ‏2012-11-02T17:42:20Z by lfly
lfly
lfly
5 Posts

Pinned topic XML having multiple items per node query issue

‏2012-11-02T02:07:46Z |
Hi,

We are on DB2 9.5 and Java 6 application (JDBC type 4 driver) and we encounter difficulties with the result of a query on an XML having multiple items per node.

Example of two rows :

ROW 1 contains this XML :

<data> <rubrique> <item>RUB_0</item> </rubrique> <titre>mon titre</titre> </data>


ROW 2 contains this XML :

<data> <rubrique> <item>RUB_1</item> <item> RUB_2</item> </rubrique> <titre>mon titre</titre> </data>

We try to have the following result :
RUB_0
RUB_1
RUB_2

We have wrote this query :

SELECT XMLCAST( XMLQUERY(
'for $r in $s/data/rubriques/item return string(<t>;</t>)' PASSING jal.CONTRIB_XML AS 
"s") as clob) as rubrique, XMLQUERY(
'string($s/data/titre)' PASSING jal.CONTRIB_XML AS 
"s") as titre FROM TLCONTRIB jal

We expected to have this result (we tried to concatenate multiple nodes with ";" as separator) :
RUB_0
RUB_1;RUB_2

Instead, we have an error :

SQL State = 10507 SQL Code = -16003 SQL Message = An expression of data type "(item(),item()+ )" cannot be used when the data type "CLOB_1048576" is expected in the context. Error QName=err:XPTY0004. Exception message = com.ibm.db2.jcc.c.SqlException: An expression of data type "(item(),item()+ )" cannot be used when the data type "CLOB_1048576" is expected in the context. Error QName=err:XPTY0004.

We tried

fn :concat, fn :string, fn :string-join
with the for expression without success.

  • How can we concatenate multiple nodes with separator in a varchar or clob ?
  • Is there another solution ?

Could you please help us.
Thanks
Updated on 2012-11-02T17:42:20Z at 2012-11-02T17:42:20Z by lfly
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XML having multiple items per node query issue

    ‏2012-11-02T03:30:54Z  
    Hi there,

    several solutions to your problem are available in the section "Handling multiple values per cell" in this article:
    http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/

    Also, the following examples might be helpful:

    
    CREATE TABLE tlcontrib(contrib_xml XML);   INSERT INTO tlcontrib VALUES(
    '<data> <rubrique> <item>RUB_0</item> </rubrique> <titre>mon titre A</titre> </data>
    ');   INSERT INTO tlcontrib VALUES(
    '<data> <rubrique> <item>RUB_1</item> <item>RUB_2</item> </rubrique> <titre>mon titre B</titre> </data>
    ');     SELECT X.* FROM tlcontrib, XMLTABLE(
    '$CONTRIB_XML/data/rubrique/item' COLUMNS item  VARCHAR(20)  PATH 
    '.') AS X; ITEM -------------------- RUB_0 RUB_1 RUB_2   3 record(s) selected.   SELECT X.* FROM tlcontrib, XMLTABLE(
    '$CONTRIB_XML/data/rubrique/item' COLUMNS item  VARCHAR(20)  PATH 
    '.', tite  VARCHAR(20)  PATH 
    '../../titre') AS X;   ITEM                 TITE -------------------- -------------------- RUB_0                mon titre A RUB_1                mon titre B RUB_2                mon titre B   3 record(s) selected.         SELECT XMLCAST( XMLQUERY(
    '$CONTRIB_XML/data/rubrique/string-join(item,";")') AS VARCHAR(30)) As items, XMLCAST( XMLQUERY(
    '$CONTRIB_XML/data/titre') AS VARCHAR(15)) As titre FROM TLCONTRIB jal;     ITEMS                          TITRE ------------------------------ --------------- RUB_0                          mon titre A RUB_1;RUB_2                    mon titre B   2 record(s) selected.
    

    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • lfly
    lfly
    5 Posts

    Re: XML having multiple items per node query issue

    ‏2012-11-02T17:42:20Z  
    Hi Matthias,
    It's OK now. We did not use the string-join at the right place !
    Thank-you very much for answering quickly.
    Laurent