Load XML data into SQL 2000

I recently had the need (read "somebody wanted a big favor") to load XML data into a SQL Server 2000 database. I came across an easy way for them (being a developer to push the data into SQL) per the article (http://support.microsoft.com/kb/316005).

After creating the table on SQL, you have to create a mapping file to tell it which fields go with which columns in the SQL database. The article above has a good basic but working example. Modify away.

Basically, I created a VB script as follows:
Note you can use localhost or replace with a SQL instance (default or named). You can also use paths relative to the executable.

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")objBL.ConnectionString = "provider=SQLOLEDB.1;data source=localhost;database=XMLImport;Integrated Security=SSPI"objBL.ErrorLogFile = "error.log"objBL.Execute "XML_Schema.xml", "XML_Data.xml"Set objBL = Nothing

The biggest "gotchas" were the case-sensitivity of the field names and it didn't like spaces either. Also, you had to have the right version to run the VB script. Since I did not, my workaround was to execute the script remotely.

List Table Rowcount and Size

Here's a handy way to get a list of table rowcounts and space used. If you've not used the sp_msforeachtable just plug in the question mark (?) as a token for the name of the table.

--create a temporary table to hold the results
CREATE TABLE #TableStats (
 [name] varchar(128),
 [rows] varchar(128),
 [reserved] varchar(128),
 [data] varchar(128),
 [index_size] varchar(128),
 [unused] varchar(128)
)
GO
--loop through each table and populate the temp table
--NOTE: we can't use table variables with the sp_msforeachtable
sp_msforeachtable "INSERT INTO #TableStats EXEC sp_spaceused '?'"

SELECT * FROM #TableStats
--ORDER BY LEN([rows]) DESC, [rows] DESC --sort by # of rows
ORDER BY LEN([reserved]) DESC, [reserved] DESC --sort by space used
DROP TABLE #TableStats
GO