Returning the correct xml structure from query in SQL Server

Question

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

I changed it to use nested FOR XML Statements to allow for multiple values in the correct element.

    SELECT
   Consumer.*,
   (SELECT
       cp.*
        FROM dbo.ConsumerPhone cp
        WHERE cp.ConsumerID = Consumer.ConsumerID
        FOR XML PATH('ConsumerPhone'), TYPE
       ) AS 'ConsumerPhoneNos'
    FROM dbo.Consumer Consumer
    where Consumer.ConsumerID = 220901
FOR XML PATH('Consumer'), ROOT('Consumers')

j.v. · Accepted Answer · 2015-12-04 09:18:05Z

1

This will work:

     SELECT Consumer.Username AS "@Username",
            ConsumerPhone.Number AS "ConsumerPhone",
            ConsumerAddress.Country AS "ConsumerAddress"
     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 PATH ('Consumer');

answered Dec 4, 2015 at 9:18

j.v.

9976 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Returning the correct xml structure from query in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related