Karan Karan - 2 months ago 13
SQL Question

SQL Server For XML PATH nesting is not happening

I am trying to generate the XML out of some variable in the desired format with proper nesting. I am unable to close the

<SProc>
element before starting the
<ParamList>
element. Please help me out with this SQL statement.

My desired output is:

<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SProc>dbo.usp_InsertUserPlay</SProc>
<ParamList>
<Param>Rocky</Param>
<Param>Boxer</Param>
<Param>SpaceX</Param>
<Param xsi:nil="true" />
<Param>0</Param>
</ParamList>
</AsyncRequest>


My SQL code & its output is different:

Declare @requestMessage xml,
@P1 nvarchar(20) = 'Rocky',
@P2 nvarchar(20) = 'Boxer',
@P3 nvarchar(20) = 'SpaceX'

select
@requestMessage = (select 'dbo.usp_InsertUserPlay',
(select @P1 as [Param],
@P2 as [Param],
@P3 as [Param],
null as [Param],
0 as [Param]
FOR XML RAW('ParamList'), TYPE, Elements XSINIL)
FOR XML PATH ('SProc'), ROOT('AsyncRequest'), TYPE, ELEMENTS XSINIL)

select @requestMessage AS XML_Output


The output is :

<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SProc>dbo.usp_InsertUserPlay
<ParamList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Param>Rocky</Param>
<Param>Boxer</Param>
<Param>SpaceX</Param>
<Param xsi:nil="true" />
<Param>0</Param>
</ParamList></SProc>
</AsyncRequest>

Answer

You're very close:

    select @requestMessage =    (select  'dbo.usp_InsertUserPlay' as [SProc],
                (select @P1 as [Param], 
                        @P2 as [Param],     
                        @P3 as[Param],                                  
                        null as [Param],    
                        0 as [Param]
                     FOR XML RAW('ParamList'), TYPE, Elements XSINIL)
           FOR XML PATH('AsyncRequest'), TYPE, ELEMENTS XSINIL)

The trick is that not supplying a column name as you did causes SQL Server to insert the name of the stored procedure as raw element content, when you want it wrapped in an element.