CODEWITHSUNDEEP
sqlsql-server
Karan
Karan

Reputation: 3338

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>

Upvotes: 1

Views: 547

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

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>

Upvotes: 2

Related Questions