July 31, 2014 at 9:01 am
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
SELECT a.* from @address a
where a.AddressID = 1
FOR XML path('Addresses')
SELECT a.* from @address a
where a.AddressID = 9
FOR XML path('Addresses')
Issue:
As you can see for second query where AddressID = 9 is not exists so xml is not generated but my expected result is for second query is
<Addresses>
<AddressID />
<AddressType />
<Address1 />
<Address2 />
<City />
<AgentID />
</Addresses>
Thanks in advance for all your help.
July 31, 2014 at 9:41 am
Use the ELEMENTS XSINIL after the FOR XML clause.
😎
July 31, 2014 at 9:48 am
Don't think this works if there is no underlying row selected.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2014 at 9:48 am
I try but its not work
SELECT a.* from @address a
where a.AddressID = 9
FOR XML path('Addresses'), ELEMENTS XSINIL
July 31, 2014 at 9:49 am
I have an 'XML-hacker' solution:
declare @add1 xml
set @add1 = (SELECT a.* from @address a
where a.AddressID = 9
FOR XML path('Addresses'))
if @add1 is null
begin
set @add1 = cast('<Addresses>
<AddressID />
<AddressType />
<Address1 />
<Address2 />
<City />
<AgentID />
</Addresses>' as XML)
end
select @add1
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2014 at 10:01 am
Currently I'm doing this I just want to check is there any other way its possible. Any how Thanks for you your reply!
July 31, 2014 at 10:29 am
Phil Parkin (7/31/2014)
Don't think this works if there is no underlying row selected.
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>
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply