singhch
asked on
SQL Query
Hi there,
The following is an example of my table
c1 c2
1 A
1 B
2 A
1 C
3 D
3 B
And I would like my query to output as
<row id =1>
<ele>A</ele>
<ele>B</ele>
<ele>C</ele>
</row>
<row id =2>
<ele>A</ele>
</row>
<row id =3>
<ele>D</ele>
<ele>B</ele>
</row>
I tried doing this
select t1.c1,
(select t2.c2 from table where t1.c1 = t2.c2 for xml paht('ele')
from table
group by c1
for xml path('row')
but it doesnt work. could someone pls point out where i am goin wrong? cheers
The following is an example of my table
c1 c2
1 A
1 B
2 A
1 C
3 D
3 B
And I would like my query to output as
<row id =1>
<ele>A</ele>
<ele>B</ele>
<ele>C</ele>
</row>
<row id =2>
<ele>A</ele>
</row>
<row id =3>
<ele>D</ele>
<ele>B</ele>
</row>
I tried doing this
select t1.c1,
(select t2.c2 from table where t1.c1 = t2.c2 for xml paht('ele')
from table
group by c1
for xml path('row')
but it doesnt work. could someone pls point out where i am goin wrong? cheers
Yveau
First try to change the typo from 'paht' to 'path' ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Notes:
1) Inner query should return no element (path('')) because you don't wrap ele in some parent.
2) ele is name of inner elements so put it inside inner query
3) inner query has to have "type" directive to return xml type not text. Text will be escaped and will not produce valid xml.
Patrik
1) Inner query should return no element (path('')) because you don't wrap ele in some parent.
2) ele is name of inner elements so put it inside inner query
3) inner query has to have "type" directive to return xml type not text. Text will be escaped and will not produce valid xml.
Patrik
@patrikt ... three minutes too slow :-\ I got exact the same as you did ... great minds think alike :-)
Yveau: :) :) It is not first time it happend to me there on EE. Once I produced exact same query and posted in exact same time as other expert. :)
... it's always a dash for the points :-)
Is there anything we can do for you regarding this Q ?
If you are satisfied with the answer, would please close the Q.
If you are satisfied with the answer, would please close the Q.
mbizup,
No problems with your recommendation.
Yveau
No problems with your recommendation.
Yveau
Forced accept.
Computer101
EE Admin
Computer101
EE Admin