Topic
  • 4 replies
  • Latest Post - ‏2012-04-09T07:05:39Z by MatthiasNicola
SystemAdmin
SystemAdmin
230 Posts

Pinned topic How to select the distinct values from the XML document

‏2012-04-08T10:41:27Z |
Hi,

Following is the XML Document.

<Account>
<ServId>1</ServId>
<AlertCtgyTypCd>1|2|3|4|5|6</AlertCtgyTypCd>
</Account>
<Account>
<ServId>2</ServId>
<AlertCtgyTypCd>1|2|3|7|8</AlertCtgyTypCd>
</Account>

The expected output should be as follows.

SERVID CTGYCD
1|2 1|2|3|4|5|6|7|8

So, here we would need to filter the duplicates in <AlertCtgyTypCd> tag. Could you please let me know how to achieve this?

For the SERVID, I can do a string-join and achieve that, but for the CTGYCD, I need to remove the duplicates and then string-join.

Since multiple values are stored in the single XML tag seperated by '|', I am not sure how to arrive at the result provided above.

Many Thanks
Tilak
Updated on 2012-04-09T07:05:39Z at 2012-04-09T07:05:39Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: How to select the distinct values from the XML document

    ‏2012-04-08T19:15:39Z  
    Hi Tilak,

    yes, the key problem here is that the element <AlertCtgyTypCd> contains a list of values but there are no XML tags that mark the individual values in the list. Instead, a simple string delimiter (|) is being used.

    So, you need to break up the list of values into a sequence of separate values before you can remove duplicates. This can be done with the function fn:tokenize.

    A description and some examples of fn:tokenize can be found here:

    http://nativexmldatabase.com/2010/01/25/tokenize-it/
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrfntkz.html

    Give that a try. After tokenizing the list of values, use the function fn:distinct-values to remove duplicates.

    Please post again here if you need more help - or to share your solution with others you read this forum.

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • SystemAdmin
    SystemAdmin
    230 Posts

    Re: How to select the distinct values from the XML document

    ‏2012-04-09T06:49:09Z  
    Hi Tilak,

    yes, the key problem here is that the element <AlertCtgyTypCd> contains a list of values but there are no XML tags that mark the individual values in the list. Instead, a simple string delimiter (|) is being used.

    So, you need to break up the list of values into a sequence of separate values before you can remove duplicates. This can be done with the function fn:tokenize.

    A description and some examples of fn:tokenize can be found here:

    http://nativexmldatabase.com/2010/01/25/tokenize-it/
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrfntkz.html

    Give that a try. After tokenizing the list of values, use the function fn:distinct-values to remove duplicates.

    Please post again here if you need more help - or to share your solution with others you read this forum.

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias,

    wow:)

    Thank you soooo much. It is working now. I am getting the result how I want :)

    Just as an information..here is the synatax I have used.

    SELECT XMLCAST(XMLQUERY('string-join(distinct-values($Service/StatCardProf/CardProf/tokenize(AlertCtgyTypCd, "\|")),"|")' passing M.STATIC_CARD_XML as "Service") as Varchar(2000)) as Card_option_enrol_date
    ...
    ...

    Many Thanks for your timely help.

    Cheers
    Tilak
  • SystemAdmin
    SystemAdmin
    230 Posts

    Re: How to select the distinct values from the XML document

    ‏2012-04-09T06:53:11Z  
    Hi Tilak,

    yes, the key problem here is that the element <AlertCtgyTypCd> contains a list of values but there are no XML tags that mark the individual values in the list. Instead, a simple string delimiter (|) is being used.

    So, you need to break up the list of values into a sequence of separate values before you can remove duplicates. This can be done with the function fn:tokenize.

    A description and some examples of fn:tokenize can be found here:

    http://nativexmldatabase.com/2010/01/25/tokenize-it/
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrfntkz.html

    Give that a try. After tokenizing the list of values, use the function fn:distinct-values to remove duplicates.

    Please post again here if you need more help - or to share your solution with others you read this forum.

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias,

    wow:)

    Thank you soooo much. It is working now. I am getting the result how I want :)

    Just as an information..here is the synatax I have used.

    SELECT XMLCAST(XMLQUERY('string-join(distinct-values($Service/StatCardProf/CardProf/tokenize(AlertCtgyTypCd, "\|")),"|")' passing M.STATIC_CARD_XML as "Service") as Varchar(2000)) as Card_option_enrol_date
    ...
    ...

    Many Thanks for your timely help.

    Cheers
    Tilak
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: How to select the distinct values from the XML document

    ‏2012-04-09T07:05:39Z  
    Hi Matthias,

    wow:)

    Thank you soooo much. It is working now. I am getting the result how I want :)

    Just as an information..here is the synatax I have used.

    SELECT XMLCAST(XMLQUERY('string-join(distinct-values($Service/StatCardProf/CardProf/tokenize(AlertCtgyTypCd, "\|")),"|")' passing M.STATIC_CARD_XML as "Service") as Varchar(2000)) as Card_option_enrol_date
    ...
    ...

    Many Thanks for your timely help.

    Cheers
    Tilak
    Very nice! Thanks for sharing the final solution.


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/