Bulk insert using OpenXML

— Tables
CREATE TABLE Singer
(
Id int,
Name varchar(200)
)

CREATE TABLE SingerGenere
(
SingerId int,
GenereId int
)

CREATE TABLE Genere
(
ID int,
Name varchar(200)
)

Data

— Singers
INSERT INTO Singer
VALUES (1, ‘Joe’)

INSERT INTO Singer
VALUES (2, ‘MJ’)

INSERT INTO Singer
VALUES (3, ‘ACDC’)

— Genere
INSERT INTOh Genere
VALUES
(1, ‘Rock’)

INSERT INTO Genere
VALUES
(2, ‘POP’)

INSERT INTO Genere
VALUES
(3, ‘Heavy Metal’)

— Singer – Genere mapping
INSERT INTO SingerGenere
(SingerId, GenereId)
VALUES (1,1)

INSERT INTO SingerGenere
(SingerId, GenereId)
VALUES (2,2)

INSERT INTO SingerGenere
(SingerId, GenereId)
VALUES (3,3)

The stored procedure :-

CREATE PROCEDURE GetSingersGenere
(@SingerData XML)
AS
BEGIN
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@SingerData
IF OBject_id(‘SingerGenereTable’) IS NOT NULL
BEGIN
DROP TABLE SingerGenereTable
END
CREATE TABLE SingerGenereTable
(
SingerName varchar(200),
GenereName varchar(200)
)

INSERT INTO SingerGenereTable
(
SingerName,
GenereName
)
SELECT XMLSinger.SingerName, Genere.Name  FROM OpenXML (@hDoc,’/Singers/Singer’)
WITH (SingerName varchar(200)’text()’) XMLSinger
INNER JOIN Singer on XMLSinger.SingerName=Singer.Name
INNER JOIN SingerGenere ON SingerGenere.SingerId = Singer.Id
INNER JOIN Genere ON Genere.Id = SingerGenere.GenereId
SELECT * FROM SingerGenereTable
END

Executing the Stored procedure

EXEC GetSingersGenere1
‘<Singers>
<Singer>
Joe
</Singer>
<Singer>
ACDC
</Singer>
</Singers>’