Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cismm
Starting Member
3 Posts |
 Posted - 2008-12-14 : 04:32:16
|
| Hello,I need to load very large XML files in SQL Server 2005 (or 2008). These XML files are in an Excel format. I’m wondering if I should use XML Bulk Load (SQLXML 4.0) as introduced here: http://msdn.microsoft.com/en-us/library/ms171721(SQL.90).aspx?n=0. Or should I use the OPENXML (http://msdn.microsoft.com/en-us/library/ms186918.aspx) and INSERT statements. The XML files could be as large as 50MB, 500MB, or 20GB. The XML will be formatted like Excel XML, for example:<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"></OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"></ExcelWorkbook> <Table ss:ExpandedColumnCount="435" ss:ExpandedRowCount="15" x:FullColumns="1" x:FullRows="1"> <Row> <Cell ss:StyleID="s21"> <Data ss:Type="String">Name</Data> <Comment ss:Author="mike"> <ss:Data xmlns="http://www.w3.org/TR/REC-html40"> <Font html:Face="Tahoma" html:Size="8" html:Color="#000000"></Font> </ss:Data> </Comment> </Cell> <Cell ss:StyleID="s21"> <Data ss:Type="String">Michael</Data> <Comment ss:Author="mike"> <ss:Data xmlns="http://www.w3.org/TR/REC-html40"> <Font html:Face="Tahoma" html:Size="8" html:Color="#000000"></Font> </ss:Data> </Comment> </Cell> </Row> </Table> ...Does anyone have experience loading this kind of XML format using the Bulk Loader?Thanks,Mike =:-) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-14 : 12:37:10
|
| http://msdn.microsoft.com/en-us/library/ms191184.aspx |
 |
|
|
cismm
Starting Member
3 Posts |
Posted - 2008-12-15 : 02:58:14
|
quote:
Originally posted by visakh16
http://msdn.microsoft.com/en-us/library/ms191184.aspx
Thank you for the link. I have come across many links on MSDN, however never one that explicitly refers to the Excel formatted XML as I refer to in my post example.Anyone else have info where they used this specific formatted XML:<?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"xmlns:o="urn:schemas-microsoft-com:office:office"xmlns:x="urn:schemas-microsoft-com:office:excel"xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"......Thanks,Mike =:-) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-15 : 03:38:41
|
What is your goal? To import the data embedded in the XML file?Use the new version 12 "JET" provider.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
cismm
Starting Member
3 Posts |
Posted - 2008-12-15 : 03:49:39
|
quote:
Originally posted by Peso
What is your goal? To import the data embedded in the XML file?Use the new version 12 "JET" provider.
E 12°55'05.63"N 56°04'39.26"
Yes, to import the data in the file. I will look into the version 12 "JET" provider. Is this the same JET provider like for MS Access DB's? or something else? I found this: http://www.eggheadcafe.com/software/aspnet/32261310/import-excel-sheet-into-a.aspxThanks,Mike =:-) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Excel XML with XML Bulk Load OR OPENXML / INSERT ?