DataForXmlHC(zml)

USE [lab17025(MKLTK)]
GO
/****** Object: StoredProcedure [dbo].[DataForXmlHC] Script Date: 2019-10-05 20:39:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DataForXmlHC]
@Text1 xml
AS
BEGIN

DECLARE @DataXml xml
DECLARE @docHandle int

--新建采购单 读取供应商信息
if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCGYXN'
BEGIN

set @DataXml=(
select tb_ClientSupplier.ContactFax,tb_ClientSupplierLXR.SPName,tb_ClientSupplierLXR.SPPhone from tb_ClientSupplier
left join tb_ClientSupplierLXR on tb_ClientSupplier.TreeID=tb_ClientSupplierLXR.TreeID
where tb_ClientSupplier.TreeID=@Text1.value('(root/TreeID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml

end

--耗材验收要求列表
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCARList'
begin

set @DataXml=(select tb_HC_AcceptanceRequirements.AID,tb_HC_AcceptanceRequirements.Item1,tb_HC_AcceptanceRequirements.Item2,
tb_HC_AcceptanceRequirements.HCAID,t_User.RealName AS Creator
from tb_HC_AcceptanceRequirements
left join t_User on tb_HC_AcceptanceRequirements.Creator=t_User.UserName
where HCAID=@Text1.value('(root/HCAID)[1]','int') for xml path, root('root'))

select @DataXml as DataXml
end

--新建耗材验收要求
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCARNew'
begin

INSERT INTO dbo.tb_HC_AcceptanceRequirements
(HCAID,Item1,Item2,Creator)
VALUES (@Text1.value('(root/HCAID)[1]','int')
,@Text1.value('(root/Item1)[1]','NVARCHAR(1024)'),
@Text1.value('(root/Item2)[1]','NVARCHAR(1024)')
,@Text1.value('(root/Creator)[1]','NVARCHAR(50)')
)

end

--修改耗材验收要求
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCARUpd'
begin

update dbo.tb_HC_AcceptanceRequirements set
Item1=@Text1.value('(root/Item1)[1]','NVARCHAR(1024)')
,Item2=@Text1.value('(root/Item2)[1]','NVARCHAR(1024)')
,Creator=@Text1.value('(root/Creator)[1]','NVARCHAR(50)')
where tb_HC_AcceptanceRequirements.AID=@Text1.value('(root/AID)[1]','int')

end

--删除耗材验收要求
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCARDel'
begin

delete from tb_HC_AcceptanceRequirements where AID=@Text1.value('(root/AID)[1]','int')
end

--耗材列表
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCLIST'
begin

set @DataXml=(
SELECT
t1.AID,
tdd.ItemName AS ObjectType,
t1.ObjectSnID,
t1.ObjectName,
t1.ObjectSpec,
t1.CountUnit,
t1.UnitPrice,
t1.UpperLimit,
t1.LowerLimit,
t1.Location,
t2.NodeName AS 'Supplier',
t1.ObjectPicPath,
t1.AttachPath,
t1.Remark,
tu.RealName AS Creator,
t1.CreateTime,
t3.StockQuantity AS 'QuantityExit',
t31.NodeName AS 'Supplier1'

FROM tb_HC AS t1
LEFT OUTER JOIN tb_Tree AS t2 ON t1.SupplierAID=t2.TreeID
LEFT OUTER JOIN tb_Tree AS t31 ON t1.SupplierAID1=t31.TreeID

LEFT OUTER JOIN tb_HC_StockExit AS t3 ON t1.AID=t3.HCAID
LEFT OUTER JOIN t_User AS tu ON t1.Creator=tu.UserName
LEFT OUTER JOIN tb_SYS_DD AS tdd ON CONVERT(INT,t1.ObjectType)=tdd.AID
where ISNULL(tdd.ItemName ,'')
+ISNULL(t1.ObjectName,'')
+ISNULL(t1.ObjectSpec,'')
+ISNULL(t1.CountUnit,'')
+ISNULL(t1.Location,'')+ISNULL(t1.Remark,'')
like '%'+ISNULL(@Text1.value('(root/KeyWord)[1]','VARCHAR(50)'),'')+'%'
for xml path, root('root'))

select @DataXml as DataXml
end

--单个耗材
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCSingle'
begin
set @DataXml=(
SELECT
t1.AID,
tdd.ItemName AS ObjectType,
t1.ObjectSnID,
t1.ObjectName,
t1.ObjectSpec,
t1.CountUnit,
t1.UnitPrice,
t1.UpperLimit,
t1.LowerLimit,
t1.Location,
t1.SupplierAID AS 'Supplier',
t1.ObjectPicPath,
t1.AttachPath,
t1.Remark,
tu.RealName AS Creator,
t1.CreateTime,
t3.StockQuantity AS 'QuantityExit',
t1.SupplierAID1 AS 'Supplier1'

FROM tb_HC AS t1
LEFT OUTER JOIN tb_Tree AS t2 ON t1.SupplierAID=t2.TreeID
LEFT OUTER JOIN tb_Tree AS t31 ON t1.SupplierAID1=t31.TreeID

LEFT OUTER JOIN tb_HC_StockExit AS t3 ON t1.AID=t3.HCAID
LEFT OUTER JOIN t_User AS tu ON t1.Creator=tu.UserName
LEFT OUTER JOIN tb_SYS_DD AS tdd ON CONVERT(INT,t1.ObjectType)=tdd.AID
where t1.AID= @Text1.value('(root/AID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml

end

--修改单个耗材
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCUPDSingle'
begin

update tb_HC set
ObjectSnID=@Text1.value('(root/ObjectSnID)[1]','VARCHAR(50)'),
ObjectType=@Text1.value('(root/ObjectType)[1]','VARCHAR(50)'),
ObjectName=@Text1.value('(root/ObjectName)[1]','VARCHAR(50)'),
ObjectSpec=@Text1.value('(root/ObjectSpec)[1]','VARCHAR(50)'),
CountUnit=@Text1.value('(root/CountUnit)[1]','VARCHAR(50)'),
UnitPrice=@Text1.value('(root/UnitPrice)[1]','decimal(10,2)'),
UpperLimit=@Text1.value('(root/UpperLimit)[1]','decimal(10,2)'),
LowerLimit=@Text1.value('(root/LowerLimit)[1]','decimal(10,2)'),
Location=@Text1.value('(root/Location)[1]','VARCHAR(50)'),
SupplierAID=@Text1.value('(root/SupplierAID)[1]','int'),
SupplierAID1=@Text1.value('(root/SupplierAID1)[1]','int'),
ObjectPicPath=@Text1.value('(root/ObjectPicPath)[1]','VARCHAR(1024)'),
AttachPath=@Text1.value('(root/AttachPath)[1]','VARCHAR(1024)'),
Remark=@Text1.value('(root/Remark)[1]','VARCHAR(1024)')
where AID=@Text1.value('(root/AID)[1]','int')

end

--采购单 耗材采购项明细
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='CGDHCLIST'
begin
set @DataXml=(
SELECT
tb_HC_OpDetails.AID,
tb_HC_OpDetails.HCAID,
tb_HC_OpDetails.SupplierAID,
isnull(tb_HC.ObjectName,tb_HC_OpDetails.ObjectName) AS ObjectName,
tb_HC.CountUnit,
tb_HC.ObjectSpec AS ModelType,
tb_HC_OpDetails.Remark AS Attention,
tb_HC_OpDetails.UnitPrice AS ItemMoney,
tb_HC_OpDetails.Quantity,
tb_HC_OpDetails.ItemMoney AS TotalMoney
FROM tb_HC_OpDetails
LEFT JOIN tb_HC ON tb_HC_OpDetails.HCAID=tb_HC.AID
WHERE tb_HC_OpDetails.OpAID=@Text1.value('(root/AID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml
end

--采购单03环节 验收 耗材验收项查看
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCYSLIST'
BEGIN
if not exists(SELECT * from tb_HC_OpDetails_Sub where MainAID=@Text1.value('(root/MainAID)[1]','int') AND HCMainAID=@Text1.value('(root/HCMainAID)[1]','int'))
begin

set @DataXml=(select 'false' AS check1,
@Text1.value('(root/MainAID)[1]','VARCHAR(50)') AS MainAID
,@Text1.value('(root/HCMainAID)[1]','VARCHAR(50)') AS HCMainAID
,tb_HC_AcceptanceRequirements.HCAID,
tb_HC_AcceptanceRequirements.AID AS YSAID
,tb_HC_AcceptanceRequirements.Item1,
tb_HC_AcceptanceRequirements.Item2
from
tb_HC_AcceptanceRequirements
where tb_HC_AcceptanceRequirements.HCAID=@Text1.value('(root/HCAID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml

end

else begin
set @DataXml=(select (case tb_HC_OpDetails_Sub.check1 when 1 then 'true' else 'false' end) AS check1
,tb_HC_OpDetails_Sub.MainAID
,tb_HC_OpDetails_Sub.HCMainAID
,tb_HC_OpDetails_Sub.HCAID
,tb_HC_OpDetails_Sub.YSAID
,tb_HC_OpDetails_Sub.Item1
,tb_HC_OpDetails_Sub.Item2
from tb_HC_OpDetails_Sub
where tb_HC_OpDetails_Sub.MainAID= @Text1.value('(root/MainAID)[1]','VARCHAR(50)')
and tb_HC_OpDetails_Sub.HCMainAID=@Text1.value('(root/HCMainAID)[1]','VARCHAR(50)')
for xml path, root('root'))

select @DataXml as DataXml

end

END
--采购单03环节 验收 耗材验收项 添加或修改
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='HCYSUPD'
begin

if not exists(
SELECT * from tb_HC_OpDetails_Sub where MainAID=@Text1.value('(root/MainAID)[1]','int') AND HCMainAID=@Text1.value('(root/HCMainAID)[1]','int'))
BEGIN
EXEC sp_xml_preparedocument @docHandle OUTPUT, @Text1
INSERT INTO tb_HC_OpDetails_Sub(MainAID,HCMAinAID,HCAID,YSAID,check1,Item1,Item2)
SELECT @Text1.value('(root/MainAID)[1]','int'),@Text1.value('(root/HCMainAID)[1]','int'),@Text1.value('(root/HCAID)[1]','int'),*
FROM OPENXML (@docHandle, '/root/row', 1)
WITH (

YSAID int,
check1 bit,
Item1 nvarchar(1000),
Item2 nvarchar(1000))
EXEC sp_xml_removedocument @docHandle

update tb_HC_OpDetails set ObjectName='已验收' where AID=@Text1.value('(root/HCMainAID)[1]','int')

END
ELSE
BEGIN

EXEC sp_xml_preparedocument @docHandle OUTPUT, @Text1
update tb_HC_OpDetails_Sub
SET tb_HC_OpDetails_Sub.check1=DS.check1
FROM OPENXML (@docHandle, '/root/row', 1)
WITH ( YSAID int,
check1 bit) as DS ,tb_HC_OpDetails_Sub
where tb_HC_OpDetails_Sub.MainAID=@Text1.value('(root/MainAID)[1]','int')
AND tb_HC_OpDetails_Sub.HCMainAID=@Text1.value('(root/HCMainAID)[1]','int')
AND tb_HC_OpDetails_Sub.YSAID=DS.YSAID
EXEC sp_xml_removedocument @docHandle

END

END

--采购单03环节 验收 耗材验收确认
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='YSXConCon'
BEGIN

update tb_HC_OpDetails set CountUnit1=@Text1.value('(root/con)[1]','VARCHAR(50)')
,OtherText2=@Text1.value('(root/Num1)[1]','VARCHAR(50)')
,OtherText3=@Text1.value('(root/Text1)[1]','VARCHAR(1024)')
where AID=@Text1.value('(root/AID)[1]','int')

END

--采购单生成
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='GenWord1'
BEGIN

set @DataXml=(
SELECT
ISNULL(tb_ClientSupplier.ObjectName,'') AS ObjectName--供应商名称：
,ISNULL(tb_ClientSupplier.Address,'') AS Address--供应商地址：
,ISNULL(tb_ClientSupplier.Telephone,'') AS Telephone--电话：
,ISNULL(tb_ClientSupplier.ContactFax,'') AS ContactFax--传真：
,ISNULL(tb_ClientSupplierLXR.SPName,'') AS SPName--联系人：
,ISNULL(tb_HC_Op.SnID,'') AS SnID--订单号：
,ISNULL(tb_Tree.NodeName,'') AS NodeName--部门：
,ISNULL(CONVERT(VARCHAR(10),tb_HC_Op.OpDate,120),'') AS Date1--日期：
,ISNULL(tb_HC_Op.OtherText1,'') AS OtherText1
,ISNULL(tb_HC_Op.OtherText2,'') AS OtherText2--需求人：
,ISNULL(tb_HC_Op.OtherText3,'') AS OtherText3--用途：
,ISNULL(tb_HC_Op.OtherText4,'') AS OtherText4
,ISNULL(tb_HC_Op.OtherText5,'') AS OtherText5--(委外)注意事项/特别说明：
,ISNULL(tb_HC_Op.OtherText7,'') AS OtherText7--(委外)用途：
,ISNULL(tb_HC_Op.OtherText8,'') AS OtherText8
,ISNULL(tb_HC_Op.OtherText9,'') AS OtherText9
,ISNULL(tb_HC_Op.ClosePerson0,'') AS ClosePerson0
,ISNULL(tb_HC_Op.OtherText10,'') AS OtherText10
,ISNULL(tb_HC_Op.OtherText11,'') AS OtherText11
,ISNULL(tb_HC_Op.ClosePerson,'') AS ClosePerson
,ISNULL(convert(varchar(10),tb_HC_Op.CloseTime,120),'') AS CloseTime
,ISNULL(tb_HC_Op.RealPerson,'') AS RealPerson--(委外)需求人：
,ISNULL(tb_HC_Op.TotalMoney,0) AS TotalMoney--PO Total：
,ISNULL(tb_HC_Op.Remark,'') AS Remark-- （委外),传递方式；注意事项/特别说明
,ISNULL(t_User.RealName,'') AS RealName-- 批准:
,ISNULL(convert(varchar(10),tb_HC_Op.ApprovalTime,120),'') AS Date2-- 日期:
FROM tb_HC_Op
LEFT JOIN tb_ClientSupplier ON CONVERT(INT,tb_HC_Op.OtherText1)=tb_ClientSupplier.TreeID
LEFT JOIN tb_Tree on tb_Tree.TreeID=CONVERT(INT,(
case tb_HC_Op.BuyApplyID when 2 then tb_HC_Op.OtherText7
else tb_HC_Op.Dept end
))
LEFT JOIN tb_ClientSupplierLXR ON tb_ClientSupplierLXR.AID=CONVERT(INT,(
case tb_HC_Op.BuyApplyID when 2 then tb_HC_Op.OtherText3
when 0 then tb_HC_Op.OtherText7
else tb_HC_Op.Dept end
))
LEFT JOIN t_User ON t_User.UserName=tb_HC_Op.ApprovaPerson
WHERE tb_HC_Op.AID=@Text1.value('(root/AID)[1]','INT')
for xml path, root('root'))

select @DataXml as DataXml

END

--(普通)采购单生成-采购项
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='GenWord2'
BEGIN

set @DataXml=(
SELECT
ROW_NUMBER() OVER(ORDER BY tb_HC_OpDetails.AID) AS td,
tb_HC_OpDetails.Quantity AS td,
ISNULL(tb_HC.CountUnit,'') AS td,
ISNULL(tb_HC.ObjectName,tb_HC_OpDetails.ObjectName) AS td,
ISNULL(tb_HC.ObjectSpec,'') AS td,
tb_HC_OpDetails.Remark AS td,
tb_HC_OpDetails.UnitPrice0 AS td,
tb_HC_OpDetails.UnitPrice AS td
FROM tb_HC_OpDetails
LEFT JOIN tb_HC ON tb_HC_OpDetails.HCAID=tb_HC.AID
WHERE tb_HC_OpDetails.OpAID=@Text1.value('(root/AID)[1]','int')
for xml raw('tr'),ELEMENTS )

select @DataXml as DataXml

END

--采购单生成-验收项
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='GenWord3'
BEGIN

set @DataXml=(
SELECT
DENSE_RANK() OVER(ORDER BY HCMainAID) AS td,
tb_HC.ObjectName AS td,
--ROW_NUMBER() OVER(PARTITION BY HCMainAID ORDER BY HCMainAID) AS td,
( CASE tb_HC_OpDetails_Sub.check1 WHEN 1 THEN '是' ELSE '否' END) AS td,
tb_HC_OpDetails_Sub.Item1 AS td,
tb_HC_OpDetails_Sub.Item2 AS td
FROM tb_HC_OpDetails_Sub
LEFT JOIN tb_HC ON tb_HC_OpDetails_Sub.HCAID=tb_HC.AID
WHERE tb_HC_OpDetails_Sub.MainAID=@Text1.value('(root/AID)[1]','int')
for xml raw('tr'),ELEMENTS )

select @DataXml as DataXml

END

--(委外)采购单生成-采购项
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='GenWord4'
BEGIN

set @DataXml=(
SELECT
ROW_NUMBER() OVER(ORDER BY tb_HC_OpDetails.AID) AS td,
[ObjectName] AS td,
[ObjectSpec] AS td,
[CountUnit] AS td,
[CountUnit1] AS td,
[UnitPrice0] AS td,
[Quantity0] AS td,
[ItemMoney0] AS td,
[OtherText4] AS td,
[OtherText5] AS td,
[OtherText1] AS td,
[Remark] AS td
FROM tb_HC_OpDetails

WHERE tb_HC_OpDetails.OpAID=@Text1.value('(root/AID)[1]','int')
for xml raw('tr'),ELEMENTS )

select @DataXml as DataXml

END

--(校准)采购单生成-采购项
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='GenWord5'
BEGIN

set @DataXml=(
SELECT
ROW_NUMBER() OVER(ORDER BY tb_HC_OpDetails.AID) AS td,
[ObjectName] AS td,
[ObjectSpec] AS td,
[CountUnit] AS td,
[CountUnit1] AS td,
[OtherText1] AS td,
[Remark] AS td,
[ItemMoney0] AS td
FROM tb_HC_OpDetails

WHERE tb_HC_OpDetails.OpAID=@Text1.value('(root/AID)[1]','int')
for xml raw('tr'),ELEMENTS )

select @DataXml as DataXml

END

--单个采购单
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='SingleRe'
BEGIN

set @DataXml=(

select * from tb_HC_Op where AID=@Text1.value('(root/AID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml

END

--校准采购单 校准采购项明细
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='CGDJZLIST'
begin
set @DataXml=(
SELECT
tb_HC_OpDetails.AID,
tb_HC_OpDetails.HCAID,
tb_HC_OpDetails.OtherText1,
tb_HC_OpDetails.ObjectName,
tb_HC_OpDetails.ObjectSpec,
tb_HC_OpDetails.CountUnit,
tb_HC_OpDetails.CountUnit1,
tb_HC_OpDetails.OtherText1 AS sOtherText1,
tb_HC_OpDetails.Remark AS Remark1,
tb_HC_OpDetails.ItemMoney
FROM tb_HC_OpDetails
WHERE tb_HC_OpDetails.OpAID=@Text1.value('(root/AID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml
end

--委外采购单 委外采购项明细
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='CGDWWLIST'
begin
set @DataXml=(
SELECT
*
FROM tb_HC_OpDetails
WHERE tb_HC_OpDetails.OpAID=@Text1.value('(root/AID)[1]','int')
for xml path, root('root'))

select @DataXml as DataXml
end

--委外采购单-采购项-工作单明细 显示
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='WSNLIST'
BEGIN

set @DataXml=(
select ISNULL(SnID,'') AS SnID
,ISNULl(Text110,'') AS Text110
,ISNULl(ClientAID,0) AS ClientAID
from td_YWZC_Main where AID= @Text1.value('(root/MainAID)[1]','VARCHAR(50)')

for xml path, root('root'))

select @DataXml as DataXml

end

--普通采购单-采购项-修改
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='PTCGDLISTUPD'
begin

UPDATE [dbo].[tb_HC_OpDetails]
SET [SupplierAID] = @Text1.value('(root/SupplierAID)[1]','int')
,[UnitPrice] = @Text1.value('(root/ItemMoney)[1]','decimal(10,2)')
,[Quantity] = @Text1.value('(root/Quantity)[1]','decimal(10,2)')
,[ItemMoney] =@Text1.value('(root/sTotalMoney)[1]','decimal(10,2)')
,[Remark] = @Text1.value('(root/sAttention)[1]','varchar(1000)')
,[ObjectName] = @Text1.value('(root/sObjectName)[1]','varchar(1000)')
,[ObjectSpec] = @Text1.value('(root/sModelType)[1]','varchar(1000)')
,[CountUnit] = @Text1.value('(root/sCountUnit)[1]','varchar(1000)')
,[Quantity0] = @Text1.value('(root/Quantity)[1]','decimal(10,2)')
,[UnitPrice0] =@Text1.value('(root/ItemMoney)[1]','decimal(10,2)')
,[ItemMoney0] =@Text1.value('(root/sTotalMoney)[1]','decimal(10,2)')
WHERE [tb_HC_OpDetails].AID= @Text1.value('(root/AID)[1]','int')

end

--委外采购单-采购项-修改
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='WWCGDLISTUPD'
begin

UPDATE [dbo].[tb_HC_OpDetails]
SET [SupplierAID] = @Text1.value('(root/MainAID)[1]','int')
,[UnitPrice] = @Text1.value('(root/UnitPrice)[1]','decimal(10,2)')
,[Quantity] = @Text1.value('(root/Quantity)[1]','decimal(10,2)')
,[ItemMoney] =@Text1.value('(root/ItemMoney)[1]','decimal(10,2)')
,[Remark] = @Text1.value('(root/Remark1)[1]','varchar(1000)')
,[OtherText1] =@Text1.value('(root/ClientName)[1]','varchar(1000)')
,[OtherText4] = @Text1.value('(root/WSN)[1]','varchar(50)')
,[OtherText5] = @Text1.value('(root/SnID)[1]','varchar(50)')
,[Quantity0] = @Text1.value('(root/Quantity)[1]','decimal(10,2)')
,[UnitPrice0] =@Text1.value('(root/UnitPrice)[1]','decimal(10,2)')
,[ItemMoney0] =@Text1.value('(root/ItemMoney)[1]','decimal(10,2)')
,[SupplierAID1] =@Text1.value('(root/MainAID)[1]','int')
,[SupplierAID0] =@Text1.value('(root/ClientAID)[1]','int')
,[ObjectName] =@Text1.value('(root/ObjectName)[1]','varchar(1000)')
,[ObjectSpec] = @Text1.value('(root/ObjectSpec)[1]','varchar(1000)')
,[CountUnit] = @Text1.value('(root/CountUnit)[1]','varchar(1000)')
,[CountUnit1] = @Text1.value('(root/CountUnit1)[1]','varchar(1000)')
WHERE [tb_HC_OpDetails].AID= @Text1.value('(root/AID)[1]','int')

end

--校准采购单-采购项-修改
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='JZCGDLISTUPD'
begin

UPDATE [dbo].[tb_HC_OpDetails]
SET
[ItemMoney] =@Text1.value('(root/ItemMoney)[1]','decimal(10,2)')
,[Remark] = @Text1.value('(root/Remark1)[1]','varchar(1000)')
,[OtherText1] = @Text1.value('(root/sOtherText1)[1]','varchar(1000)')
,[ItemMoney0] =@Text1.value('(root/ItemMoney)[1]','decimal(10,2)')
,[ObjectName] =@Text1.value('(root/ObjectName)[1]','varchar(1000)')
,[ObjectSpec] = @Text1.value('(root/ObjectSpec)[1]','varchar(1000)')
,[CountUnit] = @Text1.value('(root/CountUnit)[1]','varchar(1000)')
,[CountUnit1] = @Text1.value('(root/CountUnit1)[1]','varchar(1000)')
WHERE [tb_HC_OpDetails].AID= @Text1.value('(root/AID)[1]','int')

end

--校准采购单-切换设备显示设备信息
else if(select @Text1.value('(root/Type)[1]','VARCHAR(50)'))='SBInfo'
begin

set @DataXml=(
SELECT ISNULL(tb_Tree.NodeName,'') AS ObjectName,
ISNULL(tb_Tree.NodeSymbol,'') AS ObjectSpec,
ISNULL(tb_Equip.EquipName,'') AS CountUnit,
ISNULL(tb_Equip.OtherText8,'') AS CountUnit1,
ISNULL(tb_Equip.OtherText1,'') AS sOtherText1
from tb_Equip
LEFT JOIN tb_Tree ON tb_Equip.EquipTreeID=tb_Tree.TreeID
where tb_Equip.EquipTreeID=@Text1.value('(root/TreeID)[1]','int')

for xml path, root('root'))

select @DataXml as DataXml
end

end