Including null complex type elements in XML output

Question

Post reply

Including null complex type elements in XML output

mb 38827

SSC Rookie

Points: 33
More actions
April 3, 2013 at 5:02 am

#276015

Given two entities, mortgage and customer, each in a table and a third table linking customers to mortgages, I need to output as xml the data relating to each Mortgage.
create table Mortgage
( AccountNoint )
create table Customer
( CustomerNoint )
create table MortCustLink
( AccountNoint
,CustomerNoint )
insert into Mortgage values ( 123456 )
insert into Mortgage values ( 234567 )
insert into Mortgage values ( 345678 )
insert into Customer values ( 123 )
insert into Customer values ( 124 )
insert into Customer values ( 125 )
insert into MortCustLink values ( 123456, 123 )
insert into MortCustLink values ( 234567, 124 )
insert into MortCustLink values ( 234567, 125 )
A mortgage may have 0, 1 or more customers associated with it.
The xml output I am trying to produce is
<Mortgages>
<Mortgage>
<AccountNo>123456</AccountNo>
<Customers>
<Customer>
<CustomerNo>123</CustomerNo>
</Customer>
</Customers>
</Mortgage>
<Mortgage>
<AccountNo>234567</AccountNo>
<Customers>
<Customer>
<CustomerNo>124</CustomerNo>
</Customer>
<Customer>
<CustomerNo>125</CustomerNo>
</Customer>
</Customers>
</Mortgage>
<Mortgage>
<AccountNo>345678</AccountNo>
<Customers>
</Customers>
</Mortgage>
</Mortgages>
where the 'Customers' element appears even when there are no customers linked to a mortgage.
This is what I haven't managed to do and I'm running short on new ideas.
Code I am using is
select Mortgage.AccountNo
,( select Customer.CustomerNo
from MortCustLink
join Customer
on MortCustLink.CustomerNo = Customer.CustomerNo
where MortCustLink.AccountNo = Mortgage.AccountNo
for xml auto, root('Customers'), elements, type )
from Mortgage
for xml auto, root('Mortgages'), elements, type
and the xml produced is
<Mortgages>
<Mortgage>
<AccountNo>123456</AccountNo>
<Customers>
<Customer>
<CustomerNo>123</CustomerNo>
</Customer>
</Customers>
</Mortgage>
<Mortgage>
<AccountNo>234567</AccountNo>
<Customers>
<Customer>
<CustomerNo>124</CustomerNo>
</Customer>
<Customer>
<CustomerNo>125</CustomerNo>
</Customer>
</Customers>
</Mortgage>
<Mortgage>
<AccountNo>345678</AccountNo>
</Mortgage>
</Mortgages>
where the last Mortgage (AccountNo = 345678) has no Customers.
Your suggestions much appreciated, thanks for your help

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You're going to want to use FOR XML PATH instead of AUTO. Assuming you do, the query would look something like:

select Mortgage.AccountNo

, (select

( select Customer.CustomerNo

from #MortCustLink MortCustLink

join #Customer customer

on MortCustLink.CustomerNo = Customer.CustomerNo

where MortCustLink.AccountNo = Mortgage.AccountNo

for xml path(''), root('Customer'), elements, type )

for xml path(''), root('Customers'), elements, type )

from #Mortgage Mortgage

for xml path('Mortgage'), root('Mortgages'), elements, type

----------------------------------------------------------------------------------
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?

mb 38827 SSC Rookie Points: 33 More actions · Answer 2

Thanks Matt.

I thought logically it should be something like that but hadn't managed to make it work, I think because I'd tried to use the PATH option but incorrectly.