I have a stored procedure in SQL Server that is simply a select statement:

SELECT TOP 3 
    'SIMPLERULE' AS 'Header/TransactionType',
    'PROJECT' AS 'Header/BusinessRule',
    REPLACE(j.Job, '-', '') + '000000' AS 'SimpleRule/RuleCode',
    j.Description AS 'SimpleRule/Description',
    'N' AS 'SimpleRule/IsDelete'
FROM
    [dbo].[Table_A] AS J
INNER JOIN 
    [dbo].[TAble_B] AS C ON J.Job = C.Job
WHERE 
    <some condition>
FOR XML PATH('Transaction'), ROOT('Transactions'), ELEMENTS XSINIL, 

But it needs to return data as XML formatted document. So I'm using FOR XML PATH('Transaction'), ROOT('Transactions'), ELEMENTS XSINIL.

When I ran this in SQL Server Management Studio, it produces the correct XML, and it looks ok.

First the result is this way:

And then, when link is clicked, I see the XML:

<Transactions>
  <Transaction>
    <Header>
      <TransactionType>SIMPLERULE</TransactionType>
      <BusinessRule>PROJECT</BusinessRule>
    </Header>
    <SimpleRule>
      <RuleCode>0000000000000</RuleCode>
      <Description>Training Purposes</Description>
      <IsDelete>N</IsDelete>
    </SimpleRule>
  </Transaction>
  <Transaction>
    <Header>
      <TransactionType>SIMPLERULE</TransactionType>
      <BusinessRule>PROJECT</BusinessRule>
    </Header>
    <SimpleRule>
      <RuleCode>0000000000000</RuleCode>
      <Description>Training Purposes</Description>
      <IsDelete>N</IsDelete>
    </SimpleRule>
  </Transaction>
</Transactions>

(Result is much larger, but for this part is enough shown)

Next step - I call the stored procedure from the Power Automate flow (triggered by "When Http Request is received"), and the result that I get is corrupted. Corrupted in a strange way.

{"Table1":**[{"XML_F5....6B"**:"
<Transactions>
    <Transaction>
        <Header>
            <TransactionType>SIMPLERULE</TransactionType>
            <BusinessRule>PROJECT</BusinessRule>
        </Header>
        <SimpleRule>
            <RuleCode>0000000000000</RuleCode>
            <Description>Training Purposes</Description>
            <IsDelete>N</IsDelete>
        </SimpleRule>
    </Transaction>
    <Transaction>
        <Header>
            <TransactionType>SIMPLERULE</TransactionType>
            <BusinessRule>PROJECT</BusinessRule>
        </Header>
        <SimpleRule>
            <RuleCode>0000000000000</RuleCode>
            <Description>Training Purposes</Description>
            <IsDelete>N</IsDelete>
        </SimpleRule>
    </Transaction>
    ... more here

At the first part I understand what this is and what it is for: {"Table1":[{"XML_F5....6B": from the table output before - the header of a column. I could work with it.

However, further inspecting the returned response, I see something like this in multiple places

<Transaction>
        ***<H"},{"XML_F5....6B":"eader>***
            <TransactionType>SIMPLERULE</TransactionType>
            <BusinessRule>PROJECT</BusinessRule>
        ***</Header>***
        <SimpleRule>
            <RuleCode>4007839xx993uu</RuleCode>
            <Description>Some description</Description>
            <IsDelete>N</IsDelete>
        </SimpleRule>
    </Transaction>

In this snippet, it is cut into tag "Header", but in other times it may appear cutting in the the middle of the tag "Transaction", or something else.

The data returned is still correct, however the formatting is messed up and I can't consume it this way. I tried changing the Accept and Content-Type header in the request that triggers the Power Automate flow. It does not help. Looks to me as JSON format is trying to make its way where it does not belong.

Please, help. Maybe I don't know of the header to include? Some options?