Generating XML from self referencing Oracle table

I have an Oracle DB with a self referencing table of hierarchical medical data called "services".

Services

service_id ---------- number

service_name ----- varchar2(200)

service_parent --- number

where each service can have 1 parent service as well as n number of children. "Top level" services would have a service_parent value of zero.

I need to generate an XML structure from this data. I've scoured the web but cannot seem to find an example of this sort of XML generation. This is an example of the XML output I need:

<services>
    <service id="1" name="Medicine">
        <service id="10" name="Anesthesia">

        </service>
    </service>
    <service id="2" name="Surgery">
        <service id="3" name="Cardiology">
            <service id="4" name="Bypass">

            </service>
            <service id="5" name="Transplant">

            </service>
            <service id="6" name="Valve Replacement">

            </service>
        </service>
        <service id="9" name="Ear Nose Throat">
            <service id="7" name="Laryngectomy">

            </service>
            <service id="8" name="Septoplasty">

            </service>
        </service>
    </service>  
</services>

I've tried using CO开发者_StackOverflow中文版NNECT_BY_ISLEAF, CONNECT_BY_PATH, CONNECT BY PRIOR, etc... but the closest I could get it was:

<services>
<service id="1" name="Medicine" />
<service id="10" name="Anesthesia" />
<service id="2" name="Surgery" />
<service id="3" name="Cardiology" />
<service id="4" name="Bypass" />
<service id="5" name="Transplant" />
<service id="6" name="Valve Replacement" />
<service id="9" name="Ear Nose Throat" />
<service id="8" name="Laryngectomy" />
<service id="7" name="Septoplasty" />
</services>

What is the best way to get the XML structure above?

This example using DBMS_XMLGEN.newContextFromHierarchy looks pretty similar to what you're trying to do.