Insert XML into SQL Table

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

alter procedure [dbo].[xmlToTable]

(@xmlstr ntext)

as

begin

declare @hDoc int

exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr

insert into tblCustomers(

CustomerID,

FirstName,

LastName,

Email,

Phone)

select

xml.CustomerID,

xml.FirstName,

xml.LastName,

xml.EmailAddress,

xml.PhoneNumber

from OPENXML(@hDoc,’/Customers’,2) –node text not attribute

with(

CustomerId int,

FirstName nvarchar(100),

LastName nvarchar(200),

EmailAddress nvarchar(200),

PhoneNumber nvarchar(100)

)xml

exec sp_xml_removedocument @hDoc

end

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s