Hello ,
Create one stored procedure as mentioned below
CREATE PROCEDURE spSavePurchase_Order_Products
(
@OrderInfo AS XML
)
AS
BEGIN
DECLARE @tblOrderInfo AS TABLE(Product NVARCHAR(MAX),Qty INT , Amount INT)
INSERT INTO @tblOrderInfo
SELECT
Orders.Orderinfo.value('@Product', 'NVARCHAR(MAX)') as Product,
Orders.Orderinfo.value('@Qty', 'NVARCHAR(MAX)') as Qty,
Orders.Orderinfo.value('@Amount', 'NVARCHAR(MAX)') as Amount
FROM @OrderInfo.nodes('/Orders/Order') as Orders(Orderinfo)
BEGIN TRAN
BEGIN TRY
INSERT INTO [Purchase Order](Name)
SELECT Product FROM @tblOrderInfo WHERE Product NOT IN (SELECT Name FROM [Purchase Order])
INSERT INTO [Purchase Order Products] (ProductId ,Qty , Amount )
SELECT PO.Id,tblO.Qty,tblO.Amount FROM @tblOrderInfo tblO inner join [Purchase Order] PO on tblO.Product=PO.Name
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
Create single XML string for all your orders and execute above procedure and pass that XML String
see below
DECLARE @X AS XML
SET @X='<Orders>
<Order Product="ProductA1" Qty="10" Amount="10"></Order>
<Order Product="ProductB1" Qty="20" Amount="100"></Order>
</Orders>'
EXEC spSavePurchase_Order_Products @X
Hope this helps if yes then accept and vote this solution
--RDBurmon