如何在SQL Server中从参数格式化XML ?

Question

我正在尝试使包装器参数化存储过程执行另一个存储过程，该存储过程将XML作为其输入(用于SSRS)。

XML存储过程当前是这样运行的(@Xml参数实际上是文本类型):

EXEC Report_Invoice @Xml = N'<XML><Params Date="2022-07-05T00:00:00" /><Customer KeyId="19" /><company KeyId="1" /><Department KeyId="1" /><Office KeyId="1" /></XML>'

我已经想出了以下，但我肯定我做错了。如何正确地格式化XML ?

CREATE OR ALTER PROCEDURE Report_Statement
@EndDate SMALLDATETIME = NULL,
@OfficeFilterBy INT = NULL,
@DepartmentFilterBy INT = NULL,
@companyFilterBy INT = NULL,
@CustomerIdFilterBy INT = NULL
AS
DECLARE @myXml Xml = (
SELECT 
Params.[Date],
CustomerOffice.KeyId,
Department.KeyId,
company.KeyId,
Customer.KeyId
FROM
(SELECT 1 AS ID, @EndDate AS [Date]) AS Params
LEFT JOIN 
(SELECT 1 AS ID, @OfficeFilterBy AS [KeyId]) CustomerOffice ON CustomerOffice.ID = Params.ID
LEFT JOIN 
(SELECT 1 AS ID, @DepartmentFilterBy AS [KeyId]) Department ON Department.ID = Params.ID
LEFT JOIN 
(SELECT 1 AS ID, @companyFilterBy AS [KeyId]) company ON company.ID = Params.ID
LEFT JOIN 
(SELECT 1 AS ID, @CustomerIdFilterBy AS [KeyId]) Customer ON Customer.ID = Params.ID
FOR XML AUTO, TYPE, ROOT('XML'))
DECLARE @myXmlStr NVARCHAR(MAX) =  CAST( @myXml AS NVARCHAR(MAX))
EXEC Report_Invoice @Xml = @myXmlStr
GO

测试执行:

DECLARE @cDate DATETIME = GETDATE()
EXECUTE [dbo].Report_Statement 
@EndDate = @cDate,
@OfficeFilterBy = 1,
@DepartmentFilterBy = 2,
@companyFilterBy = 3,
@CustomerIdFilterBy = 4

生成以下XML:

<XML>
<Params Date="2022-07-05T23:16:00">
<CustomerOffice KeyId="1">
<Department KeyId="2">
<company KeyId="3">
<Client KeyId="4" />
</company>
</Department>
</CustomerOffice>
</Params>
</XML>

Answer 1

我不知道为什么要使用XML来传递参数(在已弃用的text数据类型中)，但看起来您只需要FOR XML PATH以及每个列名称的XQuery路径

DECLARE @myXmlStr nvarchar(max) = (
SELECT
@EndDate AS [Params/@Date],
@OfficeFilterBy AS [Office/@KeyId],
@DepartmentFilterBy AS [Department/@KeyId],
@companyFilterBy AS [company/@KeyId],
@CustomerIdFilterBy AS [Customer/@KeyId]
FOR XML PATH(''), ROOT('XML')
);
EXEC Report_Invoice @Xml = @myXmlStr;

,db&lt的在小提琴