omit empty elements in for xml query

  • Hi SQL experts,

    I need help with a query to build XML.

    I have a query like the following:

    DECLARE @parentTbl TABLE(parent VARCHAR(6));

    DECLARE @childTbl TABLE(parent VARCHAR(6), child VARCHAR(6));

    DECLARE @childDetailsTbl TABLE(child VARCHAR(6), childDetails VARCHAR(12));

    INSERT INTO @parentTbl VALUES('parent');

    INSERT INTO @childTbl VALUES('parent', 'child1');

    INSERT INTO @childTbl VALUES('parent', 'child2');

    INSERT INTO @childDetailsTbl VALUES('child1', 'childDetails');

    SELECTparentTbl.parent,

    childTbl.child,

    childDetailsTbl.childDetails

    FROM @parentTbl parentTbl

    LEFT JOIN @childTbl childTbl ON childTbl.parent = parentTbl.parent

    LEFT JOIN @childDetailsTbl childDetailsTbl ON childDetailsTbl.child = childTbl.child

    FOR XML AUTO, ELEMENTS ABSENT

    Which outputs

    <parentTbl>

    <parent>parent</parent>

    <childTbl>

    <child>child1</child>

    <childDetailsTbl>

    <childDetails>childDetails</childDetails>

    </childDetailsTbl>

    </childTbl>

    <childTbl>

    <child>child2</child>

    <childDetailsTbl />

    </childTbl>

    </parentTbl>

    What's the best way to get rid of the empty childDetailsTbl element in child2? Ideally while continuing to use AUTO or possibly PATH mode.

    Thanks,

    -a.



    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

  • It runs contrary to previous teachings, but - create correlated sub-queries to ensure what you're looking for.

    This is a bit more wordy, but does allow for better control over the sub nodes:

    DECLARE @parentTbl TABLE(parent VARCHAR(6));

    DECLARE @childTbl TABLE(parent VARCHAR(6), child VARCHAR(6));

    DECLARE @childDetailsTbl TABLE(child VARCHAR(6), childDetails VARCHAR(12));

    INSERT INTO @parentTbl VALUES('parent');

    INSERT INTO @childTbl VALUES('parent', 'child1');

    INSERT INTO @childTbl VALUES('parent', 'child2');

    INSERT INTO @childDetailsTbl VALUES('child1', 'childDetails');

    SELECT parentTbl.parent,

    (select

    childTbl.child,

    (select childDetailsTbl.childDetails

    from @childDetailsTbl childDetailsTbl where childDetailsTbl.child = childTbl.child

    for xml auto, elements, type)

    from @childTbl childTbl where childTbl.parent = parentTbl.parent

    for xml auto, elements, type)

    FROM @parentTbl parentTbl

    FOR XML auto, elements

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks for the reply, I was worried it would be something that complicated.

    It's fine in this simple example, but that represents a couple days of coding. Better get started.

    -a.



    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply