从具有值和名称的数据表创建具有可变元素名称的XML [英] Create XML with variable element names from a data table with values and names
问题描述
我没有找到相关的帖子,所以我决定问。
I wasn't able to find a relevant post, so I decided to ask.
我的SQL Server数据库中有以下表格:
I have the following table in my SQL Server database:
ID attname value
---------------------------------
22405543 blktradind N
22405543 brkref IRVTGB2X
22405543 buyamt 104650.2000
22405543 buycurref USD
22405543 Buy53ref
22405543 Buy56ref
22405543 Buy57ref IRVTBEBB
如何通过将FOR XML变体转换为动态基于每个消息的attname的XML结果?
How can I convert this table by using FOR XML variations to a dynamic XML result based on the "attname" that each message has?
对于上面的摘录,所需的结果将是:
For the excerpt above, the desired result would be:
<Message id=22405543>
<blktradind>N</blktradind>
<brkref>IRVTGB2X</brkref>
<buyamt>104650.2000</buyamt>
<buycurref>USD</buycurref>
<buy53ref />
<buy56ref />
<buy57ref>IRVTBEBB</buy57ref>
</Message>
谢谢
推荐答案
这是不可能正常。 SQL Server不支持输出的变量列别名。但是有解决方法:
This is not possible normally. SQL Server does not support variable column aliases for your output. But there are workarounds:
这种方法有点难看,因为我通常不会喜欢通过字符串连接来创建XML。但是通过使用 SELECT FOR XML PATH
本身的值来封装这个值,这是非常稳定的禁止字符,如<>或&
。
The approach is a bit ugly, as I normally would not prefer to create XML via string concatenation. But by wrapping the value with a SELECT FOR XML PATH
itself, this is even stable with forbidden characters like <> or &
.
DECLARE @tbl TABLE(ID BIGINT,attname NVARCHAR(100),value NVARCHAR(100));
INSERT INTO @tbl VALUES
(22405543,'blktradind','N')
,(22405543,'brkref','IRVTGB2X')
,(22405543,'buyamt','104650.2000')
,(22405543,'buycurref','USD')
,(22405543,'Buy53ref',NULL)
,(22405543,'Buy56ref',NULL)
,(22405543,'Buy57ref','IRVTBEBB');
WITH DistinctIDs AS
(
SELECT DISTINCT ID FROM @tbl
)
SELECT ID AS [@id]
,(
SELECT CAST(N'<' + attname + N'>' + ISNULL((SELECT value AS [*] FOR XML PATH('')),N'') + N'</' + attname + N'>' AS XML)
FROM @tbl AS tbl
WHERE tbl.ID=DistinctIDs.ID
FOR XML PATH(''),TYPE
)
FROM DistinctIDs
FOR XML PATH('Message')
结果
<Message id="22405543">
<blktradind>N</blktradind>
<brkref>IRVTGB2X</brkref>
<buyamt>104650.2000</buyamt>
<buycurref>USD</buycurref>
<Buy53ref />
<Buy56ref />
<Buy57ref>IRVTBEBB</Buy57ref>
</Message>
动态SQL
动态完整语句,并使用 EXEC(@cmd)
来执行它。这样的一个例子:
dynamic SQL
You could build the full statement dynamically and use EXEC(@cmd)
to execute it. Something like this:
(注意!! : SELECT TOP 1
该ID不适合实际数据!)
(Attention!!: The SELECT TOP 1
to get the ID is not appropriate for actual data!)
DECLARE @cmd NVARCHAR(MAX)=
(
SELECT 'SELECT ''' + CAST((SELECT TOP 1 ID FROM @tbl) AS NVARCHAR(100)) + ''' AS [@id] '
+ (
SELECT ',''' + ISNULL(value,'') + ''' AS [' + attname + ']'
FROM @tbl
FOR XML PATH('')
)
+ ' FOR XML PATH(''Message'')'
);
EXEC(@cmd)
这篇关于从具有值和名称的数据表创建具有可变元素名称的XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!