Resolving CHASM -> N-1-N trap using XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Resolving CHASM -> N-1-N trap using XML

Author

Topic

swanandbagve
Starting Member

2 Posts

Posted - 2014-01-23 : 01:08:38

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

swanandbagve
Starting Member

2 Posts

Posted - 2014-01-23 : 02:26:08

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 07:14:22

yep
see similar examples here
http://visakhm.blogspot.com/2013/12/generating-nested-xml-structures-with.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources