How to put namespace reference in Xquery (oracle, pl/sql)-CodePudding

I have a problem with my "namespace" space :-) - I dont know how tu put reference in xquery part of the select statement to add node to xml (xmltype variable):

DECLARE
    xmldoc XMLTYPE;
    vQuery CLOB;
BEGIN
    SELECT xmltype ('<envelope xmlns="hal:icl:01">
                      <sender>
                        <name>name</name>
                        <country>CT</country>
                        <address>first part</address>
                        <address>second part</address>
                        <sender_identifier>abc1</sender_identifier>
                        <sender_eddress>
                          <sender_agent>y1</sender_agent>
                          <sender_mailbox>code</sender_mailbox>
                        </sender_eddress>
                      </sender>
                    </envelope>') INTO xmldoc FROM DUAL;

    vQuery := ' copy $tmp := $src modify (insert node $Additional as last into $tmp/envelope) return $tmp';

    SELECT XMLQUERY (vQuery PASSING xmldoc AS "src", XMLTYPE ('<cbs_extra><emailId></emailId><organizationUnit></organizationUnit></cbs_extra>') AS "Additional" RETURNING CONTENT) INTO xmldoc FROM DUAL;


    DBMS_OUTPUT.put_line (XMLDOC.getclobval ());
END;

Anyone could give me an input?

Regards, E.

CodePudding user response：

You need to declare a default element namespace:

    vQuery := 'declare default element namespace "hal:icl:01";
      copy $tmp := $src modify (insert node $Additional as last into $tmp/envelope) return $tmp';

db<>fiddle

As @LukeWoodward pointed out, you current additional XML will override that with an empty namespace, so you might want to include the same one in that:

XMLTYPE ('<cbs_extra xmlns="hal:icl:01"><emailId></emailId><organizationUnit></organizationUnit></cbs_extra>') AS "Additional"

db<>fiddle

The namespace is duplicated in the final XML with this method but that shouldn’t matter.