xml是什么入参格式 存储过程_xml 形式的存储过程

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

create procedure [dbo].[MachiningPayedGetListByCondition]

@CustomerName varchar(50),

@CustomerPhone varchar(50),

@PactNumber varchar(50),

@Ispay bit

as

begin

set nocount on;

SELECT

dp.ID as 'MachiningPayed/@ID',

Orders.ID AS 'MachiningPayed/Order/@ID',

-- Task --

Orders.Task AS 'MachiningPayed/Order/Task/@ID',

Tasks.OrderState as 'MachiningPayed/Order/Task/OrderState',

Tasks.UpdateDate as 'MachiningPayed/Order/Task/UpdateDate',

Tasks.CustomerName as 'MachiningPayed/Order/Task/CustomerName',

Tasks.CustomerPhone as 'MachiningPayed/Order/Task/CustomerPhone',

Tasks.CellPhone as 'MachiningPayed/Order/Task/CellPhone',

Tasks.CustomerAddress as 'MachiningPayed/Order/Task/CustomerAddress',

Tasks.ForemanName as 'MachiningPayed/Order/Task/ForemanName',

Tasks.MonitorName as 'MachiningPayed/Order/Task/MonitorName',

Tasks.DesignerName as 'MachiningPayed/Order/Task/DesignerName',

Tasks.Store as 'MachiningPayed/Order/Task/Store',

Tasks.PactNumber as 'MachiningPayed/Order/Task/PactNumber',

Tasks.CreateDate as 'MachiningPayed/Order/Task/CreateDate',

Tasks.PlanDisclosureDate as 'MachiningPayed/Order/Task/PlanDisclosureDate',

Tasks.PlanHiddenCheckDate as 'MachiningPayed/Order/Task/PlanHiddenCheckDate',

Tasks.PlanMidCheckDate as 'MachiningPayed/Order/Task/PlanMidCheckDate',

Tasks.PlanEndCheckDate as 'MachiningPayed/Order/Task/PlanEndCheckDate',

-- Task End--

Orders.PactNumber AS 'MachiningPayed/Order/PactNumber',

Orders.CustomerName AS 'MachiningPayed/Order/CustomerName',

Orders.CustomerPhone AS 'MachiningPayed/Order/CustomerPhone',

Orders.CellPhone AS 'MachiningPayed/Order/CellPhone',

Orders.CustomerType AS 'MachiningPayed/Order/CustomerType',

Orders.NativeProvince AS 'MachiningPayed/Order/NativeProvince',

Orders.Address AS 'MachiningPayed/Order/Address',

Orders.Store AS 'MachiningPayed/Order/Store',

Orders.SalesDepartment AS 'MachiningPayed/Order/SalesDepartment',

Orders.Salesman AS 'MachiningPayed/Order/Salesman',

Orders.DepositMoney AS 'MachiningPayed/Order/DepositMoney',

Orders.Activitys AS 'MachiningPayed/Order/Activitys',

Orders.SetLocationType AS 'MachiningPayed/Order/SetLocationType',

Orders.DistanceFeesType AS 'MachiningPayed/Order/DistanceFeesType',

Orders.Foreman AS 'MachiningPayed/Order/Foreman',

Orders.Monitor AS 'MachiningPayed/Order/Monitor',

Orders.AreaSize AS 'MachiningPayed/Order/AreaSize',

Orders.RoomType AS 'MachiningPayed/Order/RoomType',

Orders.PlanDisclosureDate AS 'MachiningPayed/Order/PlanDisclosureDate',

Orders.ActualDisclosureDate AS 'MachiningPayed/Order/ActualDisclosureDate',

Orders.PlanHiddenCheckDate AS 'MachiningPayed/Order/PlanHiddenCheckDate',

Orders.ActualHiddenCheckDate AS 'MachiningPayed/Order/ActualHiddenCheckDate',

Orders.ActualMidCheckDate AS 'MachiningPayed/Order/ActualMidCheckDate',

Orders.PlanEndCheckDate AS 'MachiningPayed/Order/PlanEndCheckDate',

Orders.ActualEndCheckDate AS 'MachiningPayed/Order/ActualEndCheckDate',

Orders.DistanceFeesType AS 'MachiningPayed/Order/DistanceFeesType',

Orders.SetMealTemplate AS 'MachiningPayed/Order/SetMealTemplate/@ID',

SetMealTemplates.Name AS 'MachiningPayed/Order/SetMealTemplate/@Name',

SetMealTemplates.Description AS 'MachiningPayed/Order/SetMealTemplate/Description',

Orders.SetMealLevel as 'MachiningPayed/Order/SetMealLevel',

Orders.SetCount AS 'MachiningPayed/Order/SetCount',

Orders.IncreaseCount AS 'MachiningPayed/Order/IncreaseCount',

Orders.Managementfees AS 'MachiningPayed/Order/Managementfees',

Orders.Taxes AS 'MachiningPayed/Order/Taxes',

Orders.Distancefees AS 'MachiningPayed/Order/Distancefees',

Orders.CustomerResourcesDetail as 'MachiningPayed/Order/CustomerResourcesDetail',

Orders.CustomerResources as 'MachiningPayed/Order/CustomerResources',

Orders.OrderState AS 'MachiningPayed/Order/OrderState',

--  'Created' AS 'Order/OrderState',

osr.Title AS 'MachiningPayed/Order/OrderStateTitle',

