get xml output for multiple tables with 1:M joins

• swanand.bagve

  SSC Enthusiast

  Points: 157

  More actions

  January 22, 2014 at 10:51 pm

  #396148

  Assume i have 3 tables

  Person(personname,age)

  Children(childname,personname)

  car(carname,personname)

  A persone can have multiple cars

  A person can have multiple children

  Its not possible to display the results in SQL rows

  and columns.

  If i try to it will give

  PersonName Carname Childname

  Sachin Audi C1

  Sachin Maruti C1

  Sachin Audi C2

  Sachin Maruti C2

  Instead of writing seperate queries the application wants

  to receive an xml output as follows

  <person>

  <pname>sachin</pname>

  <car>audi</car>

  <car>bmw</car>

  <cname>c1</cname>

  <cname>c2</cname>

  <person>

  How to get this output ?

• swanand.bagve

  SSC Enthusiast

  Points: 157

  More actions

  January 23, 2014 at 12:07 am

  #1682645

  I solved my own query using nesting 🙂

  SELECT person.name,

  (select vname

  from vehicle

  where vehicle.name=person.name

  FOR XML AUTO, TYPE),

  ( SELECT cname

  FROM children

  WHERE children.name = person.name

  FOR XML AUTO, TYPE)

  FROM person

  FOR XML AUTO, TYPE;