Karan Karan - 2 months ago 8
SQL Question

sql server - For XML Raw - Add custom attribute to the element

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.

Current SQL Statements:-

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


XML Output:-

<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SProc>dbo.usp_InsertUserPlay</SProc>
<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>
</AsyncRequest>


My Desired XML output:-

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

Answer

This should generate expected xml:

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

select  'dbo.usp_InsertUserPlay' as [SProc],
(
    select * FROM (VALUES 
        ('P1', @P1),
        ('P2', @P2),     
        ('P3', @P3),
        ('P4', @P4),
        ('P5', '0')) T([Param/@name], [Param])
    FOR XML PATH(''), ROOT('ParamList'), TYPE, ELEMENTS XSINIL
) FOR XML PATH('AsyncRequest'), TYPE, ELEMENTS XSINIL

Result:

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