Orders.CreateDate AS 'MachiningPayed/Order/CreateDate',

Orders.CompleteDate AS 'MachiningPayed/Order/CompleteDate',

Orders.SendOrderDate AS 'MachiningPayed/Order/SendOrderDate',

Orders.Sender AS 'MachiningPayed/Order/Sender',

Orders.IsDisclosure AS 'MachiningPayed/Order/IsDisclosure',

Orders.IsHiddenCheck AS 'MachiningPayed/Order/IsHiddenCheck',

Orders.IsMidCheck AS 'MachiningPayed/Order/IsMidCheck',

Orders.IsEndCheck AS 'MachiningPayed/Order/IsEndCheck',

Orders.DisclosureText AS 'MachiningPayed/Order/DisclosureText',

Orders.HiddenCheckText AS 'MachiningPayed/Order/HiddenText',

Orders.MidCheckText AS 'MachiningPayed/Order/MidCheckText',

Orders.EndCheckText AS 'MachiningPayed/Order/EndCheckText',

Orders.IsForemanMidSettle AS 'MachiningPayed/Order/IsForemanMidSettle',

Orders.IsForemanEndSettle AS 'MachiningPayed/Order/IsForemanEndSettle',

Orders.OrigCount AS 'MachiningPayed/Order/OrigCount',

Orders.TotalCount AS 'MachiningPayed/Order/TotalCount',

Orders.IsRecovery AS 'MachiningPayed/Order/IsRecovery',

Orders.Invoice AS 'MachiningPayed/Order/Invoice',

Orders.Remark AS 'MachiningPayed/Order/Remark',

Orders.Area AS 'MachiningPayed/Order/Area',

Orders.IncreaseA AS 'MachiningPayed/Order/IncreaseA',

Orders.IncreaseB AS 'MachiningPayed/Order/IncreaseB',

Orders.XCoordinate AS 'MachiningPayed/Order/XCoordinate',

Orders.YCoordinate AS 'MachiningPayed/Order/YCoordinate',

Users.ID as 'MachiningPayed/Order/Designer/@ID',

Users.LogName as 'MachiningPayed/Order/Designer/@LogName',

Users.LogPassword as 'MachiningPayed/Order/Designer/@LogPassword',

Users.UserName as 'MachiningPayed/Order/Designer/@UserName',

Users.Remark as 'MachiningPayed/Order/Designer/Remark',

-- 主材集合--

(SELECT

opi.ID AS '@ID',

opi.Remark AS '@Remark',

opi.Unit AS '@Unit',

opi.IsPayedUpdate AS '@IsPayedUpdate',

opi.IncreaseCount AS '@IncreaseCount',

opi.SetQuantity AS 'SetQuantity',

opi.IncreaseQuantity AS 'IncreaseQuantity',

opi.Product AS 'Product/@ID',

p.Name AS 'Product/Name',

p.Brand AS 'Product/Brand',

p.SupplierType AS 'Product/SupplierType',

p.Model AS 'Product/Model'

FROM OrderProductItems opi

inner join Products as p on p.id =opi.Product

WHERE opi.Parent = Orders.ID

FOR XML PATH('OrderProductItem'), TYPE

) AS 'MachiningPayed/Order/OrderProductItems',

-- 主材集合 End --

-- 工艺集合--

(SELECT

oci.ID AS '@ID',

oci.Unit AS '@Unit',

oci.IncreaseCount AS '@IncreaseCount',

oci.Crafts AS 'Crafts/@ID',

Crafts.Title AS 'Crafts/@Title',

Crafts.Material AS 'Crafts/@Material',

Crafts.Unit AS 'Crafts/@Unit',

Crafts.Description AS 'Crafts/@Description',

oci.SetQuantity AS 'SetQuantity',

oci.IncreaseQuantity AS 'IncreaseQuantity',

oci.Remark AS 'Remark'

FROM OrderCraftsItems oci

inner join Crafts on Crafts.ID = oci.Crafts

WHERE oci.Parent = Orders.ID

FOR XML PATH('OrderCraftsItems'), TYPE

) AS 'MachiningPayed/Order/OrderCraftsItemss',

-- 工艺集合 End --

dp.ActuallyPayed as 'MachiningPayed/ActuallyPayed',

dp.Receipt as 'MachiningPayed/Receipt',

dp.GetPayedDate as 'MachiningPayed/GetPayedDate',

dp.IsPayed as 'MachiningPayed/IsPayed',

dp.Remark as 'MachiningPayed/Remark',

dp.PayedType as 'MachiningPayed/PayedType'

from

MachiningPayed as dp

left join Orders on dp.Orders=Orders.id

inner join Tasks on Tasks.id = Orders.Task

inner join Users on orders.designer=Users.id

inner join OrderStateRelation osr on orders.OrderState = osr.OrderState

inner join SetMealTemplates on SetMealTemplates.ID=Orders.SetMealTemplate

where

dp.IsPayed=@Ispay and

Orders.CustomerName like '%'+coalesce(@CustomerName,Orders.CustomerName)+'%' and

Orders.CustomerPhone like '%'+coalesce(@CustomerPhone,Orders.CustomerPhone)+'%' and

Orders.PactNumber like '%'+coalesce(@PactNumber,Orders.PactNumber)+'%'

for xml path(''),root('ArrayOfMachiningPayed')

end