FOR XML problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

FOR XML problem

Author

Topic

sks198117
Starting Member

46 Posts

Posted - 2008-12-16 : 05:14:32

table column is as follow
c1 c2 c3 c4

c1 is root
parent of c2 is c1
parent of c3 is c2
parent of c4 is c3

i have to write a procedure to generate a xml like

c1
all child of c1
c2
all child of c2
c3
all child of c3
c4
all child of c4

i ve tried using FOR XML but didnt get exactly

please help its urgent

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 05:15:40

can you show a sample output xml?

sks198117
Starting Member

46 Posts

Posted - 2008-12-16 : 05:20:32

quote:
Originally posted by visakh16

can you show a sample output xml?

Hi vikash
thanks for ur quick response
here it goes sample XML
-------------------------
<?xml version="1.0" encoding="utf-8"?>
<person name="C1" >
<person name="C11" />
<person name="C12" />
....
...
and so on
</person>
<person name="C2" >
<person name="C21" />
<person name="C22" />
....
...
and so on
</person>
<person name="C3" >
<person name="C31" />
<person name="C32" />
....
...
and so on
</person>
<person name="C4" >
<person name="C41" />
<person name="C42" />
....
...
and so on
</person>
-----------------------

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 05:26:56

try this
select stuff((select ','+ name from @tab where person name = 'c1' for xml path('')),1,1,'')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 05:26:56

are c1,c11,c12,... values in columns c1, c2, c3, c4?

sks198117
Starting Member

46 Posts

Posted - 2008-12-16 : 06:00:56

quote:
Originally posted by visakh16

are c1,c11,c12,... values in columns c1, c2, c3, c4?

yes c11 c12 ...are values

sks198117
Starting Member

46 Posts

Posted - 2008-12-16 : 06:36:56

quote:
Originally posted by sks198117

quote:
Originally posted by visakh16

are c1,c11,c12,... values in columns c1, c2, c3, c4?

yes c11 c12 ...are values

col1-----col2----------------------col3
1999 Jan - Mar , 1999 February, 1999
1999 Jan - Mar , 1999 January, 1999
1999 Jan - Mar , 1999 March, 1999
1999 Jul - Sep , 1999 August, 1999
1999 Jul - Sep , 1999 July, 1999
1999 Jul - Sep , 1999 September, 1999
1999 Oct - Dec ,1999 December, 1999
1999 Oct - Dec ,1999 November, 1999
1999 Oct - Dec ,1999 October, 1999

2000 Jan - Mar , 2000 January, 2000
2000 Jan - Mar , 2000 March, 2000
2000 Jul - Sep , 2000 August, 2000
2000 Jul - Sep , 2000 July, 2000
2000 Jul - Sep , 2000 September, 2000
2000 Oct - Dec ,2000 December, 2000
2000 Oct - Dec ,2000 November, 2000
2000 Oct - Dec ,2000 October, 2000

2001 Jul - Sep , 2001 September, 2001
2001 Oct - Dec ,2001 December, 2001
2001 Oct - Dec ,2001 November, 2001
2001 Oct - Dec ,2001 October, 2001
2002 Apr - Jun , 2002 April, 2002

2002 Jul - Sep , 2002 July, 2002
2002 Jul - Sep , 2002 September, 2002
2002 Oct - Dec ,2002 December, 2002
2002 Oct - Dec ,2002 November, 2002
2002 Oct - Dec ,2002 October, 2002
2003 Apr - Jun , 2003 April, 2003
2003 Apr - Jun , 2003 June, 2003
2003 Apr - Jun , 2003 May, 2003
2003 Jan - Mar , 2003 February, 2003
2003 Oct - Dec ,2003 October, 2003

2004 Jul - Sep , 2004 August, 2004
2004 Jul - Sep , 2004 July, 2004
2004 Jul - Sep , 2004 September, 2004
2004 Oct - Dec ,2004 December, 2004
2004 Oct - Dec ,2004 November, 2004
2004 Oct - Dec ,2004 October, 2004

2005 Oct - Dec ,2005 December, 2005
2005 Oct - Dec ,2005 November, 2005
2005 Oct - Dec ,2005 October, 2005

2006 Jul - Sep , 2006 July, 2006
2006 Jul - Sep , 2006 September, 2006
2006 Oct - Dec ,2006 December, 2006
2006 Oct - Dec ,2006 November, 2006
2006 Oct - Dec ,2006 October, 2006

2007 Jul - Sep , 2007 August, 2007
2007 Jul - Sep , 2007 July, 2007
2007 Jul - Sep , 2007 September, 2007
2007 Oct - Dec ,2007 December, 2007
2007 Oct - Dec ,2007 November, 2007
2007 Oct - Dec ,2007 October, 2007

2008 Jul - Sep , 2008 September, 2008
2008 Oct - Dec ,2008 December, 2008
2008 Oct - Dec ,2008 November, 2008
2008 Oct - Dec ,2008 October, 2008

output which i desire is like below
<?xml version="1.0" encoding="utf-8"?>
<person name="1999" >
<person name="Jan - Mar , 1999">
<person name="Jan 1999">
<person name="feb 1999">
</person>
....
...
and so on
</person>
<person name="2000" >
<person name="Jan - Mar , 2000">
<person name="Jan 2000">
<person name="feb 200">
</person>
....
...
and so on
</person>
..........
...........

hope this will help u better to understand the requirement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 07:04:25

seems like what you need is a recursive cte to get hierarchy and use FOR XML with it to get xml.

sks198117
Starting Member

46 Posts

Posted - 2008-12-16 : 07:20:17

quote:
Originally posted by visakh16

seems like what you need is a recursive cte to get hierarchy and use FOR XML with it to get xml.

yeah you are right vikash.

as i did this programatically on application side but i wanted to use it in stored procedure.

any clue??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources