insert row by row data in table via openxml stored procedure in MS SQL

Question

I m trying to insert the data VIA XML Format. And same the XML format defined as below & i wants to insert the field row by row in SQL Table. But, none of address is inserting in SQL Table, ONLY Customer information is inserting in Dummy Table.

<XML>
    <Customer>
    <NAME>YOGESH</NAME>
    <CONTACT>YOGESH SHARMA</CONTACT>
    <Mobile>123456789</Mobile>
    <Status>A</Status>
    <MALE>1</MALE>
    <Add>
       <ADD1>
             <Address>AHMEDABAD</Address>
             <State>GUJARAT</State>
             <City>AHMEDABAD</City>
             <Pincode>380016</Pincode>
      </ADD1>
      <ADD2>
             <Address>RAJKOT</Address>
             <State>GUJARAT</State>
             <City>RAJKOT</City>
             <Pincode>360001</Pincode>
     </ADD2>
    </Add>
    </Customer>
</XML>

MY SP AS BELOW :

ALTER PROCEDURE [dbo].[OPENXMLDUMMY]

 @xmlCustomer NTEXT

AS
 BEGIN
     DECLARE @DOC INT;
     EXEC sp_xml_preparedocument
          @DOC OUTPUT,
          @xmlCustomer;
     INSERT INTO Dummy
     (Name,
      Contact,
      Mobile,
      Status,
      Male,
      InsertDate
     )
            SELECT XML.NAME,
                   XML.Contact,
                   XML.Mobile,
                   XML.Status,
                   XML.Male,
                   GETDATE()
            FROM OPENXML(@DOC, '/XML/Customer', 2) WITH(Name VARCHAR(50), 
 Contact VARCHAR(75), Mobile BIGINT, Status VARCHAR(10), Male VARCHAR(10), 
 InsertDate DATETIME) XML;

    INSERT INTO DummyExtd
    (
      ID,
     Address,
     State,
     City,
     Pincode
    ) SELECT (SELECT ID FROM DUMMY WHERE Name = Name),
    XML.Address,
    XML.State,
    XML.City,
    XML.Pincode
    FROM OPENXML(@DOC, '/XML/Customer/Add',2) WITH (ID INT, Address 
    VARCHAR(50), State VARCHAR(50), City VARCHAR(50), Pincode INT) XML;

     EXEC sp_xml_removedocument @DOC;
 END;

So, i just want to insert the data as below format in SQL Tables:

ID  Name    Contact         Mobile    Status    Male    InsertDate
1   YOGESH  YOGESH SHARMA   123456789   A         1  2017-07-26 13:28:30.957

ID  Address     State   City        Pincode
1   AHMEDABAD   GUJARAT AHMEDABAD   380016
1   RAJKOT      GUJARAT RAJKOT      360001

So, what is the issue in my current stored procedure & needs to correct it.

Thanking you Yogesh

Answer 1

Here I made one demo for the same. Please look into this.

Firstly, I created two tables which are Customer(your table name Dummy) and Customer_Address(your table name DummyText). They are look like below snaps.

Table : Customer

Table : Customer_Address

Below is your updated store procedure.

ALTER PROCEDURE [dbo].[OPENXMLDUMMY]
 @xmlCustomer NTEXT
AS
 BEGIN
     DECLARE @DOC INT;
     Declare @CustId INT;
     EXEC sp_xml_preparedocument
          @DOC OUTPUT,
          @xmlCustomer;

     INSERT INTO Customer(Name, Contact, Mobile, Status, Male, InsertDate)
     SELECT XML.[NAME], XML.Contact, XML.Mobile, XML.Status, XML.Male, GETDATE() AS InsertDate
     FROM OPENXML(@DOC, '/XML/Customer', 2) WITH(NAME VARCHAR(50), CONTACT VARCHAR(75), Mobile BIGINT, Status VARCHAR(10), MALE VARCHAR(10), InsertDate DATETIME) XML;

     SET @CustId = SCOPE_IDENTITY()


    INSERT INTO Customer_Address(Cust_Id, Address, State, City, Pincode)
    SELECT @CustId AS Cust_Id, XML.Address, XML.State, XML.City, XML.Pincode
    FROM OPENXML(@DOC, '/XML/Customer/Add/ADD1',2) WITH 
    ( 
        Address VARCHAR(50), 
        State VARCHAR(50), 
        City VARCHAR(50),
        Pincode INT
    ) XML;

    INSERT INTO Customer_Address(Cust_Id, Address, State, City, Pincode)
    SELECT @CustId AS Cust_Id, XML.Address, XML.State, XML.City, XML.Pincode
    FROM OPENXML(@DOC, '/XML/Customer/Add/ADD2',2) WITH 
    ( 
        Address VARCHAR(50), 
        State VARCHAR(50), 
        City VARCHAR(50),
        Pincode INT
    ) XML;

    EXEC sp_xml_removedocument @DOC;

END

Using this procedure I executed your sample xml data and it looks like below entries in both tables.

Answer 2

Your issue is at

(SELECT ID FROM DUMMY WHERE Name = Name)

It returns all values from DUMMY table, and causes error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Solution:

-- After the first insert

Declare @DummyId int = scope_identity() -- get new inserted id from dummy

INSERT INTO DummyExtd
(
  ID,
 Address,
 State,
 City,
 Pincode
) SELECT 
@DummyId,    
XML.Address,
XML.State,
XML.City,
XML.Pincode
FROM OPENXML(@DOC, '/XML/Customer/Add',2) WITH 
( -- ID INT, --- Remove it, ID tag doesn't exist in your xml
    Address VARCHAR(50), 
    State VARCHAR(50), 
    City VARCHAR(50), 
    Pincode INT
) XML;