Split XML report in SQL

Shali_123 21 Reputation points
2021-05-17T07:48:58.087+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,278 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. Viorel 109.9K Reputation points
    2021-05-17T08:52:14.003+00:00

    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.


0 additional answers

Sort by: Most helpful