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>’

 

image

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: