You are right Phil, works for columns but not rows;-)

Here is a workaround

😎

declare @address table

(

AddressID int,

AddressType varchar(12),

Address1 varchar(20),

Address2 varchar(20),

City varchar(25),

AgentID int

)

insert into @address

select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all

select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all

select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all

select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all

select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all

select 6, 'Home', 'ttt', 'loik road', 'NY', 3

;WITH AA AS

(

SELECT

NULLIF(A.AddressID,9) AS AddressID

,A.AddressType

,A.Address1

,A.Address2

,A.City

,A.AgentID

from @address A

WHERE AddressID = 1

UNION ALL

select NULL, NULL, NULL, NULL, NULL, NULL

)

SELECT * FROM AA A

FOR XML path('Addresses'), ELEMENTS XSINIL, TYPE

Results

<Addresses xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<AddressID>1</AddressID>

<AddressType>Home</AddressType>

<Address1>abc</Address1>

<Address2>xyz road</Address2>

<City>RJ</City>

<AgentID>1</AgentID>

</Addresses>

<Addresses xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<AddressID xsi:nil="true" />

<AddressType xsi:nil="true" />

<Address1 xsi:nil="true" />

<Address2 xsi:nil="true" />

<City xsi:nil="true" />

<AgentID xsi:nil="true" />

</Addresses>