Click here to Skip to main content
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 OriginalGriff 517
1 CPallini 460
2 Sergey Alexandrovich Kryukov 358
3 George Jonsson 251
4 BillWoodruff 224
0 OriginalGriff 5,370
1 CPallini 4,360
2 Sergey Alexandrovich Kryukov 3,822
3 George Jonsson 3,037
4 Gihan Liyanage 2,435


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