FOR XML EXPLICIT使用

来源：互联网发布：都玩网络编辑：程序博客网时间：2024/06/05 06:18

declare @t table (

SendID nvarchar(50),

ChannelsID nvarchar(50) ,

DeliveryLoc nvarchar(50) ,

MsgNo nvarchar(50) ,

MsgDate datetime ,

Coin nvarchar(50) ,

Amt float,

Remark nvarchar(50) ,

ContactName nvarchar(50) ,

ContactTel nvarchar(50) ,

ContactAddress nvarchar(50),

ItemNo int ,

PartNo nvarchar(50) ,

Unit nvarchar(50) ,

Price float ,

Qty_BY Float ,

SubAmt_BY Float ,

DeliveryDate_BY datetime ,

ItemRemark_BY nvarchar(50)

)

insert @t select'','34010019','南京江宁镇宁桥北路','3410052400009808','2010-5-24 10:33:18','RMB','0','测试接口(南京仓)','刘拓','18602520723','南京江宁镇宁桥北路','1','01000188','台','1000.0000','6','0','2010-5-24 10:37:17','' union all

select'','34010019','南京江宁镇宁桥北路','3410052400009808','2010-5-24 10:33:18','RMB','0','测试接口(南京仓)','刘拓','18602520723','南京江宁镇宁桥北路','2','01000189','台','1000.0000','4','0','2010-5-24 10:37:17','' union all

select'','330A0000','无锡开锋路小吏巷','3410052400009811','2010-5-24 10:33:18','RMB','0','测试接口(无锡仓)','刘拓','18602520723','无锡开锋路小吏巷','1','01000188','台','1000.0000','3','0','2010-5-24 10:37:17','' union all

select'','330A0000','无锡开锋路小吏巷','3410052400009811','2010-5-24 10:33:18','RMB','0','测试接口(无锡仓)','刘拓','18602520723','无锡开锋路小吏巷','2','01000189','台','1000.0000','7','0','2010-5-24 10:37:17',''

--方法一：

;with SOMaster as(

select distinct SendID,

ChannelsID,

DeliveryLoc,

MsgNo,

MsgDate,

Coin,

Amt,

Remark,

ContactName,

ContactTel,

ContactAddress from @t)

select Tag,

Parent,

[SOMasters!1], -- empty root element

[SOMaster!2!SendID!ELEMENT],

[SOMaster!2!ChannelsID!ELEMENT],

[SOMaster!2!DeliveryLoc!ELEMENT],

[SOMaster!2!MsgDate!ELEMENT],

[SOMaster!2!Coin!ELEMENT],

[SOMaster!2!Amt!ELEMENT],

[SOMaster!2!Remark!ELEMENT],

[SOMaster!2!ContactName!ELEMENT],

[SOMaster!2!ContactTel!ELEMENT],

[SOMaster!2!ContactAddress!ELEMENT],

[SODetails!3!ELEMENT],

[SODetail!4!ItemNo!ELEMENT],

[SODetail!4!PartNo!ELEMENT],

[SODetail!4!Unit!ELEMENT],

[SODetail!4!Qty_BY!ELEMENT],

[SODetail!4!SubAmt_BY!ELEMENT],

[SODetail!4!DeliveryDate_BY!ELEMENT],

[SODetail!4!ItemRemark_BY!ELEMENT]

FROM (

SELECT

0 as sort,

1 AS Tag,

NULL AS Parent,

NULL AS 'SOMasters!1', -- empty root element

NULL AS 'SOMaster!2!SendID!ELEMENT',

NULL AS 'SOMaster!2!ChannelsID!ELEMENT',

NULL AS 'SOMaster!2!DeliveryLoc!ELEMENT',

NULL AS 'SOMaster!2!MsgNo!ELEMENT',

NULL AS 'SOMaster!2!MsgDate!ELEMENT',

NULL AS 'SOMaster!2!Coin!ELEMENT',

NULL AS 'SOMaster!2!Amt!ELEMENT',

NULL AS 'SOMaster!2!Remark!ELEMENT',

NULL AS 'SOMaster!2!ContactName!ELEMENT',

NULL AS 'SOMaster!2!ContactTel!ELEMENT',

NULL AS 'SOMaster!2!ContactAddress!ELEMENT',

NULL AS 'SODetails!3!ELEMENT',

NULL AS 'SODetail!4!ItemNo!ELEMENT',

NULL AS 'SODetail!4!PartNo!ELEMENT',

NULL AS 'SODetail!4!Unit!ELEMENT',

NULL AS 'SODetail!4!Qty_BY!ELEMENT',

NULL AS 'SODetail!4!SubAmt_BY!ELEMENT',

NULL AS 'SODetail!4!DeliveryDate_BY!ELEMENT',

NULL AS 'SODetail!4!ItemRemark_BY!ELEMENT'

UNION ALL

SELECT

ROW_NUMBER() over(order by ChannelsID)*100 as sort,

2 AS Tag,

1 AS Parent,

NULL

,SendID

,ChannelsID

,DeliveryLoc

,MsgNo

,MsgDate

,Coin

,ltrim(Amt)

,Remark

,ContactName

,ContactTel

,ContactAddress

,NULL

FROM

SOMaster

UNION ALL

SELECT

ROW_NUMBER() over(order by ChannelsID)*100+1 as sort,

3 AS Tag,

2 AS Parent,

NULL

,NULL

from SOMaster

UNION ALL

SELECT

dense_rank() over(order by ChannelsID)*100+2 as sort,

4 AS Tag

,3 AS Parent

, NULL

,SendID

,ChannelsID

,DeliveryLoc

,MsgNo

,MsgDate

,Coin

,ltrim(Amt)

,Remark

,ContactName

,ContactTel

,ContactAddress

,null

,ItemNo

,PartNo

,Unit

,ltrim(Qty_BY)

,ltrim(SubAmt_BY)

,DeliveryDate_BY

,ItemRemark_BY

from @t

) a

