How to insert data into two cumulative tables?

Question

I have 2 tables: tblProduct and tblTransaction.

Please look at below code, this is what I have tried so far:-

 '<ArrayOfProduct>
          <Product>
            <GetSetParameter>
              <Parameter>
                <PClientID>2</PClientID>
                <PParameterID>1</PParameterID>
              </Parameter>
              <Parameter>
                <PClientID>2</PClientID>
                <PParameterID>2</PParameterID>
              </Parameter>
            </GetSetParameter>
            <PProductName>Yellow</PProductName>
            <PDescription>rtrtret</PDescription>
            <PClientID>2</PClientID>
            <PFromdate>2018/02/20</PFromdate>
            <PTodate>2018/02/20</PTodate>
          </Product>
          <Product>
            <GetSetParameter>
              <Parameter>
                <PClientID>2</PClientID>
                <PParameterID>1</PParameterID>
              </Parameter>
              <Parameter>
                <PClientID>2</PClientID>
                <PParameterID>2</PParameterID>
              </Parameter>
              <Parameter>
                <PClientID>2</PClientID>
                <PParameterID>3</PParameterID>
              </Parameter>
            </GetSetParameter>
            <PProductName>green</PProductName>
            <PDescription>rtrtret</PDescription>
            <PClientID>2</PClientID>
            <PFromdate>2018/02/20</PFromdate>
            <PTodate>2018/02/20</PTodate>
          </Product>
        </ArrayOfProduct>' 

This is my data. I want to insert my data into 2 table product and parameter.first product data will insert ,and generated id will insert into parameter table.

Alter PROCEDURE xmlOrderInsert(

 @order nvarchar(max)
 )

  AS

DECLARE @index int, @OID int
EXEC sp_xml_preparedocument @index OUTPUT, @order

BEGIN TRANSACTION

insert into Tbl_ProductMaster    

 (ProductName, 

 ProductDescription,   

 ClientID ,   

 IsActive  ,

 Fromdate,      

 Todate    

 ) 
 select PProductName,PDescription,PClientID,'True' as PisActive ,PFromdate,PTodate       

 FROM  OPENXML (@index, '/ArrayOfProduct/Product',2)    WITH (  PProductName varchar(50),                          

 PDescription varchar(6),                          

 PClientID varchar(30),

 PFromdate datetime,  

 PTodate datetime    ) 
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -100 END

SET @OID = SCOPE_IDENTITY()
INSERT INTO [Tbl_ParameterTransact] ( ProdID,   ClientID,   ParameterID)

--(2,1,1,@OID)

SELECT  @OID AS ProductID,PClientID, PParameterID
 FROM OpenXml( @index, '/ArrayOfProduct/Product/GetSetParameter/Parameter',2)   WITH 

 ( PClientID int, PParameterID int ) 
  IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END

COMMIT TRANSACTION

EXEC sp_xml_removedocument @index SELECT @OID AS ProductID

but scope identity only fetching last inserted data. This is what I am getting.

And this is what I want:-

Answer 1

I'm not entirely sure what did you want to accomplish, but in order to capture the identity you need to do some kind of a loop

look at this example and let me know if that's what you were aiming to

        DECLARE @XML XML = 
        '<ArrayOfProduct>
                  <Product>
                    <GetSetParameter>
                      <Parameter>
                        <PClientID>2</PClientID>
                        <PParameterID>1</PParameterID>
                      </Parameter>
                      <Parameter>
                        <PClientID>2</PClientID>
                        <PParameterID>2</PParameterID>
                      </Parameter>
                    </GetSetParameter>
                    <PProductName>Yellow</PProductName>
                    <PDescription>rtrtret</PDescription>
                    <PClientID>2</PClientID>
                    <PFromdate>2018/02/20</PFromdate>
                    <PTodate>2018/02/20</PTodate>
                  </Product>
                  <Product>
                    <GetSetParameter>
                      <Parameter>
                        <PClientID>2</PClientID>
                        <PParameterID>1</PParameterID>
                      </Parameter>
                      <Parameter>
                        <PClientID>2</PClientID>
                        <PParameterID>2</PParameterID>
                      </Parameter>
                      <Parameter>
                        <PClientID>2</PClientID>
                        <PParameterID>3</PParameterID>
                      </Parameter>
                    </GetSetParameter>
                    <PProductName>green</PProductName>
                    <PDescription>rtrtret</PDescription>
                    <PClientID>2</PClientID>
                    <PFromdate>2018/02/20</PFromdate>
                    <PTodate>2018/02/20</PTodate>
                  </Product>
                </ArrayOfProduct>'

         SET NOCOUNT ON 
         IF OBJECT_ID ('tempdb..#Main') IS NOT NULL DROP TABLE #Main
         DECLARE @Tbl TABLE (XmlCol xml)
         INSERT INTO @Tbl
         SELECT @xml

         SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) Id ,
                DENSE_RANK () OVER (ORDER BY ProductName) dr,
                 * 
         INTO #Main
         FROM 
         (
         SELECT  
           g.p.value ('../../PProductName[1]', 'nvarchar(50)') ProductName
         , g.p.value ('../../PDescription[1]', 'nvarchar(6)') [Description]
         , g.p.value ('../../PClientID[1]', 'nvarchar(30)') ClientID
         , g.p.value ('../../PFromdate[1]', 'nvarchar(20)') Fromdate
         , g.p.value ('../../PTodate[1]', 'nvarchar(20)') Todate
         , g.p.value('./PParameterID[1]', 'int') ParameterID
          FROM @Tbl t
          CROSS apply xmlcol.nodes ('/ArrayOfProduct/Product/GetSetParameter/Parameter') g(p)
        ) P

          DECLARE @i INT = 1 
          DECLARE @Tbl_ProductMaster  TABLE (TransactionId INT IDENTITY (1,1) NOT NULL, ProductName nvarchar(50) , [Description] nvarchar(6) , ClientID nvarchar(30) , Fromdate nvarchar(20) ,  Todate nvarchar(20) , ParameterID int)
          DECLARE @Tbl_ParameterTransact TABLE (ProdID INT , ClientID nvarchar(30), ParameterID INT, TransactionId INT )

          WHILE @i <= (SELECT MAX(dr) FROM #Main)
            BEGIN
            DECLARE @Ident INT 
            DECLARE @Out TABLE (TransactionId INT , ClientID nvarchar(30) , ParameterID INT ,T_Id INT)
          INSERT INTO @Tbl_ProductMaster
          OUTPUT inserted.TransactionId, inserted.ClientID , inserted.ParameterID , inserted.TransactionId
          INTO  @Out
          SELECT ProductName , [Description] ,ClientID ,Fromdate , Todate ,  ParameterID 
          FROM #Main
          WHERE dr = @i

          SELECT @Ident = MIN(TransactionId)
          FROM @Out

          INSERT INTO @Tbl_ParameterTransact (ProdID, ClientID , ParameterID , TransactionId)
          SELECT @Ident , ClientID , ParameterID, T_Id
          FROM @Out

          DELETE FROM @Out
          SET @i +=1

          END

        SELECT M.TransactionId , M.ClientID , M.ParameterID , NULL IsActive , T.ProdID 
        FROM @Tbl_ProductMaster M
        LEFT JOIN @Tbl_ParameterTransact T
        ON M.TransactionId = T.TransactionId