How to convert xml as a table or html table in sqlserver

You find a much enhanced version of this answer here.

old answer:

because I don't know the column name when it pass as parameters

This makes it impossible, to call something like SELECT * FROM... You might think about dynamic SQL, but there is a great alternative: FLWOR

declare @tv  table
(
  id int, 
  username varchar(50),
  department varchar(50) 

)
insert into @tv values(1,'tom','finance'),(2,'mark','business');

SELECT
(
    SELECT *
    FROM @tv 
    FOR XML PATH('tr'),TYPE
).query('for $tr in /tr
         return <tr>
           {
             for $td in $tr/*
             return <td>{$td/text()}</td>
           }
                </tr>')
FOR XML PATH('table')

The .query() will run through your XML and re-create it as demanded.

The result:

<table>
  <tr>
    <td>1</td>
    <td>tom</td>
    <td>finance</td>
  </tr>
  <tr>
    <td>2</td>
    <td>mark</td>
    <td>business</td>
  </tr>
</table>

##UPDATE ##This is a solution with a FUNCTION on XML-base using FLWOR

It will transform any SELECT into a XHTML table: The call is as easy as this:

SELECT dbo.CreateHTMLTable((SELECT TOP 5 * FROM sys.objects FOR XML RAW,ELEMENTS XSINIL));

That's the code

CREATE FUNCTION dbo.CreateHTMLTable(@SelectForXmlRawElementsXsinil XML)
RETURNS XML
AS
BEGIN

RETURN
(
    SELECT  
    @SelectForXmlRawElementsXsinil.query('let $first:=/row[1]
                return 
                <tr> 
                {
                for $th in $first/*
                return <td>{local-name($th)}</td>
                }
                </tr>') AS thead
    ,@SelectForXmlRawElementsXsinil.query('for $tr in /row
                 return 
                 <tr>
                 {
                 for $td in $tr/*
                 return <td>{string($td)}</td>
                 }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE
);
END
GO

--a mock-up-table with data

declare @tv  table
(
  id int, 
  username varchar(50),
  department varchar(50) 
)
--NULL value in row=2!!!
insert into @tv values(1,'tom','finance'),(2,NULL,'business');

--That's the way you use it

SELECT dbo.CreateHTMLTable((SELECT * FROM @tv FOR XML RAW,ELEMENTS XSINIL));

--Clean-Up

DROP FUNCTION dbo.CreateHTMLTable;

returns be aware of the NULL value in last row!

<table>
  <thead>
    <tr>
      <td>id</td>
      <td>username</td>
      <td>department</td>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>tom</td>
      <td>finance</td>
    </tr>
    <tr>
      <td>2</td>
      <td />
      <td>business</td>
    </tr>
  </tbody>
</table>

##UPDATE Possible Enhancements

• You can control the layout easily via CSS
• You might pass in class names for table, thead, tbody... for better CSS-control
• One could pass in a one-row-footer with aggregated values as second parameter and append it as <tfoot>