Click here to Skip to main content
12,699,123 members (25,136 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# C#4.0
i need Bulk XML to insert Database suggest pls ,.


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 26-Feb-13 0:02am
Updated 26-Feb-13 1:24am
v4
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?
raja muthaiah 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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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>
  Permalink  
v4

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 26 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100