Split XML report in SQL

Question

I have below SQL stored procedure which generates a report. However it sends all the various rows as one big chunk report. Rather I would want it to split each row at node "OrderReport".
Any idea how I can split the below query to have individual XML rows generated.
I have tried using CROSS APPLY but get an error 't' has more columns than were specified in the column list.

Original query:
DECLARE @report XML
BEGIN

UPDATE [Orders].dbo.PurOrd
SET [Reason] = 'AutoFailure',
WHERE [Status] = 'Errored'
SET @report = (

SELECT
p.Name as "@DeezNutz ",
CASE p.Name
WHEN 'Default' THEN convert(xml,p.Reason)
ELSE convert(xml,f.Reason)
END AS "Reason",
(
SELECT u2.u_first_name as "@Firstname "
FROM [Users].dbo.Users u2
WHERE u2.u_user_id = u.u_user_id
for xml PATH('Users'), type
)
FROM [Orders].dbo.Forms f
JOIN [Orders].dbo.PurOrd p on f.OrderId = p.OrderId
LEFT JOIN [Users].dbo.Users u ON p.Customerid = u.u_user_id
WHERE
(p.Status = 'Failed' )
FOR XML PATH('PurchaseOrder'), TYPE
)
select @report
FOR XML PATH('OrderReport')
END

Output of above query:
<OrderReport>
<PurchaseOrder Name="Default">
<Reason>
<errors>
<e reason="autofailure" />
</errors>
</Reason>
<Users FirstName="Mike_1" />
</PurchaseOrder>
<PurchaseOrder Name="Default">
<Reason>
<errors>
<e reason="autofailure" />
</errors>
</Reason>
<Users FirstName="Mike_2" />
</PurchaseOrder>
</OrderReport>

But I need the report output as
<OrderReport>
<PurchaseOrder Name="Default">
<Reason>
<errors>
<e reason="autofailure" />
</errors>
</Reason>
<Users FirstName="Mike_1" />
</PurchaseOrder>
</OrderReport>
<OrderReport>
<PurchaseOrder Name="Default">
<Reason>
<errors>
<e reason="autofailure" />
</errors>
</Reason>
<Users FirstName="Mike_2" />
</PurchaseOrder>
</OrderReport>

Tried using CROSS APPLY but gives me some error
DECLARE @report XML
BEGIN

UPDATE [Orders].dbo.PurOrd
SET [Reason] = 'AutoFailure',
WHERE [Status] = 'Errored'
SET @report = (
select t.PurchaseOrder FROM [Orders].dbo.Forms f
JOIN [Orders].dbo.PurOrd p on f.OrderId = p.OrderId
LEFT JOIN [Users].dbo.Users u ON p.Customerid = u.u_user_id
CROSS APPLY(
SELECT
p.Name as "@DeezNutz ",
CASE p.Name
WHEN 'Default' THEN convert(xml,p.Reason)
ELSE convert(xml,f.Reason)
END AS "Reason",
(
SELECT u2.u_first_name as "@Firstname "
FROM [Users].dbo.Users u2
WHERE u2.u_user_id = u.u_user_id
for xml PATH('Users'), type
)
FOR XML PATH(''), TYPE
) t(PurchaseOrder)
WHERE
(p.Status = 'Failed' )
FOR XML PATH('PurchaseOrder'), TYPE
)
select @report
FOR XML PATH('OrderReport')
END

Answer 1

Try something like this:

SELECT  
   p.Name as "PurchaseOrder/@Name",  
   CASE p.Name  
   WHEN 'Default' THEN convert(xml,p.Reason)  
   ELSE convert(xml,f.Reason)  
   END AS "PurchaseOrder/Reason",  
   (  
      SELECT u2.u_first_name as "@FirstName"  
      FROM [Users].dbo.Users u2  
      WHERE u2.u_user_id = u.u_user_id  
      for xml PATH('Users'), type  
   ) as PurchaseOrder  
FROM [Orders].dbo.Forms f  
JOIN [Orders].dbo.PurOrd p on f.OrderId = p.OrderId  
LEFT JOIN [Users].dbo.Users u ON p.Customerid = u.u_user_id  
WHERE p.Status = 'Failed'   
FOR XML PATH('OrderReport'), TYPE  

The second 'select @report ...' and the variable are not needed.

If it does not work, then show more details.