CODEWITHSUNDEEP
sqlsql-serverxml
Karan
Karan

Reputation: 3338

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>

Upvotes: 2

Views: 63

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

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.

Upvotes: 1

Related Questions