subquery must return onl one column in postgresql

Question

I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can someone help me with this?

SELECT (
        SELECT xmlforest('2021_PPP_Parasiticide_Program' AS Name, CASE 
                    WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                        THEN 'True'
                    ELSE 'False'
                    END AS requirementsMe, coalesce("temptable"."YTD_2021_Qualifying_Carton_Purchased", 0) AS qualifyingPurchaseAmount, 'Dollar' AS qualifyingPurchaseAmountType, CASE 
                    WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                        THEN 'Active'
                    ELSE 'In-Active'
                    END AS componentStatus)
            ,(
                SELECT (
                        SELECT xmlforest('q1_parasiticide_carton_rebate' AS rewardName, CASE 
                                    WHEN q1_parasiticide_carton_rebate IS NULL
                                        THEN CAST(0 AS VARCHAR)
                                    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                    END AS rewardAmount)
                        FROM temptable
                        )
                    ,(
                        SELECT xmlforest('q2_parasiticide_carton_rebate' AS rewardName, CASE 
                                    WHEN q2_parasiticide_carton_rebate IS NULL
                                        THEN CAST(0 AS VARCHAR)
                                    ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                    END AS rewardAmount)
                        FROM temptable
                        WHERE temptable.rus_id = temptable.rus_id
                        )
                FROM temptable
                )
        FROM temptable
        );

I want response in the below format -

<CustomerProgramStatus_response>
   <programComponents>
      <name>2020_PPP_Parasiticide_Program</name>
      <requirementsMet>FALSE</requirementsMet>
      <qualifyingPurchaseAmount>0</qualifyingPurchaseAmount>
      <qualifyingPurchaseAmountType>Dollar</qualifyingPurchaseAmountType>
      <componentStatus>In-Active</componentStatus>
      <componentRewards>
         <rewardName>Q1_Parasiticide_Carton_Rebate</rewardName>
         <rewardAmount>0</rewardAmount>
      </componentRewards>
      <componentRewards>
         <rewardName>Q2_Parasiticide_Carton_Rebate</rewardName>
         <rewardAmount>0</rewardAmount>
      </componentRewards>
   </programComponents>
</CustomerProgramStatus_response>
 

Answer 1

If temptable has one record, then you probably dont need subqueries at all. It it has more the one, then you need aggregates. Probably the best is not to provide more info, but study some examples like here: https://www.w3resource.com/PostgreSQL/postgresql-xml-functions.php and build your xml from inside moving outward step by step like

1:

SELECT xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
    CASE WHEN q1_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount)
FROM temptable;

2:

SELECT xmlforest(xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
    CASE WHEN q1_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount) as "componentRewards",
xmlforest('q2_parasiticide_carton_rebate' AS rewardName,
    CASE WHEN q2_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount) as "componentRewards")
FROM temptable;