XML : Returning the correct xml structure from query in SQL Server

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