如何将xml转换为sqlserver中的表或html表 [英] How to convert xml as a table or html table in sqlserver
问题描述
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> - 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>
UPDATE Possible Enhancements
这篇关于如何将xml转换为sqlserver中的表或html表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