order by sort ,'SODetail!4!ItemNo!ELEMENT',Parent

FOR XML EXPLICIT

--方法二：

;with SOMaster as(

select distinct SendID,

ChannelsID,

DeliveryLoc,

MsgNo,

MsgDate,

Coin,

Amt,

Remark,

ContactName,

ContactTel,

ContactAddress from @t)

SELECT

1 AS Tag,

NULL AS Parent,

NULL AS 'SOMasters!1', -- empty root element

0 AS 'SOMasters!1!Sort!hide',

NULL AS 'SOMaster!2!SendID!ELEMENT',

NULL AS 'SOMaster!2!ChannelsID!ELEMENT',

NULL AS 'SOMaster!2!DeliveryLoc!ELEMENT',

NULL AS 'SOMaster!2!MsgNo!ELEMENT',

NULL AS 'SOMaster!2!MsgDate!ELEMENT',

NULL AS 'SOMaster!2!Coin!ELEMENT',

NULL AS 'SOMaster!2!Amt!ELEMENT',

NULL AS 'SOMaster!2!Remark!ELEMENT',

NULL AS 'SOMaster!2!ContactName!ELEMENT',

NULL AS 'SOMaster!2!ContactTel!ELEMENT',

NULL AS 'SOMaster!2!ContactAddress!ELEMENT',

NULL AS 'SODetails!3!ELEMENT',

NULL AS 'SODetail!4!ItemNo!ELEMENT',

NULL AS 'SODetail!4!PartNo!ELEMENT',

NULL AS 'SODetail!4!Unit!ELEMENT',

NULL AS 'SODetail!4!Qty_BY!ELEMENT',

NULL AS 'SODetail!4!SubAmt_BY!ELEMENT',

NULL AS 'SODetail!4!DeliveryDate_BY!ELEMENT',

NULL AS 'SODetail!4!ItemRemark_BY!ELEMENT'

UNION ALL

SELECT

2 AS Tag

,1 AS Parent

,NULL

,ROW_NUMBER() over(order by ChannelsID)*100

,SendID

,ChannelsID

,DeliveryLoc

,MsgNo

,MsgDate

,Coin

,ltrim(Amt)

,Remark

,ContactName

,ContactTel

,ContactAddress

,NULL

FROM

SOMaster

UNION ALL

SELECT

3 AS Tag

,2 AS Parent

,NULL

,ROW_NUMBER() over(order by ChannelsID)*100+1

,NULL

from SOMaster

UNION ALL

SELECT

4 AS Tag

,3 AS Parent

, NULL

,dense_rank() over(order by ChannelsID)*100+2

,SendID

,ChannelsID

,DeliveryLoc

,MsgNo

,MsgDate

,Coin

,ltrim(Amt)

,Remark

,ContactName

,ContactTel

,ContactAddress

,null

,ItemNo

,PartNo

,Unit

,ltrim(Qty_BY )

,ltrim(SubAmt_BY)

,DeliveryDate_BY

,ItemRemark_BY

from @t

order by 'SOMasters!1!Sort!hide' ,'SODetail!4!ItemNo!ELEMENT',Parent

FOR XML EXPLICIT

输出结果：

<DeliveryLoc>无锡开锋路小吏巷</DeliveryLoc>

<ContactAddress>无锡开锋路小吏巷</ContactAddress>

<Qty_BY>3</Qty_BY>

<SubAmt_BY>0</SubAmt_BY>

<DeliveryDate_BY>2010-05-24T10:37:17</DeliveryDate_BY>

<ItemRemark_BY></ItemRemark_BY>

</SODetail>

<Qty_BY>7</Qty_BY>

<SubAmt_BY>0</SubAmt_BY>

<DeliveryDate_BY>2010-05-24T10:37:17</DeliveryDate_BY>

<ItemRemark_BY></ItemRemark_BY>

</SODetail>

</SODetails>

</SOMaster>

<DeliveryLoc>南京江宁镇宁桥北路</DeliveryLoc>

<ContactAddress>南京江宁镇宁桥北路</ContactAddress>

<Qty_BY>6</Qty_BY>

<SubAmt_BY>0</SubAmt_BY>

<DeliveryDate_BY>2010-05-24T10:37:17</DeliveryDate_BY>

<ItemRemark_BY></ItemRemark_BY>

</SODetail>

<Qty_BY>4</Qty_BY>

<SubAmt_BY>0</SubAmt_BY>

<DeliveryDate_BY>2010-05-24T10:37:17</DeliveryDate_BY>

<ItemRemark_BY></ItemRemark_BY>

</SODetail>

</SODetails>

</SOMaster>

</SOMasters>

原帖地址：http://topic.csdn.net/u/20100727/13/82A4D095-9EF6-4B3E-BE41-DC3E71B37373.html