how to insert bulk XML to DB

Question

0.00/5 (No votes)

See more:

i need Bulk XML to insert Database suggest pls ,.

VB

SELECT [ID]
      ,[NUMBER]
      ,[NAME]
      ,[GROUPCODE]
      ,[CustomerType]
      ,[ISACTIVE]
      ,[SHORTNAME]
      ,[CURRENCY]
      ,[ADDR1]
      ,[ADDR2]
      ,[PHONE]
      ,[CITY]
      ,[STATE]
      ,[ZIP]
      ,[COUNTRY]
      ,[CONTACT]
      ,[EMAIL]
      ,[WEBSITE]
      ,[TERMSCODE]
      ,[CREDITLIMIT]
      ,[OUTSTANDINGBALANCE]
      ,[DATELASTMN]
  FROM [BB].[dbo].[Customer]
GO

and i have N number records in datatable i am convert XML file to bUlk insert to DB

Posted 25-Feb-13 23:02pm

RajaMuthaiahV

Updated 26-Feb-13 0:24am

v4

Add a Solution

Comments

Chris Reynolds (UK) 26-Feb-13 5:07am

You need to give a lot more detail if you want any help. How much XML? What database server are you looking to load it into? What is the structure of the XML, give a small sample? Do you have an existing DB schema or can you create a new one?

RajaMuthaiahV 26-Feb-13 6:47am

<documentelement> -<XML-Customer> <number>1100 <name>Bargain Mart - San Diego <groupcode>RTL <customertype>0 <isactive>1 <shortname>BMT-SD <currency>USD <addr1>Bargain Mart Plaza <addr2>Corner 182nd and 34th Street <PHONE>4084518981</PHONE> <city>chennai <state>CA <zip>45112 <country>USA <contact>Mr. Jose Grange <email>Jose@bargainmart.com <website>www.BargainMart.com <termscode>N30 <creditlimit>10000 <outstandingbalance>5177.85 <datelastmaintained>2/5/2013 12:00:00 AM </XML-Customer>

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RajaMuthaiahV · Accepted Answer · 2013-02-26T00:46:00

Store procedure
------------------

ALTER PROCEDURE [dbo].[SPInsertCustomerXML]
(
@strXMLCustomertxt text
)
AS
begin
delete from Customer
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXMLCustomertxt
Insert into Customer
Select
newID()
,NUMBER
,NAME
,GROUPCODE
,CustomerType
,ISACTIVE
,SHORTNAME
,CURRENCY
,ADDR1
,ADDR2
,PHONE
,CITY
,STATE
,ZIP
,COUNTRY
,CONTACT
,EMAIL
,WEBSITE
,TERMSCODE
,CREDITLIMIT
,OUTSTANDINGBALANCE
,DateLastMaintained
from OpenXml(@intPointer,'/DocumentElement/XML-Customer',2)
With (
NUMBER nvarchar(50) 'NUMBER',
NAME nvarchar(MAX) 'NAME',
GROUPCODE nvarchar(50) 'GROUPCODE',
CustomerType nvarchar(MAX) 'CustomerType',
ISACTIVE nvarchar(MAX) 'ISACTIVE',
SHORTNAME nvarchar(MAX) 'SHORTNAME',
CURRENCY nvarchar(MAX) 'CURRENCY',
ADDR1 nvarchar(MAX) 'ADDR1',
ADDR2 nvarchar(MAX) 'ADDR2',
PHONE nvarchar(MAX) 'PHONE',
CITY nvarchar(MAX) 'CITY',
STATE nvarchar(MAX) 'STATE',
ZIP nvarchar(MAX) 'ZIP',
COUNTRY nvarchar(MAX) 'COUNTRY',
CONTACT nvarchar(MAX) 'CONTACT',
EMAIL nvarchar(MAX) 'EMAIL',
WEBSITE nvarchar(MAX) 'WEBSITE',
TERMSCODE nvarchar(MAX) 'TERMSCODE',
CREDITLIMIT nvarchar(MAX) 'CREDITLIMIT',
OUTSTANDINGBALANCE nvarchar(MAX) 'OUTSTANDINGBALANCE',
DateLastMaintained nvarchar(MAX) 'DateLastMaintained'
)
exec sp_xml_removedocument @intPointer

end

sample XML
--------------

<documentelement>
<XML-Customer>
<number>1100
<name>Bargain Mart - San Diego
<groupcode>RTL
<customertype>0
<isactive>1
<shortname>BMT-SD
<currency>USD
<addr1>Bargain Mart Plaza
<addr2>Corner 182nd and 34th Street
<phone>4084518981
<city>chennai
<state>CA
<zip>45112
<country>USA
<contact>Mr. Jose Grange
<email>Jose@bargainmart.com
<website>www.BargainMart.com
<termscode>N30
<creditlimit>10000
<outstandingbalance>5177.85
<datelastmaintained>2/5/2013 12:00:00 AM
</XML-Customer>