XML Query Help row with no data

  • 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.

  • Use the ELEMENTS XSINIL after the FOR XML clause.

    😎

  • 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

  • I try but its not work

    SELECT a.* from @address a

    where a.AddressID = 9

    FOR XML path('Addresses'), ELEMENTS XSINIL

  • 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

  • Currently I'm doing this I just want to check is there any other way its possible. Any how Thanks for you your reply!

  • 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