如何从 SQL Server 2014 获取特定的 XML 格式(提供示例)

Question

多年来，我一直在用这个用头撞墙 - 我终于屈服了。

任何SQL/XML专家可以看看下面，并告诉我使用FOR XML(原始？/自动？/路径？)获得所需XML格式的最佳方法吗？

感谢您的帮助

测试数据

CREATE TABLE #CustomerData(
[Customer ID] int
, IsCustomerID bit DEFAULT 1
, Amount1 float DEFAULT 0
, Amount2 float DEFAULT 0
)


INSERT #CustomerData
SELECT *
FROM (

VALUES (12345, 1, 50, 75),
(12444, 1, 100, 100),
(12455, 1, 25, 65)

) zz ([Customer ID], IsCustomerID, Amount1, Amount2)

我尝试了 FOR XML RAW/FOR XML PATH 和 FOR XML AUTO 的各种组合，但没有一个完全匹配。

<Root>
<Customers>
<Customer Prefix="Loan">
<Property Name="Customer ID" Value="REF_1234" IsCustomerId="True" />
<Property Name="Amount1" Value="10" />
<Property Name="Amount2" Value="15" />
</Customer>
</Customers>
</Root>

"Loan"是一个硬编码的文本标志，应用于上表中的所有条目，尽管没有特定的列。

"客户 ID" "IsCustomerID" "金额1" "金额2">

所有字段都在单个表中

Answer 1

几件事：

• 您没有指定结果中Prefix值的来源，但对于查询结构来说，这无关紧要。
• FOR XML PATH允许您使用特定语法("@"表示属性)召唤嵌套元素和属性，并指定TYPE以将子查询插入为类型化的 XML。
• T-SQL 无法将列动态透视为行(仅静态)，因此无法(轻松)编写查询以在其自己的子元素中生成每个单独的列，除非显式写出列。只有当您确实需要动态输出时，这才是一个问题(即，即使人们稍后添加列，查询也必须继续包含所有列而不重写)。
• 由于 T-SQL 用于将FLOAT和REAL值插入 XML 的完全特殊语法(始终使用科学记数法)，因此通常最好根本不使用FLOAT/REAL，而是使用特定的整型(带CONVERT)，或者使用FORMAT来生成更友好的值。同样，BIT值将始终输出为1或0，因此如果您需要其他值，则需要自己提供。

所以：

SELECT (
SELECT 
"@Prefix" = 'Loan',
(SELECT 
"@Name" = 'Customer ID', 
"@Value" = [Customer ID], 
"@IsCustomerID" = CASE IsCustomerId WHEN 1 THEN 'True' ELSE 'False' END
FOR XML PATH('Property'), TYPE
),
(SELECT "@Name" = 'Amount1', "@Value" = FORMAT(Amount1, 'G') FOR XML PATH('Property'), TYPE),
(SELECT "@Name" = 'Amount2', "@Value" = FORMAT(Amount2, 'G') FOR XML PATH('Property'), TYPE)
FROM #CustomerData
FOR XML PATH ('Customer'), ROOT('Customers'), TYPE
)
FOR XML PATH('Root')

通过使用使用UNION ALL的子查询，可以分解出重复提及Property：

SELECT (
SELECT 
"@Prefix" = 'Loan', 
(
SELECT * FROM (
SELECT 
"@Name" = 'Customer ID', 
"@Value" = [Customer ID], 
"@IsCustomerID" = CASE IsCustomerId WHEN 1 THEN 'True' ELSE 'False' END 
UNION ALL
SELECT "@Name" = 'Amount1', "@Value" = FORMAT(Amount1, 'G'), NULL 
UNION ALL
SELECT "@Name" = 'Amount2', "@Value" = FORMAT(Amount2, 'G'), NULL
) _
FOR XML PATH('Property'), TYPE
)
FROM #CustomerData
FOR XML PATH ('Customer'), ROOT('Customers'), TYPE
)
FOR XML PATH('Root')

我可以看到这些表单中的任何一个或多或少都是可读/可维护的，具体取决于可能更改的内容。