Topic
  • 3 replies
  • Latest Post - ‏2013-08-07T09:23:49Z by Tilak85
Tilak85
Tilak85
4 Posts

Pinned topic Merge Command - updating a specific xml node

‏2013-08-06T15:35:25Z |

Hi,

We're trying to run below Merge Query

MERGE INTO CUST_PROFILE ASA

USING (SELECT 11118 AS ID from SYSIBM.SYSDUMMY1) AS B

ON (A.ID = 11118 AND A.MKT = '999')

 WHEN MATCHED THEN

UPDATE SETXML_TX =

XMLQUERY(

'transform copy $newDoc := $CustXml

modify

if ($newDoc/Cust/addr) then

do replace $newDoc/Cust/addr

with $ADXml

else

 

do insert $ADXml

as last into $newDoc/Cust

return $newDoc'

PASSING A.XML_TX AS "CustXml",

XMLPARSE(DOCUMENT('<Cust><addr>1</addr></Cust>')) AS "ADXml"

)

WHEN NOT MATCHED THEN

INSERT (ID, MKT, XML_TX)

VALUES (11118, '999', '<Cust><addr></addr></Cust>')

It's successfully updating a row, but when tried to Insert a new row, then giving the below error

An assigned value in the copy clause of a transform expression is not a sequence with exactly one item that is a node. Error QName=err:XUTY0013.. SQLCODE=-16084, SQLSTATE=10705, DRIVER=4.11.69.

For the first time, when there is NO record in the table, it has to INSERT(i.e., WHEN NOT MATCHED THEN will be become true), but looks like the control is going to UPADTE section(i.e., WHEN MATCHED THEN), so when tries to update it's facing the Empty sequence issue), but ideally it should go to WHEN NOT MATCHED THEN section

Thanks.

 

  • anost
    anost
    13 Posts

    Re: Merge Command - updating a specific xml node

    ‏2013-08-06T20:23:32Z  

    Hi,

    I assume the trouble comes from this line: XMLPARSE(DOCUMENT('<Cust><addr>1</addr></Cust>')) AS "ADXml"

    Please try it by  removing <Cust> and just with XMLPARSE(DOCUMENT('<addr>1</addr>')) AS "ADXml"

    The error message idicates that just one node should be used.

    With your ADXml the replace/insert adds an additional "Cust" node and would result in  $newDoc/Cust/Cust/addr.

     Best,

    Andreas

     

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Merge Command - updating a specific xml node

    ‏2013-08-07T04:22:57Z  

    Hi Tilak,

    I think there are two problems.

    The first problem is the one that Andreas has pointed out. Your transform expression would insert a new "Cust" element into the existing "Cust" element, which does not seem to be you intention here.

    Another possible way to fix this is to explicitly navigate to the new "addr" element, like this:

                 if ($newDoc/Cust/addr) then
                    do replace $newDoc/Cust/addr  with $ADXml/Cust/addr
                 else
                    do insert $ADXml/Cust/addr as last into $newDoc/Cust

    However, I don't think that you are hitting this problem just yet.

    When the table is empty then your MERGE statement should not go into the WHEN MATCHED branch. But, for some reason it does. As a result, the XML update is executed on an empty table, and hence the clause PASSING A.XML_TX AS "CustXml" does not provide any XML document to work on, and that is causing error SQL16084N.

    So the second and foremost problem is: why does the MERGE go into the WHEN MATCHED branch even though there is no row to match? I'm wondering if this is a bug in DB2.

    I have simplified your MERGE statement as follows and it works correctly:

    MERGE INTO CUST_PROFILE AS A
    USING (SELECT 11118 AS ID from SYSIBM.SYSDUMMY1) AS B
    ON (A.ID = 11118 AND A.MKT = '999')
     WHEN MATCHED THEN
           UPDATE SET XML_TX = XMLPARSE(DOCUMENT('<Cust><addr>12345</addr></Cust>'))         
     WHEN NOT MATCHED THEN
         INSERT (ID, MKT, XML_TX)
         VALUES (11118, '999', '<Cust><addr></addr></Cust>');

        
     

    Note that I did not change the matching condition of the MERGE. I only removed the XML transform expression. I'm wondering if the XML transform expression causes DB2 to compile the statement incirrectly.

    Could you open a PMR for this problem so that IBM support can investigate?

    Thanks,

    Matthias

     

  • Tilak85
    Tilak85
    4 Posts

    Re: Merge Command - updating a specific xml node

    ‏2013-08-07T09:23:49Z  

    Hi Tilak,

    I think there are two problems.

    The first problem is the one that Andreas has pointed out. Your transform expression would insert a new "Cust" element into the existing "Cust" element, which does not seem to be you intention here.

    Another possible way to fix this is to explicitly navigate to the new "addr" element, like this:

                 if ($newDoc/Cust/addr) then
                    do replace $newDoc/Cust/addr  with $ADXml/Cust/addr
                 else
                    do insert $ADXml/Cust/addr as last into $newDoc/Cust

    However, I don't think that you are hitting this problem just yet.

    When the table is empty then your MERGE statement should not go into the WHEN MATCHED branch. But, for some reason it does. As a result, the XML update is executed on an empty table, and hence the clause PASSING A.XML_TX AS "CustXml" does not provide any XML document to work on, and that is causing error SQL16084N.

    So the second and foremost problem is: why does the MERGE go into the WHEN MATCHED branch even though there is no row to match? I'm wondering if this is a bug in DB2.

    I have simplified your MERGE statement as follows and it works correctly:

    MERGE INTO CUST_PROFILE AS A
    USING (SELECT 11118 AS ID from SYSIBM.SYSDUMMY1) AS B
    ON (A.ID = 11118 AND A.MKT = '999')
     WHEN MATCHED THEN
           UPDATE SET XML_TX = XMLPARSE(DOCUMENT('<Cust><addr>12345</addr></Cust>'))         
     WHEN NOT MATCHED THEN
         INSERT (ID, MKT, XML_TX)
         VALUES (11118, '999', '<Cust><addr></addr></Cust>');

        
     

    Note that I did not change the matching condition of the MERGE. I only removed the XML transform expression. I'm wondering if the XML transform expression causes DB2 to compile the statement incirrectly.

    Could you open a PMR for this problem so that IBM support can investigate?

    Thanks,

    Matthias

     

    Thanks Andreas and Matthias.

    Yes, the actual problem here is MERGE is going into WHEN MATCHED branch even when the condition is not matching, and this happens when we use XPATH Transform expression.

    Okay; I will open a PMR for this, but could you let me know how to open a PMR? that would help me.

    May be i will try using Separate INSERT and UPDATE SQLs  and see if it works for me.

    Thanks again.

    Tilak