cte that creates nested html lists

I have the following cte:

WITH cte AS
(
        SELECT 
            c.ParentIDNo,  
            c.Category,
            c.ChildCategory,
            c.WSWebProductNameIDNo, 
            c.IDNo, 
            0 AS Level,
            CAST('/' + c.Category AS VARCHAR(1000)) as CteName
        FROM WSWebCategory as c
        WHERE c.ParentIDNo IS NULL

        UNION ALL

        SELECT   
            t.ParentIDNo, 
            t.Category,
            t.ChildCategory, 
            t.WSWebProductNameIDNo,
            t.IDNo, 
            cte.Level + 1 AS Level,
            CAST(cte.CteName + '/' + t.ChildCategory AS VARCHAR(1000)) AS CteName
        FROM WSWebCategory t
        INNER JOIN cte ON t.ParentIDNo = cte.IDNo
    )
    SELECT *, REPLICATE('----', Level) + CteName as CteName FROM cte
    ORDER BY cte.CteName

which gives me a list like:

    /Apparatus
    ----/Apparatus/Autoclaves
    --------/Apparatus/Autoclaves/ALL-AMERICAN Portable Sterilizers (WAFCO)
    --------/Apparatus/Autoclaves/Autoclave Accessories
    ------------/Apparatus/Autoclaves/Autoclave Accessories/Clavies® Autoclave Gloves (Bel-Art Scienceware)
    ------------/Apparatus/Autoclaves/Autoclave Accessories/Grabbit™ Temp Mitts (Heathrow Scientific)
    ------------/Apparatus/Autoclaves/Autoclave Accessories/Odo-Clave® Deodorant Pads (Bel-Art Scienceware)
    --------/Apparatus/Autoclaves/Autoclave Bags
    ------------/Apparatus/Autoclaves/Autoclave Bags/Autoclavable Biohazard Disposal Bags (Bel-Art Scienceware)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Autoclavable Biohazard Disposal Bags (Gosselin)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Autoclavable Hi-Temp Biohazard Disposal Bags (Bel-Art Scienceware)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Chex-All® II Sterilization Pouches (Propper)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Clavies® Autoclave Gloves (Bel-Art Scienceware)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Clavies® Bag Holder (Bel-Art Scienceware)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Clavies® General Purpose Autoclavable Bags (Bel-Art Scienceware)
    ------------/Apparatus/Autoclaves/Autoclave Bags/Poxygrid® Biohazard Bag Holder (Bel-Art Scienceware)

but what I'm after is a set of nested html lists:

<ul> 
<li>Apparatus
   <ul> 
    <li>Autoclaves
        <ul>   
            <li>ALL-AMERICAN Portable Sterilizers (WAFCO)</li> 
            <li>Autoclave Accessories
                <ul>
                    <li>Clavies® Autoclave Gloves (Bel-Art Scienceware)</li>
                    <li>Grabbit™ Temp Mitts (Heathrow Scientific)</li>
                    <li>Odo-Clave® Deodorant Pads (Bel-Art Scienceware) </li>
                </ul>
            </li>
            <li>Autoclave Bags
                <ul>
                    <li>Autoclavable Biohazard Disposal Bags (Bel-Art Scienceware)</li>
                    <li>Autoclavable Biohazard Disposal Bags (Gosselin)</li>
                    <li>A开发者_运维知识库utoclavable Hi-Temp Biohazard Disposal Bags (Bel-Art Scienceware)</li>
                    <li>Chex-All® II Sterilization Pouches (Propper)</li>
                    <li>Clavies® Autoclave Gloves (Bel-Art Scienceware)</li>
                    <li>Clavies® Bag Holder (Bel-Art Scienceware)</li>
                    <li>Clavies® General Purpose Autoclavable Bags (Bel-Art Scienceware)</li>
                    <li>Poxygrid® Biohazard Bag Holder (Bel-Art Scienceware)</li>
                </ul>
            </li>
        </ul>
    </li> 
    </ul>
</li>
</ul> 

I'm stumped as how to do this!

Is there some reason you can't just parse it on the backside using whatever your higher-level programming language is?

It would appear you data is an excellent candidate for XML output (using SELECT ... FOR XML EXPLICIT.) Your data seems set up perfectly for that, which would be easily parsed by something on your application side.

Outside of that, you could likely get the list item tags added using concatenation of the results and stripping the tag in the next CTE loop before you read it on the extended text. The closing tags are going to be the challenge though, as I don't believe CTE's step back out in a recursive way to allow you to close them out.

Four years later...

Pseudocode:

previousLevel = -1;

for each item in items

  if item.level > previousLevel
    print "<ul>"

  if item.level < previousLevel
    print repeat("</ul>", previousLevel - item.level)

  print "<li>" + item.text + "</li>"

  previousLevel = item.level

end for

print repeat("</ul>", previousLevel + 1)

JSP / JSTL:

<c:set var="previousLevel" value="-1"/>

<c:forEach items="${items}" var="item">

    <c:if test="${ item.level > previousLevel }">
        <ul>
    </c:if>

    <c:if test="${ item.level < prevLevel }">
        ${xfn:repeat("</ul>", previousLevel - item.level)}
    </c:if>

    <li>${item.text}</li>

    <c:set var="previousLevel" value="${item.level}"/>

</c:forEach>

<c:if test="${previousLevel > -1}">
    ${xfn:repeat("</ul>",  previousLevel + 1 )}
</c:if>

Where xfn:repeat is a tld function for any String repeat function