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

Question

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>

Paweł Dyl · Accepted Answer · 2016-09-27 12:28:24Z

2

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>

answered Sep 27, 2016 at 12:28

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Karan Over a year ago

Thanks for the help :-)

Karan Over a year ago

Another thing i noticed here. We have to CAST the non-string based parameters to nvarchar(max) data type to prevent the runtime XML generation error. For instance, if @P4 is int then the statement should be ('P4', CAST(@P4 as nvarchar(max)))

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related