Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I need to get an xml output where we have one lead no along with the resultset of aggregate function
 
Code snippet:
select distinct
ISNULL(job.joblead,'')
ROW_NUMBER() OVER(ORDER BY Desc) as Item_Number ,Count(*) Quantity,Desc Desc
FROM dbo.tableJob job
    left join jobprods  on thejob=tableJob
    left join ItemSkill on tblleadsprodsold_sp.theItem = ItemSkill.theItem
    left join SkillGroup on ItemSkill.theSkillGroup = SkillGroup.theKey
 where thejob = 6670683  and  tableJob=6670683
 group by SkillGroup.theKey, SkillGroup.Desc,tableJob
for xml path('Test')
 

Current output:
<Test>
  <joblead>6670683</joblead>
  <Item_Number>1</Item_Number>
  <Quantity>3</Quantity>
  <Desc>Inserts</Desc>
</Test>
<Test>
  <joblead>6670683</joblead>
  <Item_Number>2</Item_Number>
  <Quantity>1</Quantity>
  <Desc>Delte</Desc>
</Test>
<Test>
  <joblead>6670683</joblead>
  <Item_Number>3</Item_Number>
  <Quantity>1</Quantity>
  <Desc>Remove</Desc>
</Test>
 
Desired output:
<Test>
  <joblead>6670683</joblead>
 
  <Item_Number>1</Item_Number>
  <Quantity>3</Quantity>
  <Desc>Inserts</Desc>
 
  <Item_Number>2</Item_Number>
  <Quantity>1</Quantity>
  <Desc>Delte</Desc>
 
  <Item_Number>3</Item_Number>
  <Quantity>1</Quantity>
  <Desc>Remove</Desc>
</Test>
Posted 21-Feb-13 15:33pm
Edited 21-Feb-13 19:50pm
v4

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi Bharath
 
Below is the solution for your question. Just keep path('') and include root('Table')
 
select distinct
ISNULL(job.joblead,'')
ROW_NUMBER() OVER(ORDER BY Desc) as Item_Number ,Count(*) Quantity,Desc Desc
FROM dbo.tableJob job
    left join jobprods  on thejob=tableJob
    left join ItemSkill on tblleadsprodsold_sp.theItem = ItemSkill.theItem
    left join SkillGroup on ItemSkill.theSkillGroup = SkillGroup.theKey
 where thejob = 6670683  and  tableJob=6670683
 group by SkillGroup.theKey, SkillGroup.Desc,tableJob
for xml path(''), root('Test')
 
Regards
Willington
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 365
1 Nirav Prabtani 252
2 Richard Deeming 215
3 CHill60 170
4 _Amy 145
0 OriginalGriff 8,104
1 Sergey Alexandrovich Kryukov 7,045
2 Maciej Los 4,039
3 Peter Leow 3,738
4 CHill60 2,912


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100