March 3, 2011 at 5:44 pm
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.'
March 3, 2011 at 8:16 pm
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?
March 4, 2011 at 10:02 am
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