How do I format XML in SQL Server from parameters?-CodePudding

I am trying to make a wrapper parameterised stored procedure to execute another stored procedure which takes XML as its input (for use with SSRS).

This is how the XML stored procedure is currently run (the @Xml parameter is actually text type):

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

I have come up with the following, but I'm sure I'm doing it wrong. How do I get the XML formatted properly?

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

Testing execution:

DECLARE @cDate DATETIME = GETDATE()

EXECUTE [dbo].Report_Statement 
   @EndDate = @cDate,
   @OfficeFilterBy = 1,
   @DepartmentFilterBy = 2,
   @companyFilterBy = 3,
   @CustomerIdFilterBy = 4

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

CodePudding user response：

I have no idea why you are using XML to pass parameters (in the deprecated text datatype no less), but it looks like you just need FOR XML PATH along with a XQuery path for each column name

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