How do I format XML in SQL Server from parameters?

Question

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>

So what is your question? What isn't working as you require? What are you desired results? — Dale K
– Dale K, Commented Jul 5, 2022 at 22:48
Are you trying to avoid the nesting of elements? Have you tried PATH mode instead of AUTO? Ref: Examples: Use PATH mode — AlwaysLearning
– AlwaysLearning, Commented Jul 5, 2022 at 23:11

Charlieface · Accepted Answer · 2022-07-06 00:03:08Z

1

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

answered Jul 6, 2022 at 0:03

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pholcroft Over a year ago

I didn't know how to use FOR XML PATH correctly, thank you this is exactly what I needed. The SP using the deprecated type is part of an external piece of software I cannot change without breaking it, hence the wrapper.

Collectives™ on Stack Overflow

How do I format XML in SQL Server from parameters?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related