Large XML documents into SQL database-CodePudding

Have a 4 gb large XML data files, there are multiple fields, how to put it saved to the database, each field in a column? For help

CodePudding user response:

Try to reference the first
Examples of Bulk Import and Export of XML Documents (SQL Server)

If need further help, best can post data format of XML file

CodePudding user response:

<? The XML version="1.0" encoding="utf-8"?>
- & lt; ! - Prosper Data Export - Data - Version: 1.0 - Generated: the 2012-02-14 T03: - have & gt;
- & lt; ! - Subject to the Prosper API service Terms of Use Agreement located at http://www.prosper.com/tools/APITermsOfUse.aspx - & gt;
- & lt; ProsperDataExport XMLNS: xsi="http://www.w3.org/2001/XMLSchema-instance" xsi: noNamespaceSchemaLocation="ProsperDataExport. XSD & gt;"
- & lt; Bids>
- & lt; Bid>
50.00 & lt;/Amount>
The 2006-02-21 T20: thou wouldest & lt;/CreationDate>
0 f12336634427919902fe93 & lt;/Key>
D6473365888221963456102 & lt;/ListingKey>
Completed
F4273365023760472DBD934 & lt;/MemberKey>
0.25000 & lt;/MinimumRate>
0.24500 & lt;/MinimumYield>
0.00 & lt;/ParticipationAmount>
Outbid
The 2006-02-21 T20: thou wouldest & lt;/ModifiedDate>

- & lt; Bid>
50.00 & lt;/Amount>
The 2006-02-21 T22:50:00 & lt;/CreationDate>
87 f333655285187004b2ee8 & lt;/Key>
D6473365888221963456102 & lt;/ListingKey>
Completed
F4273365023760472DBD934 & lt;/MemberKey>
50.00 & lt;/ParticipationAmount>
Winning
The 2006-02-21 T22:50:00 & lt;/ModifiedDate>

This is. Article 2 of these data in the XML files, how?

CodePudding user response:

='DECLARE @ x XML & lt; ? The XML version="1.0" encoding="utf-8"?> 
 
 
 50.00 & lt;/Amount> 
 The 2006-02-21 T20: thou wouldest & lt;/CreationDate> 
 0 f12336634427919902fe93 & lt;/Key> 
 D6473365888221963456102 & lt;/ListingKey> 
 Completed 
 F4273365023760472DBD934 & lt;/MemberKey> 
 0.25000 & lt;/MinimumRate> 
 0.24500 & lt;/MinimumYield> 
 0.00 & lt;/ParticipationAmount> 
 Outbid 
 The 2006-02-21 T20: thou wouldest & lt;/ModifiedDate> 
 
 
 50.00 & lt;/Amount> 
 The 2006-02-21 T22:50:00 & lt;/CreationDate> 
 87 f333655285187004b2ee8 & lt;/Key> 
 D6473365888221963456102 & lt;/ListingKey> 
 Completed 
 F4273365023760472DBD934 & lt;/MemberKey> 
 50.00 & lt;/ParticipationAmount> 
 Winning 
 The 2006-02-21 T22:50:00 & lt;/ModifiedDate> 
 
 
 
'

The SELECT T.C.V alue (' (Amount/text ()) [1] 'and' a decimal (18, 2) ') 
, T.C.V alue (' (CreationDate/text ()) [1] 'and' datetime) 
, T.C.V alue (' (Key/text ()) [1] 'and' varchar (50) ') 
, T.C.V alue (' (ListingKey/text ()) [1] 'and' varchar (50) ') 
, T.C.V alue (' (ListingStatus/text ()) [1] 'and' varchar (50) ') 
, T.C.V alue (' (MemberKey/text ()) [1] 'and' varchar (50) ') 
, T.C.V alue (' (MinimumRate/text ()) [1] 'and' a decimal (18, 5) ') 
, T.C.V alue (' (MinimumYield/text ()) [1] 'and' a decimal (18, 5) ') 
, T.C.V alue (' (ParticipationAmount/text ()) [1] 'and' a decimal (18, 2) ') 
, T.C.V alue (' (the Status/text ()) [1] 'and' varchar (50) ') 
, T.C.V alue (' (ModifiedDate/text ()) [1] 'and' datetime) 
The FROM @ x.n odes (' ProsperDataExport/Bids/Bid) t (c) 

/* 
(no column name) (no column name) (no column name) (no column name) (no column name) (no column name) (no column name) (no column name) (no column name) (no column name) (no names) 
50.00 the 2006-02-21 20:23:20. 000 0 f12336634427919902fe93 D6473365888221963456102 Completed F4273365023760472DBD934 0.25000 0.24500 0.00 Outbid the 2006-02-21 20:23:20. 000 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull