SQL Server query returning Xml and Html

Question

We are migrating an app from oracle to SQL Server.

In Oracle a procedure used to return XML with some HTML tags embedded.

The source is below.

    SELECT XMLAGG (
               XMLFOREST (
                  XMLELEMENT ("a", XMLATTRIBUTES ('#' AS "href"), MODULENAME) "h3",
                  XMLELEMENT (
                     "ul",
                     XMLAGG (
                        XMLELEMENT (
                           "li",
                           XMLELEMENT (
                              "a",
                              XMLATTRIBUTES (
                                 '#' AS "href",
                                    'pageclick('''
                                 || SCREENPATH                                                     
                                 || ''','|| SCREENID||')' AS "onclick"),
                              SCREENNAME))
                           ORDER BY SORDER ASC)) "div")
                  ORDER BY MORDER ASC).getclobval ()

       FROM (SELECT B.SUBMODULEID MODULEID,
                    C.PAGEID SCREENID,
                    C.PAGENAME SCREENNAME,
                    C.PAGECODE,
                    B.SUBMODULEID,
                    B.SUBMODULENAME MODULENAME,
                    C.PAGEURLL1 SCREENPATH,
                    C.ORDERNO SORDER,
                    B.ORDERNO MORDER
               FROM SETP.SM_PAGES C,
                    SETP.SM_MODULES A,
                    SETP.SM_SUB_MODULES B,
                    SETP.SM_USRPRIVHDR D,
                    SETP.SM_USRPRIVDTL E
              WHERE     D.USRPRIVID = E.USRPRIVID
                    AND C.PAGEID = E.PAGEID
                    AND B.MODULEID = A.MODULEID
                    AND C.SUBMODULEID = B.SUBMODULEID
                    AND D.USRID = 2) page
   GROUP BY MODULENAME, MORDER;

and here is the output

<h3>
  <a href="#">Masters</a>
</h3>
<div>
    <ul>

         <li>
          <a href="#" onclick="pageclick(&apos;WmsSetup.aspx/BaggingConfig&apos;,1177)">Bagging Configuration</a>
        </li>
        <li>
          <a href="#" onclick="pageclick(&apos;WMS.aspx/Items&apos;,1171)">Item Master</a>
        </li>

    </ul>
</div>

I have been splitting my hair to achieve the same in SQL Server without using XSLT. Any ideas ?

Thanks & Regards

Answer 1

This looks like it should work for you.

;with C as
(

  -- Your derived table goes here
  SELECT B.SUBMODULEID MODULEID,
         C.PAGEID SCREENID,
         .
         .
  FROM SETP.SM_PAGES C,  
         .
         .

)
select '#' as "h3/a/@href",
       MODULENAME as "h3/a",
       (
         select '#' as "a/@href",
                'pageclick('''+SCREENPATH+''','+SCREENID+')' as "a/@onclick",
                SCREENNAME as "a"
         from C as C2
         where C1.MODULENAME = C2.MODULENAME and
               C1.MORDER = C2.MORDER
        for xml path('li'), root('ul'), type
       ) as "div"
from C as C1
group by MODULENAME, MORDER
for xml path('')

I'm using a common table expression (CTE) because I need to reuse the derived table to build the inner parts of the XML.

Here is a working sample with table variable instead of your sub query.

declare @T table
(
  MODULEID int,
  SCREENID varchar(10),
  SCREENNAME varchar(35),
  PAGECODE varchar(10),
  SUBMODULEID int,
  MODULENAME varchar(10),
  SCREENPATH varchar(35),
  SORDER int,
  MORDER int
)

insert into @T values
(1, '1177', 'Bagging Configuration', 'page', 3, 'Masters', 'WmsSetup.aspx/BaggingConfig', 4, 5)
insert into @T values
(1, '1171', 'Item Master', 'page', 3, 'Masters', 'WMS.aspx/Items', 4, 5)

;with C as
(
  select *
  from @T
)
select '#' as "h3/a/@href",
       MODULENAME as "h3/a",
       (
         select '#' as "a/@href",
                'pageclick('''+SCREENPATH+''','+SCREENID+')' as "a/@onclick",
                SCREENNAME as "a"
         from C as C2
         where C1.MODULENAME = C2.MODULENAME and
               C1.MORDER = C2.MORDER
        for xml path('li'), root('ul'), type
       ) as "div"
from C as C1
group by MODULENAME, MORDER
for xml path('')

Result:

<h3>
  <a href="#">Masters</a>
</h3>
<div>
  <ul>
    <li>
      <a href="#" onclick="pageclick('WmsSetup.aspx/BaggingConfig',1177)">Bagging Configuration</a>
    </li>
    <li>
      <a href="#" onclick="pageclick('WMS.aspx/Items',1171)">Item Master</a>
    </li>
  </ul>
</div>