stanl
01-20-2007, 11:04 AM
Bear with me on this. May not be advanced to you as you read, but is to me. [output files are in attached zip]
1. I can persist a specific range in an Excel file as XML with [for example]
Range("B19:G64").Value(xlRangeValueMSPersistXML) to a file which will contain an xml schema and can be treated as a normal recordset. However, even with row headers in the range, the persisted fields are referred to as col1...col2...col[n], but will reference an rs:field value [rngxml.xml in attached zip]
2. SQL Server 2005 supports the bulk load COM Object for xml files, so a sample code snippet would look like
Set oBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.2.0")
oBL.ConnectionString = "provider=SQLOLEDB.1;server=(local)\VSdotNet;" & _
"database=test;Trusted_Connection=Yes;"
oBL.KeepIdentity = False
oBL.Execute "[xsd/schema file]", "[xml file]"
3. [this would be called an AAARRRGGGHH!!!] Bulk inserts require that a schema be included along with the xml file. Problem is - by definition a persisted XML file includes a schema [think of Pink Floyd -"if you don't eat your meat, how can you have any pudding, and how can you have any pudding, if you don't eat your meat?"]
4. I actually found a Google hit for SQL Bulk Inserts and persisted recordsets, but Page Cannot Be Found.
5. I did think 'Aha, send the schema as ""' - No, if you come they won't build it.
6. So, given the persisted xml rngxml.xml - bulk insert it into a table named Cars in an SQL Server 2005 Table, so that when you later issue "SELECT * FROM Cars;" using the SQLOLEDB Provider and re-persist that file as output.xml [in attached zip] - a fully qualified recordset with field names.
.... Oh and #6 implies not having to manually create a schema.
Stan
1. I can persist a specific range in an Excel file as XML with [for example]
Range("B19:G64").Value(xlRangeValueMSPersistXML) to a file which will contain an xml schema and can be treated as a normal recordset. However, even with row headers in the range, the persisted fields are referred to as col1...col2...col[n], but will reference an rs:field value [rngxml.xml in attached zip]
2. SQL Server 2005 supports the bulk load COM Object for xml files, so a sample code snippet would look like
Set oBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.2.0")
oBL.ConnectionString = "provider=SQLOLEDB.1;server=(local)\VSdotNet;" & _
"database=test;Trusted_Connection=Yes;"
oBL.KeepIdentity = False
oBL.Execute "[xsd/schema file]", "[xml file]"
3. [this would be called an AAARRRGGGHH!!!] Bulk inserts require that a schema be included along with the xml file. Problem is - by definition a persisted XML file includes a schema [think of Pink Floyd -"if you don't eat your meat, how can you have any pudding, and how can you have any pudding, if you don't eat your meat?"]
4. I actually found a Google hit for SQL Bulk Inserts and persisted recordsets, but Page Cannot Be Found.
5. I did think 'Aha, send the schema as ""' - No, if you come they won't build it.
6. So, given the persisted xml rngxml.xml - bulk insert it into a table named Cars in an SQL Server 2005 Table, so that when you later issue "SELECT * FROM Cars;" using the SQLOLEDB Provider and re-persist that file as output.xml [in attached zip] - a fully qualified recordset with field names.
.... Oh and #6 implies not having to manually create a schema.
Stan