title: sql server - For XML Raw - Add custom attribute to the element
body: <p>I am trying to generate the XML in the following desired format in SQL Server 2014. But i am unable to make this small change of adding the attribute name to the Param elements. I simply want to add the "name" attribute to the Param element with the value.</p>
  
  <p>Current SQL Statements:- </p>
  
  <pre><code> Declare @requestMessage xml,
    @P1 nvarchar(20)= 'Rocky', @P2 nvarchar(20) = 'Boxer', @P3 nvarchar(20) = 'SpaceX',
  @P4 nvarchar(20)
  
  select @requestMessage =    (select  'dbo.usp_InsertUserPlay' as [SProc],
                                      (select @P1 as [Param], 
                                              @P2 as [Param],     
                                              @P3 as [Param],           
                                              @P4 as [Param],    
                                              0 as [Param]
                           FOR XML RAW('ParamList'), TYPE, ELEMENTS XSINIL)
                     FOR XML PATH('AsyncRequest'), TYPE, ELEMENTS XSINIL)
  
  select @requestMessage AS XML_Output
  </code></pre>
  
  <p>XML Output:- </p>
  
  <pre><code>&lt;AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;SProc&gt;dbo.usp_InsertUserPlay&lt;/SProc&gt;
    &lt;ParamList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
      &lt;Param&gt;Rocky&lt;/Param&gt;
      &lt;Param&gt;Boxer&lt;/Param&gt;
      &lt;Param&gt;SpaceX&lt;/Param&gt;
      &lt;Param xsi:nil="true" /&gt;
      &lt;Param&gt;0&lt;/Param&gt;
    &lt;/ParamList&gt;
  &lt;/AsyncRequest&gt;
  </code></pre>
  
  <p>My Desired XML output:- </p>
  
  <pre><code>&lt;AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
      &lt;SProc&gt;dbo.usp_InsertUserPlay&lt;/SProc&gt;
      &lt;ParamList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
          &lt;Param name="P1"&gt;Rocky&lt;/Param&gt;
          &lt;Param name="P2"&gt;Boxer&lt;/Param&gt;
          &lt;Param name="P3"&gt;SpaceX&lt;/Param&gt;
          &lt;Param name="P4" xsi:nil="true" /&gt;
          &lt;Param name="P5"&gt;0&lt;/Param&gt;
      &lt;/ParamList&gt;
  &lt;/AsyncRequest&gt;
  </code></pre>