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

i m trying insert data via xml format. , same xml format defined below & wants insert field row row in sql table. but, none of address inserting in sql table, customer information 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 below :

alter procedure [dbo].[openxmldummy]   @xmlcustomer ntext   begin      declare @doc int;      exec sp_xml_preparedocument           @doc output,           @xmlcustomer;      insert dummy      (name,       contact,       mobile,       status,       male,       insertdate      )             select xml.name,                    xml.contact,                    xml.mobile,                    xml.status,                    xml.male,                    getdate()             openxml(@doc, '/xml/customer', 2) with(name varchar(50),   contact varchar(75), mobile bigint, status varchar(10), male varchar(10),   insertdate datetime) xml;      insert dummyextd     (       id,      address,      state,      city,      pincode     ) select (select id dummy name = name),     xml.address,     xml.state,     xml.city,     xml.pincode     openxml(@doc, '/xml/customer/add',2) (id int, address      varchar(50), state varchar(50), city varchar(50), pincode int) xml;       exec sp_xml_removedocument @doc;  end; 

so, want insert data below format in sql tables:

id  name    contact         mobile    status    male    insertdate 1   yogesh  yogesh sharma   123456789           1  2017-07-26 13:28:30.957  id  address     state   city        pincode 1   ahmedabad   gujarat ahmedabad   380016 1   rajkot      gujarat rajkot      360001 

so, issue in current stored procedure & needs correct it.

thanking yogesh

here made 1 demo same. please this.

firstly, created 2 tables customer(your table name dummy) , customer_address(your table name dummytext). below snaps.

table : customer

table : customer_address

below updated store procedure.

alter procedure [dbo].[openxmldummy]  @xmlcustomer ntext  begin      declare @doc int;      declare @custid int;      exec sp_xml_preparedocument           @doc output,           @xmlcustomer;       insert customer(name, contact, mobile, status, male, insertdate)      select xml.[name], xml.contact, xml.mobile, xml.status, xml.male, getdate() insertdate      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 customer_address(cust_id, address, state, city, pincode)     select @custid cust_id, xml.address, xml.state, xml.city, xml.pincode     openxml(@doc, '/xml/customer/add/add1',2)      (          address varchar(50),          state varchar(50),          city varchar(50),         pincode int     ) xml;      insert customer_address(cust_id, address, state, city, pincode)     select @custid cust_id, xml.address, xml.state, xml.city, xml.pincode     openxml(@doc, '/xml/customer/add/add2',2)      (          address varchar(50),          state varchar(50),          city varchar(50),         pincode int     ) xml;      exec sp_xml_removedocument @doc;  end 

using procedure executed sample xml data , looks below entries in both tables.