Click here to Skip to main content
11,708,312 members (71,140 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 25-Feb-13 23:02pm
Edited 26-Feb-13 0:24am
v4
Comments
Chris Reynolds (UK) at 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 at 26-Feb-13 6:47am
   
-<XML-Customer> 1100 Bargain Mart - San Diego RTL 0 1 BMT-SD USD Bargain Mart Plaza Corner 182nd and 34th Street <PHONE>4084518981</PHONE> chennai CA 45112 USA Mr. Jose Grange Jose@bargainmart.com www.BargainMart.com N30 10000 5177.85 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
--------------


<XML-Customer>
1100
Bargain Mart - San Diego
RTL
0
1
BMT-SD
USD
Bargain Mart Plaza
Corner 182nd and 34th Street
4084518981
chennai
CA
45112
USA
Mr. Jose Grange
Jose@bargainmart.com
www.BargainMart.com
N30
10000
5177.85
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
0 Maciej Los 235
1 Sergey Alexandrovich Kryukov 189
2 Mika Wendelius 180
3 OriginalGriff 143
4 Peter Leow 130
0 OriginalGriff 9,253
1 Sergey Alexandrovich Kryukov 8,562
2 CPallini 5,189
3 Maciej Los 4,966
4 Mika Wendelius 3,856


Advertise | Privacy | Mobile
Web03 | 2.8.150819.1 | Last Updated 26 Feb 2013
Copyright © CodeProject, 1999-2015
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