您的位置: 首页  >  技术问答  >  如何将xml转换为sqlserver中的表或html表

如何将xml转换为sqlserver中的表或html表

分类: 技术问答 • 2022-03-08 20:23:33
问题描述：

xml是sqlserver中的一个类型，现在我想将一个表变量传递给procedure，但是你必须将该表定义为type（使用create type）。
所以我认为我可以使用xml作为变量作为参数传递给过程。

xml is a type in sqlserver, now I want to pass a table variable to procedure, but you must define the table as type (use "create type"). So I think I can use xml as a variable to pass into the procedure as parameters.

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

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

declare @xml xml;
set @xml  =(select * from @tv for xml  path('row') ,type )
select @xml  ;

我想使用像@tv这样的表作为参数，将其传递给程序，
和得到如下结果：

I want to use table like @tv as parameter, pass it to procedure, and get the result like this: 

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

我知道可以这样做：

选择id为td，用户名为td，department为td，@ tv为xml raw（'tr'），元素

但我希望它是动态的，因为我在作为参数传递时不知道列名。

but I want it dynamic, because I don't know the column name when it pass as parameters.

更多，我想在结果html中获取表列名称，例如

further more ,I want get the table column name in the result html ,like 

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

，所以当我将表变量（或xml）传递给函数时，它会像这样返回

,so when I pass a table variable(or xml ) to function ,it return like this

答


因为我在作为参数传递时不知道列名

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

这使得无法调用类似 SELECT * FROM ...你可能会想到动态SQL，但有一个很好的选择： FLWOR

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')

.query（）将运行您的XML并按需要重新创建它。

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

结果：

<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



这是一个解决方案使用 XML -base上 FUNCTION us / library / ms190945.aspxrel =nofollow> FLWOR



它将转换任何 SELECT 进入XHTML表：调用就像这样简单：

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));

这就是代码

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));

- 清理

DROP FUNCTION dbo.CreateHTMLTable;

返回请注意最后一行中的NULL值！ 

<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>



更新可能的增强功能




  • 您可以通过 CSS

  • 轻松控制布局您可以传入表的类名， thead ， tbody ...更好的CSS控制

  • 可以使用聚合值作为第二个参数传入一行页脚，并将其附加为< tfoot>

    • 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>

相关推荐