从存储过程中的输入参数创建XML

Question

我想在sqlserver2008中写storedProcedure，这个proc必须插入到一个表中（用户、角色、组…），另外一个proc插入到用户表中，我调用这个，需要写我的proc whit xml输入参数，并在这个中调用adduser proc，如何获取xml参数并在我的proc中使用？感谢

Answer 1

ALTER PROCEDURE[dbo]。[usp_SaveInvoice]--在此处添加存储过程的参数@XMLHD-XML，@XMLDT-XML作为开始声明@HDOUT INT声明@DTOUT INT声明@maxcode int创建表#MESSAGE（MID-SMALLINT，MMESSAGE-VARCHAR（200））

EXEC SP_XML_PREPAREDOCUMENT @HDOUT OUTPUT,@XMLHD
EXEC SP_XML_PREPAREDOCUMENT @DTOUT OUTPUT,@XMLDT

SELECT *into #hd FROM OPENXML(@HDOUT,'HEAD',1) 
  WITH(invoiceno VARCHAR(500),customercode INT,invDate Date);
SELECT * into #dt FROM OPENXML(@DTOUT,'SAVE/INVOIEC',1)
      WITH(No INT,ItemCode INT,Qty DECIMAL(18,2),Rate Decimal(18,2),Amount Decimal(18,2));
      IF EXISTS (SELECT invInvoiceNO FROM dbo.trnInvoiceHD WHERE invInvoiceNO IN (SELECT invoiceno FROM #hd) )
    BEGIN
    INSERT INTO #MESSAGE
     SELECT 1,'iNVOICE NO ALREADYEXIST'        
     SELECT * FROM #MESSAGE MESSAGE FOR XML AUTO,ELEMENTS XSINIL
    RETURN
    END
select @maxcode=isnull(max(invhdcode),0)+1 from dbo.trnInvoiceHD     
 begin try 
 begin transaction
insert into dbo.trnInvoiceHD(invhdcode,invInvoiceNO,invDate,invCusCode)
select @maxcode,invoiceno,invDate,customercode from #hd 
insert into dbo.trnInvoiceDt(invDtTrncode,invdtSlNo,invdtitemcode,invDtRate,invDtQty,invDtAmount)
select @maxcode,No,ItemCode,Rate,Qty,Amount from #dt 
     INSERT INTO #MESSAGE
     SELECT 1,'SUCCESSFULLY INSERTED'
   SELECT * FROM #MESSAGE MESSAGE FOR XML AUTO,ELEMENTS XSINIL
commit transaction
end try
begin Catch
rollback transaction
INSERT INTO #MESSAGE
     SELECT 1,ERROR_MESSAGE ()         
     SELECT * FROM #MESSAGE MESSAGE FOR XML AUTO,ELEMENTS XSINIL    
end Catch