How to add attribute value to root element when using for XML path?

Question

I have this SQL and it works:

set nocount on 

DECLARE
    @OrderID VARCHAR(200) = '13095314100';

declare @eOrder Table
(
  colOrderId  varchar(20),
  colDeliveryCountryCode varchar(20)
 )

insert into @eOrder values ('13095314100', 'SE')


SELECT 
    'name'  AS 'Val/@n', Sender.name AS Val

FROM @eOrder E
  inner join 
    (
       select  '12345' SenderID,     'KappAhl Sverige AB' "name",  'Idrottsvägen 14' "address1",   null address2,       '431 24' ZipCode,   'MÖLNDAL' City, 'SE' Country,  '010-138 87 11' phone
       union
       select  '12345',          'KappAhl Shop Online',       'c/o KappAhl OY',             'Unikkotie 3 C',    '01300'     ,  'VANTAA',        'PL',        '0753 267 881'    
    ) Sender
    on E.colDeliveryCountryCode = Sender.Country

WHERE colOrderId = @OrderID
FOR XML PATH('Sender');

I get this output as expected:

<Sender><Val n="name">KappAhl Sverige AB</Val></Sender>

But how do I add SenderID as an attribute to my Sender tag?

Wanted XML output:

<Sender SenderID="12345" ><Val n="name">KappAhl Sverige AB</Val></Sender>

Andrea · Accepted Answer · 2018-07-20 10:53:18Z

2

Add Sender.SenderID as '@SenderID' to your select:

SELECT Sender.SenderID as '@SenderID',
    'name'  AS 'Val/@n', Sender.name AS Val

Result:

answered Jul 20, 2018 at 10:53

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

How to add attribute value to root element when using for XML path?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related