我需要从一个与另外一个表分组的aggreagte函数中输出一个xml输出 [英] I need make out a xml output from a aggreagte function grouped with one more table

问题描述

我需要获取一个xml输出，其中我们有一个导联而不是聚合函数的结果集



代码片段：

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>

推荐答案

嗨Bharath



以下是您问题的解决方案。只需保留路径（''''）并包含root（''Table''）

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')

问候

Willington

Regards
Willington

这篇关于我需要从一个与另外一个表分组的aggreagte函数中输出一个xml输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！