I'm trying to select data from a consumer parent table and inner join on multiple child tables and return it as XML.
I have the Consumer parent table and 2 child tables ConsumerPhone and Consumer Address.
The Output I want should look like this
<Consumer Username="eallen"> <ConsumerPhone/> <ConsumerAddress /> </Consumer> <Consumer Username="jgibson"> <ConsumerPhone/> <ConsumerAddress /> </Consumer> But my query (which I think is the issue) is placing the Consumer Address inside the ConsumerPhone element
<Consumer Username="eallen"> <ConsumerPhone> <ConsumerAddress /> </ConsumerPhone> </Consumer> <Consumer Username="jgibson"> <ConsumerPhone> <ConsumerAddress /> </ConsumerPhone> </Consumer> Here is the query
SELECT Consumer.Username,ConsumerPhone.Number,ConsumerAddress.Country FROM [dbo].[Consumer] Consumer LEFT JOIN [dbo].[ConsumerPhone] ConsumerPhone ON Consumer.ConsumerID = ConsumerPhone.ConsumerID LEFT JOIN [dbo].[ConsumerAddress] ConsumerAddress ON Consumer.ConsumerID = ConsumerAddress.ConsumerID order by Consumer.ConsumerID asc OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY FOR XML AUTO; I'm not sure how to fix my query to get the output I'm looking for.
Thanks
No comments:
Post a Comment