Return xml data (element based) from stored procedure SQL Server 2000 - help needed

I am trying to retrieve simple data from 2 related tables in SQL.  I want the data in XML format without using the FOR XML clause.

I would like the data to be in this format

<module>
    <id>1</id>
    <name>module name</name>
    <desc>blah</desc>
    etc..
</module>
<module>
    <id>2</id>
    <name>module name 2</name>
    <desc>blah 2</desc>
    etc..
</module>

I have this stored proc but iot doesn't return correct xml format

ALTER PROCEDURE dbo.elearning_get_modules
      @userId int
AS
      SET NOCOUNT ON

      SELECT       DISTINCT
                  1 AS Tag,
                  NULL AS Parent,                  
                  EXM.id AS [module!1!id],
                  NULL AS [module!2!moduleName],
                  NULL AS [module!2!description],
                  NULL AS [module!2!url],
                  NULL AS [module!2!basis],
                  NULL AS [module!2!nroso],
                  NULL AS [module!2!dateCompleted]
      FROM dbo.elearning_xref_modules EXM
      LEFT OUTER JOIN elearning_user_completed_modules EUCM ON EXM.id = EUCM.ModuleId
      AND EUCM.UserId = @UserId
      
      UNION ALL
      
      SELECT       DISTINCT
                  2 AS Tag,
                  1 AS Parent,                  
                  EXM.id AS [module!1!id],
                  EXM.moduleName AS [module!2!moduleName],
                  EXM.description AS [module!2!description],
                  EXM.url AS [module!2!url],
                  EXM.basis AS [module!2!basis],
                  EXM.nroso AS [module!2!nroso],
                  EUCM.dateCompleted AS [module!2!dateCompleted]
      FROM dbo.elearning_xref_modules EXM
      LEFT OUTER JOIN elearning_user_completed_modules EUCM ON EXM.id = EUCM.ModuleId
      AND EUCM.UserId = @UserId
                  
      ORDER BY Tag, Parent, [module!1!id]
 
      RETURN

Help much appreciated

HStart Free Trial