Cursor loops; How to perform something at start/end of loop 1 time only?

Question

I've got the following in one of my Oracle procedures, I'm using it to generate XML

  -- v_client_addons is set to '' to avoid null error
  OPEN C_CLIENT_ADDONS;
  LOOP
    FETCH C_CLIENT_ADDONS INTO CLIENT_ADDONS;
    EXIT WHEN C_CLIENT_ADDONS%NOTFOUND;
    BEGIN
      v_client_addons := v_client_addons || CLIENT_ADDONS.XML_DATA;
    END;
  END LOOP;
  CLOSE C_CLIENT_ADDONS;

  -- Do something later with v_client_addons

The loop should go through my cursor and pick out all of the XML values to display, such as :

<add-on name="some addon"/>
<add-on name="another addon"/>

What I would like to achieve is to have an XML start/end tag inside this loop, so I would have the following output

<addons>
   <add-on name="some addon"/>
   <add-on name="another addon"/>
</addons>

How can I do this without having the <addons> tag after every line? If there are no addons in the cursor (cursor is empty), then I would like to skip this part enitrely

Answer 1

check the length of v_client_addons. If it is greater than 0, you actually appened something. Then create you parent tag with its children, else just ignore it.

Answer 2

How about using SQL to generate the entire XML, instead of looping over a cursor?

SELECT XMLELEMENT("addons", XMLAGG(C.XML_DATA)) INTO v_client_addons 
FROM CLIENT_ADDON_TABLE C;