Topic
  • 4 replies
  • Latest Post - ‏2012-06-21T03:36:07Z by SystemAdmin
SystemAdmin
SystemAdmin
230 Posts

Pinned topic Rename element nodes

‏2012-06-20T18:44:23Z |
Trying rename an element which is repeated in the attached XML document. The query complete successfully but does not update the element nodes. Any help is appreciated.

UPDATE LLMAPGIS.GIS_USER_DATA
SET THE_DATA = XMLQUERY('declare default element namespace "http://www.w3.org/2001/XMLSchema-instance";
copy $new := $THE_DATA
modify
for $j in $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM
return
do rename $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM as "AvgGTM2011"
return $new
')
where LLMAPGIS.GIS_USER_DATA.USER_ID = 'b804488'
AND LLMAPGIS.GIS_USER_DATA.DATA_SRC_NM = '2012-05-22-NETWORK-PLAN-MASTER'
Updated on 2012-06-21T03:36:07Z at 2012-06-21T03:36:07Z by SystemAdmin
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Rename element nodes

    ‏2012-06-20T20:01:48Z  
    Hi Sandy,

    from first sight I would say that the namespace declaration in the UPDATE does not match the namespace in the document.

    The document declares the namespace prefix "xsi" but this prefix is never used in the document. Hence, all the elements in the document have no namespace. Or you could say, they have an empty namespace.

    Your UPDATE declares "http://www.w3.org/2001/XMLSchema-instance" as a default(!) namespace which basically means that all(!) elements are expected to be in that namespace. But, the document does not contain any elements in that namespace.

    Suggestion: remove the following line from your update and try again.

    
    declare 
    
    default element namespace 
    "http://www.w3.org/2001/XMLSchema-instance";
    


    Does that solve the problem?

    Matthias

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

    Re: Rename element nodes

    ‏2012-06-20T20:26:28Z  
    Thanks Matthias,

    If I remove the namespace declaration, the query fails with SQLCODE=-16085. The for loop should iterate over each <Row> element and return only the element I'm trying to rename. I don't understand why it now fails with -16085.

    UPDATE LLMAPGIS.GIS_USER_DATA
    SET THE_DATA = XMLQUERY('
    copy $new := $THE_DATA
    modify
    for $j in $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM
    return do rename $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM as "AvgGTM2011"
    return $new
    ')
    where LLMAPGIS.GIS_USER_DATA.USER_ID = 'b804488'
    AND LLMAPGIS.GIS_USER_DATA.DATA_SRC_NM = '2012-05-22-NETWORK-PLAN-MASTER'

    The target node of an XQuery "rename" expression is not valid. Error QName=err: "XUTY0012".. SQLCODE=-16085, SQLSTATE=10703, DRIVER=4.13.111
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Rename element nodes

    ‏2012-06-20T21:50:39Z  
    Thanks Matthias,

    If I remove the namespace declaration, the query fails with SQLCODE=-16085. The for loop should iterate over each <Row> element and return only the element I'm trying to rename. I don't understand why it now fails with -16085.

    UPDATE LLMAPGIS.GIS_USER_DATA
    SET THE_DATA = XMLQUERY('
    copy $new := $THE_DATA
    modify
    for $j in $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM
    return do rename $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM as "AvgGTM2011"
    return $new
    ')
    where LLMAPGIS.GIS_USER_DATA.USER_ID = 'b804488'
    AND LLMAPGIS.GIS_USER_DATA.DATA_SRC_NM = '2012-05-22-NETWORK-PLAN-MASTER'

    The target node of an XQuery "rename" expression is not valid. Error QName=err: "XUTY0012".. SQLCODE=-16085, SQLSTATE=10703, DRIVER=4.13.111
    Yes, for loop should iterate over -and return only- the element that you want to rename. For that to work, the body of the loop should use the $j from the "for" clause, like this:

    UPDATE LLMAPGIS.GIS_USER_DATA
    SET THE_DATA = XMLQUERY('
    copy $new := $THE_DATA
    modify
    for $j in $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM
    return do rename $j as "AvgGTM2011"
    return $new
    ')
    where LLMAPGIS.GIS_USER_DATA.USER_ID = 'b804488'
    AND LLMAPGIS.GIS_USER_DATA.DATA_SRC_NM = '2012-05-22-NETWORK-PLAN-MASTER'
    Sorry, I previously didn't notice this was another problem in addition to the namespace issue. I hope this will now work. Let me know if not and we'll dig deeper.

    Matthias


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

    Re: Rename element nodes

    ‏2012-06-21T03:36:07Z  
    Yes, for loop should iterate over -and return only- the element that you want to rename. For that to work, the body of the loop should use the $j from the "for" clause, like this:

    UPDATE LLMAPGIS.GIS_USER_DATA
    SET THE_DATA = XMLQUERY('
    copy $new := $THE_DATA
    modify
    for $j in $new/NETPLAN_Lines/Row/Avg_GTMs___Mile_2011__MM
    return do rename $j as "AvgGTM2011"
    return $new
    ')
    where LLMAPGIS.GIS_USER_DATA.USER_ID = 'b804488'
    AND LLMAPGIS.GIS_USER_DATA.DATA_SRC_NM = '2012-05-22-NETWORK-PLAN-MASTER'
    Sorry, I previously didn't notice this was another problem in addition to the namespace issue. I hope this will now work. Let me know if not and we'll dig deeper.

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    That change worked! I can't believe I missed that! Tired eyes I guess.

    Thank you very much Matthias for your help and rapid response!

    Sandy