FOR XML EXPLICIT使用

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

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

      FROM

          SOMaster  

       UNION ALL

      SELECT

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

          3 AS Tag,

          2 AS Parent,

          NULL

          ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

          ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,NULL

         ,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

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

      FROM

          SOMaster  

       UNION ALL

      SELECT

          3 AS Tag

          ,2 AS Parent

          ,NULL

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

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,NULL

          ,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

 

 

/*

输出结果:

 

 

<SOMasters>

  <SOMaster>

    <SendID></SendID>

    <ChannelsID>330A0000</ChannelsID>

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

    <MsgDate>2010-05-24T10:33:18</MsgDate>

    <Coin>RMB</Coin>

    <Amt>0</Amt>

    <Remark>测试接口(无锡仓)</Remark>

    <ContactName>刘拓</ContactName>

    <ContactTel>18602520723</ContactTel>

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

    <SODetails>

      <SODetail>

        <ItemNo>1</ItemNo>

        <PartNo>01000188</PartNo>

        <Unit></Unit>

        <Qty_BY>3</Qty_BY>

        <SubAmt_BY>0</SubAmt_BY>

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

        <ItemRemark_BY></ItemRemark_BY>

      </SODetail>

      <SODetail>

        <ItemNo>2</ItemNo>

        <PartNo>01000189</PartNo>

        <Unit></Unit>

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

  <SOMaster>

    <SendID></SendID>

    <ChannelsID>34010019</ChannelsID>

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

    <MsgDate>2010-05-24T10:33:18</MsgDate>

    <Coin>RMB</Coin>

    <Amt>0</Amt>

    <Remark>测试接口(南京仓)</Remark>

    <ContactName>刘拓</ContactName>

    <ContactTel>18602520723</ContactTel>

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

    <SODetails>

      <SODetail>

        <ItemNo>1</ItemNo>

        <PartNo>01000188</PartNo>

        <Unit></Unit>

        <Qty_BY>6</Qty_BY>

        <SubAmt_BY>0</SubAmt_BY>

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

        <ItemRemark_BY></ItemRemark_BY>

      </SODetail>

      <SODetail>

        <ItemNo>2</ItemNo>

        <PartNo>01000189</PartNo>

        <Unit></Unit>

        <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

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值