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

Dipak Delvadiya · Accepted Answer · 2017-07-26 09:36:15Z

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.

Thanks for your response, but we have not fixed about ADD1, ADD2 it would be no(s) ADD1..to ADD(n).

TriV · Accepted Answer · 2017-07-26 08:45:36Z

0

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;

edited Jul 26, 2017 at 8:45

answered Jul 26, 2017 at 8:36

TriV

5,1582 gold badges12 silver badges18 bronze badges

1 Comment

Yogesh Sharma Over a year ago

Unable to insert address info in DummyExtd Table, null value inserted.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related