Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Query Help row with no data Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 8:46 PM
Points: 30, Visits: 124
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.
Post #1598314
Posted Thursday, July 31, 2014 9:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 2,395, Visits: 6,619
Use the ELEMENTS XSINIL after the FOR XML clause.
Post #1598339
Posted Thursday, July 31, 2014 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 5,245, Visits: 12,161
Don't think this works if there is no underlying row selected.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1598346
Posted Thursday, July 31, 2014 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 8:46 PM
Points: 30, Visits: 124
I try but its not work

SELECT a.* from @address a
where a.AddressID = 9
FOR XML path('Addresses'), ELEMENTS XSINIL
Post #1598347
Posted Thursday, July 31, 2014 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 5,245, Visits: 12,161
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1598348
Posted Thursday, July 31, 2014 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 8:46 PM
Points: 30, Visits: 124
Currently I'm doing this I just want to check is there any other way its possible. Any how Thanks for you your reply!
Post #1598356
Posted Thursday, July 31, 2014 10:29 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 2,395, Visits: 6,619
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>

Post #1598369
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse