Topic
  • 11 replies
  • Latest Post - ‏2014-08-29T02:02:09Z by MatthiasNicola
BenEisenstein
BenEisenstein
8 Posts

Pinned topic XMLTABLE and returning name of child node?

‏2011-01-25T15:36:19Z |
I am trying to find the name of a child element. The sturcture is /a/b/c/d or /a/b/e/d but in the future there can be different child node to b.

This works but the query would have to be changed if we get a new child to b and I would like to avoid maintenance to the query:

select nodeKind FROM tab1 r
,XMLTABLE(xmlnamespaces (DEFAULT '*')
,'$doc/a' passing r.xml_column as "doc"
COLUMNS nodeKind VARCHAR(30) PATH 'b/(c|e)/name()'
) as t
;

I tried using a PATH of 'b/*/name() results in an error and does not work.I get An expression of data type "( item(), item()+ )" cannot be used when the data type "VARCHAR_30" is expected in the context. Error QName=err:XPTY0004.. SQLCODE=-16003, SQLSTATE=10507, DRIVER=3.61.65
Any way to solve this?
Updated on 2011-01-25T22:31:26Z at 2011-01-25T22:31:26Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2011-01-25T18:28:41Z  
    Hi Ben,

    the error that you got with the path expression 'b/*/name()' means that this expression has returned more than one value. But, the column expressions in XMLTABLE need to return single values, otherwise casting to the specified SQL data type is not possible.
    So, if 'b/*/name()' produces this error then it seems like the element b has more than one child element. Hence, you must narrow it down to one specific child element for which you want the name(). Either you narrow down by element name, as you did with the expression (c|e), or maybe by position. For example, the
    path b/*[1]/name() would always produce the name of the first child element of b.
    Would that wwork for you?

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • BenEisenstein
    BenEisenstein
    8 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2011-01-25T22:11:03Z  
    Hi Ben,

    the error that you got with the path expression 'b/*/name()' means that this expression has returned more than one value. But, the column expressions in XMLTABLE need to return single values, otherwise casting to the specified SQL data type is not possible.
    So, if 'b/*/name()' produces this error then it seems like the element b has more than one child element. Hence, you must narrow it down to one specific child element for which you want the name(). Either you narrow down by element name, as you did with the expression (c|e), or maybe by position. For example, the
    path b/*[1]/name() would always produce the name of the first child element of b.
    Would that wwork for you?

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Yes the suggestion of b/*[1]/name() worked beautifully for what I wanted to do.

    Matthias, thank you again for your help!

    Ben
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2011-01-25T22:31:26Z  
    Yes the suggestion of b/*[1]/name() worked beautifully for what I wanted to do.

    Matthias, thank you again for your help!

    Ben
    You're welcome.
    Glad to hear it solves your problem!


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • urielvedder
    urielvedder
    1 Post

    Re: XMLTABLE and returning name of child node?

    ‏2014-05-21T23:04:46Z  
    Hi Ben,

    the error that you got with the path expression 'b/*/name()' means that this expression has returned more than one value. But, the column expressions in XMLTABLE need to return single values, otherwise casting to the specified SQL data type is not possible.
    So, if 'b/*/name()' produces this error then it seems like the element b has more than one child element. Hence, you must narrow it down to one specific child element for which you want the name(). Either you narrow down by element name, as you did with the expression (c|e), or maybe by position. For example, the
    path b/*[1]/name() would always produce the name of the first child element of b.
    Would that wwork for you?

    Thanks,

    Matthias


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

    Hola Matthias,

    Tu respuesta fue de muchísima ayuda para mi desarrollo.

    Gracias!!!

  • mlabudovic
    mlabudovic
    3 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-20T14:05:33Z  

    Hi,
    when I tried to update table with XML column I got the error: SQL16003N  An expression of data type "( item(), item()+ )" cannot be used when the data type "VARCHAR_1000" is expected in the context. Error QName=err:XPTY0004.

    The problem is multi values in XML column, I don't know how to update data in XML column using the same command with multi and single value.

    The update is:

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with $z,
                  do replace value of $new/row/c2 with $z1,
                  do replace value of $new/row/c3 with $z2,
                  do replace value of $new/row/c5 with $z3,
                  for $x in $new/row/c5  return do replace value of $x with $z4
                 )
         return $new '
         passing  cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c1'  passing xmlrecord as "t")  as varchar(10))) as varchar(10)) as "z"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c2'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z1"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c3'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z2"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[1]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "z3"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5'  passing xmlrecord as "t")  as varchar(1000))) as varchar(1000)) as "z4"
         )
    where RECID in ('100045','322609');

     

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-21T03:48:43Z  

    Hi,
    when I tried to update table with XML column I got the error: SQL16003N  An expression of data type "( item(), item()+ )" cannot be used when the data type "VARCHAR_1000" is expected in the context. Error QName=err:XPTY0004.

    The problem is multi values in XML column, I don't know how to update data in XML column using the same command with multi and single value.

    The update is:

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with $z,
                  do replace value of $new/row/c2 with $z1,
                  do replace value of $new/row/c3 with $z2,
                  do replace value of $new/row/c5 with $z3,
                  for $x in $new/row/c5  return do replace value of $x with $z4
                 )
         return $new '
         passing  cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c1'  passing xmlrecord as "t")  as varchar(10))) as varchar(10)) as "z"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c2'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z1"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c3'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z2"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[1]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "z3"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5'  passing xmlrecord as "t")  as varchar(1000))) as varchar(1000)) as "z4"
         )
    where RECID in ('100045','322609');

     


    Hello,

    this is an interesting and tricky question. I want to help you solve this but it may require a few iterations to get to a good solution that meets your needs.

    Ok, lets see: since the error mentions the type VARCHAR_1000, the error must be triggered in this part of your SQL statement:

    XMLCAST(xmlquery('$t/row/c5'  passing xmlrecord as "t")  as varchar(1000) ... as z4

    The reason is that the expression $t/row/c5 produces more than one value.

    Further up in your SQL I see this line:

    for $x in $new/row/c5  return do replace value of $x with $z4

    I assume that you want to iterate over the values in the input sequence z4 ($t/row/c5) and use these values to update all of the existing occurrences of the element /row/c5.

    Is that correct?



    The big question is: do you know for sure that the input sequence $t/row/c5 has exactly as many items as there are existing c5 elements in the document that you are updating?

    What if the document in the table has three c5 elements but the input sequence has 2 or 4 values? So, I'm not 100% clear on the semantics of the update operation for such cases.

    Additionally, what does the function DB2ADMIN.dm_translate_support do?

    And, do you have any XML Schema information that would tell you the maxoccur of the element c5?


    For starters, here is a simple but very crude approach, and it is really only feasible if you know that the element c5 has only a limited number of occurrences, let's say less than 10:


    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with $z,
                  do replace value of $new/row/c2 with $z1,
                  do replace value of $new/row/c3 with $z2,
                  do replace value of $new/row/c5[1] with $c51,
                  do replace value of $new/row/c5[2] with $c52,
                  do replace value of $new/row/c5[3] with $c53,
                  do replace value of $new/row/c5[4] with $c54,
                  ...
                  do replace value of $new/row/c5[10] with $c510
                 )
         return $new '
         passing  cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c1'  passing xmlrecord as "t")  as varchar(10))) as varchar(10)) as "z"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c2'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z1"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c3'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z2"
         
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[1]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c51"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[2]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c52"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[3]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c53"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[4]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c54"
         ...
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[10]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c510"

         )
    where RECID in ('100045','322609');

     

    I know this is not pretty. I have some other ideas.  But, please let me know your thoughts on this and on all of the questions above before I go into the details of another possible solution.

    Thanks,

    Matthias

     

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-21T23:10:03Z  


    Hello,

    this is an interesting and tricky question. I want to help you solve this but it may require a few iterations to get to a good solution that meets your needs.

    Ok, lets see: since the error mentions the type VARCHAR_1000, the error must be triggered in this part of your SQL statement:

    XMLCAST(xmlquery('$t/row/c5'  passing xmlrecord as "t")  as varchar(1000) ... as z4

    The reason is that the expression $t/row/c5 produces more than one value.

    Further up in your SQL I see this line:

    for $x in $new/row/c5  return do replace value of $x with $z4

    I assume that you want to iterate over the values in the input sequence z4 ($t/row/c5) and use these values to update all of the existing occurrences of the element /row/c5.

    Is that correct?



    The big question is: do you know for sure that the input sequence $t/row/c5 has exactly as many items as there are existing c5 elements in the document that you are updating?

    What if the document in the table has three c5 elements but the input sequence has 2 or 4 values? So, I'm not 100% clear on the semantics of the update operation for such cases.

    Additionally, what does the function DB2ADMIN.dm_translate_support do?

    And, do you have any XML Schema information that would tell you the maxoccur of the element c5?


    For starters, here is a simple but very crude approach, and it is really only feasible if you know that the element c5 has only a limited number of occurrences, let's say less than 10:


    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with $z,
                  do replace value of $new/row/c2 with $z1,
                  do replace value of $new/row/c3 with $z2,
                  do replace value of $new/row/c5[1] with $c51,
                  do replace value of $new/row/c5[2] with $c52,
                  do replace value of $new/row/c5[3] with $c53,
                  do replace value of $new/row/c5[4] with $c54,
                  ...
                  do replace value of $new/row/c5[10] with $c510
                 )
         return $new '
         passing  cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c1'  passing xmlrecord as "t")  as varchar(10))) as varchar(10)) as "z"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c2'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z1"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c3'  passing xmlrecord as "t")  as varchar(60))) as varchar(60)) as "z2"
         
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[1]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c51"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[2]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c52"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[3]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c53"
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[4]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c54"
         ...
         ,cast(DB2ADMIN.dm_translate_support( XMLCAST(xmlquery('$t/row/c5[10]'  passing xmlrecord as "t")  as varchar(1000))) as varchar(60)) as "c510"

         )
    where RECID in ('100045','322609');

     

    I know this is not pretty. I have some other ideas.  But, please let me know your thoughts on this and on all of the questions above before I go into the details of another possible solution.

    Thanks,

    Matthias

     

    OK, I was already able to answer one of my questions, which was "do you know for sure that the input sequence $t/row/c5 has exactly as many items as there are existing c5 elements in the document that you are updating?"

    The answer is yes, because you are actually updating each selected document (xmlrecord) with translated values from the same document. In particular, you are updating each /row/c5 element with a modified value of itself. For some reason I had overlooked this.

    This opens up additional options for writing this update. Let me think about this...

    - Matthias

  • mlabudovic
    mlabudovic
    3 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-22T12:43:13Z  

    OK, I was already able to answer one of my questions, which was "do you know for sure that the input sequence $t/row/c5 has exactly as many items as there are existing c5 elements in the document that you are updating?"

    The answer is yes, because you are actually updating each selected document (xmlrecord) with translated values from the same document. In particular, you are updating each /row/c5 element with a modified value of itself. For some reason I had overlooked this.

    This opens up additional options for writing this update. Let me think about this...

    - Matthias

    Hi,

    The main reason for the update is that I need to encrypt production data on all test environments. DB2ADMIN.dm_translate_support
    function is used for the encryption. The table consists of two columns (VARCHAR and XML), it is Themenos (T24 application). The data is non-structured, there is no XMLSCHEMA. You're right, every element and every record has to be changed. Element c5 can have a single value, but may have more attributes: <c5>DATABASE</c5> or <c5>ADRRESS BB</c5><c5 m="2">
    Number of attributes is not fixed.

    The DB2ADMIN.dm_translate_support is:

    CREATE FUNCTION DB2ADMIN.DM_TRANSLATE_SUPPORT ( P_COLUMN VARCHAR(1000) )
      RETURNS VARCHAR(1000)
      SPECIFIC DM_TRANSLATE_SUPPORT
      LANGUAGE SQL
      INHERIT SPECIAL REGISTERS
    RETURN
    SELECT TRANSLATE ( P_COLUMN, 'ZYXWUTSRQPONMLKJIHGFEDCBAzyxwutsrqponmlkjihgfedcba9876543210','ABCDEFGHIJKLMNOPQRSTUWXYZabcdefghijklmnopqrstuwxyz0123456789') FROM SYSIBM.SYSDUMMY1;
     

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-25T19:16:06Z  

    Hi,

    The main reason for the update is that I need to encrypt production data on all test environments. DB2ADMIN.dm_translate_support
    function is used for the encryption. The table consists of two columns (VARCHAR and XML), it is Themenos (T24 application). The data is non-structured, there is no XMLSCHEMA. You're right, every element and every record has to be changed. Element c5 can have a single value, but may have more attributes: <c5>DATABASE</c5> or <c5>ADRRESS BB</c5><c5 m="2">
    Number of attributes is not fixed.

    The DB2ADMIN.dm_translate_support is:

    CREATE FUNCTION DB2ADMIN.DM_TRANSLATE_SUPPORT ( P_COLUMN VARCHAR(1000) )
      RETURNS VARCHAR(1000)
      SPECIFIC DM_TRANSLATE_SUPPORT
      LANGUAGE SQL
      INHERIT SPECIAL REGISTERS
    RETURN
    SELECT TRANSLATE ( P_COLUMN, 'ZYXWUTSRQPONMLKJIHGFEDCBAzyxwutsrqponmlkjihgfedcba9876543210','ABCDEFGHIJKLMNOPQRSTUWXYZabcdefghijklmnopqrstuwxyz0123456789') FROM SYSIBM.SYSDUMMY1;
     

    Yes, this example looked a lot like T24, so I suspected it's coming from Temenos :-).

    One option is to use the XQuery function fn:translate instead of the SQL function translate.  For example, like this:

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with fn:translate($new/row/c1, ...  ,  ...),
                  do replace value of $new/row/c2 with
    fn:translate($new/row/c2, ...  ,  ...),
                  do replace value of $new/row/c3 with
    fn:translate($new/row/c3, ...  ,  ...),
                  for $x in $new/row/c5
                 
    return do replace value of $x with fn:translate($x,  ...  ,  ...)             )
         return $new ' )

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.xml.doc/doc/xqrfntrn.html?lang=en

    In this simple example, you would have to specify the 2nd and 3rd parameter for the translate function over and over again, which is kinda ugly.

    There are various ways in which this can be improved. For example, I think the following should work:

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with fn:translate($new/row/c1, $s1 ,  $s2 ),
                  do replace value of $new/row/c2 with
    fn:translate($new/row/c2, $s1 ,  $s2),
                  do replace value of $new/row/c3 with
    fn:translate($new/row/c3, $s1 ,  $s2),
                  for $x in $new/row/c5
                 
    return do replace value of $x with fn:translate($x, $s1 ,  $s2)             )
         return $new ',

         PASSING 'ZYXWUTSRQPONMLKJIHGFEDCBAzyxwutsrqponmlkjihgfedcba9876543210' as "s1",
                          'ABCDEFGHIJKLMNOPQRSTUWXYZabcdefghijklmnopqrstuwxyz0123456789' as "s2")

    What do you think? Would this work for you.

    Matthias

  • mlabudovic
    mlabudovic
    3 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-26T21:03:44Z  

    Yes, this example looked a lot like T24, so I suspected it's coming from Temenos :-).

    One option is to use the XQuery function fn:translate instead of the SQL function translate.  For example, like this:

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with fn:translate($new/row/c1, ...  ,  ...),
                  do replace value of $new/row/c2 with
    fn:translate($new/row/c2, ...  ,  ...),
                  do replace value of $new/row/c3 with
    fn:translate($new/row/c3, ...  ,  ...),
                  for $x in $new/row/c5
                 
    return do replace value of $x with fn:translate($x,  ...  ,  ...)             )
         return $new ' )

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.xml.doc/doc/xqrfntrn.html?lang=en

    In this simple example, you would have to specify the 2nd and 3rd parameter for the translate function over and over again, which is kinda ugly.

    There are various ways in which this can be improved. For example, I think the following should work:

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with fn:translate($new/row/c1, $s1 ,  $s2 ),
                  do replace value of $new/row/c2 with
    fn:translate($new/row/c2, $s1 ,  $s2),
                  do replace value of $new/row/c3 with
    fn:translate($new/row/c3, $s1 ,  $s2),
                  for $x in $new/row/c5
                 
    return do replace value of $x with fn:translate($x, $s1 ,  $s2)             )
         return $new ',

         PASSING 'ZYXWUTSRQPONMLKJIHGFEDCBAzyxwutsrqponmlkjihgfedcba9876543210' as "s1",
                          'ABCDEFGHIJKLMNOPQRSTUWXYZabcdefghijklmnopqrstuwxyz0123456789' as "s2")

    What do you think? Would this work for you.

    Matthias

    I would like to thank you for you help and support, UPDATE statement works as expected now.


    I  believe that better to have our own SQL function (DM_TRANSLATE_SUPPORT) for data encryption, because our business department often change rules for masking data. In such business environment is more flexible to have SQL function - if algoritam will change we need to change only SQL function. So, it would be nice if UPDATE will work with SQL function.
    If you have some suggestions or concerns please let me know.

    Thanks in advance

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: XMLTABLE and returning name of child node?

    ‏2014-08-29T02:02:09Z  

    I would like to thank you for you help and support, UPDATE statement works as expected now.


    I  believe that better to have our own SQL function (DM_TRANSLATE_SUPPORT) for data encryption, because our business department often change rules for masking data. In such business environment is more flexible to have SQL function - if algoritam will change we need to change only SQL function. So, it would be nice if UPDATE will work with SQL function.
    If you have some suggestions or concerns please let me know.

    Thanks in advance

    Ok, I have a couple of ideas for how you can make things more modular. I didn't have a chance yet to test them, but you can play with the ideas and see if you can make it work.

    Idea 1: Use global variables:

    CREATE VARIABLE myvars.str1 = 'ZYXWUTSRQPONMLKJIHGFEDCBAzyxwutsrqponmlkjihgfedcba9876543210';

    CREATE VARIABLE myvars.str2 = 'ABCDEFGHIJKLMNOPQRSTUWXYZabcdefghijklmnopqrstuwxyz0123456789';

     

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1 with fn:translate($new/row/c1, $s1 ,  $s2 ),
                  do replace value of $new/row/c2 with
    fn:translate($new/row/c2, $s1 ,  $s2),
                  do replace value of $new/row/c3 with
    fn:translate($new/row/c3, $s1 ,  $s2),
                  for $x in $new/row/c5
                 
    return do replace value of $x with fn:translate($x, $s1 ,  $s2)             )
         return $new ',

         PASSING myvars.str1 as "s1",
                          myvars.str2 as "s2"
    )

     

    Idea 2: Call your SQL UDF from within the XQuery update expression.

    This one is a little bit trickier, but I know it can be done. The key thing is to use the db2-fn:sqlquery function to embed a SQL statement with your user-defined function in the Xquery expression. The result of the SQL UDF needs to be converted back into an XML text node. And the UDF needs to receive a parameter from the outer XQuery, which is achieved by the "parameter(1)" mechanism.

     

    UPDATE  DB2ADMIN.DM_FBNK_CUSTOMER_SOURCE
    set xmlrecord = xmlquery('
         copy $new := $XMLRECORD
         modify ( do replace value of $new/row/c1
    with db2-fn:sqlquery("
                   VALUES XMLTEXT (DB2ADMIN.DM_TRANSLATE_SUPPORT (
                                                         CAST(parameter(1) AS VARCHAR(1000)  )))"   ,
                     $new/row/c1 )
    ,

                  do replace value of $new/row/c2 with
    db2-fn:sqlquery("
                   VALUES XMLTEXT (DB2ADMIN.DM_TRANSLATE_SUPPORT (
                                                         CAST(parameter(1) AS VARCHAR(1000)  )))"   ,
                     $new/row/c2 )
    ,

                  do replace value of $new/row/c3 with db2-fn:sqlquery("
                   VALUES XMLTEXT (DB2ADMIN.DM_TRANSLATE_SUPPORT (
                                                         CAST(parameter(1) AS VARCHAR(1000)  )))"   ,
                     $new/row/c3 )
    ,

                  for $x in $new/row/c5
                 
    return do replace value of $x with db2-fn:sqlquery("
                   VALUES XMLTEXT (DB2ADMIN.DM_TRANSLATE_SUPPORT (
                                                         CAST(parameter(1) AS VARCHAR(1000)  )))"   ,
                             $x )
                 )
         return $new ');

     

    I'm attaching a screenshot of another example that I found.

    Maybe these ideas can help you.

    - Matthias

     

     

    Attachments