Bulkload XML to SQL2005

I have hundreds of large (500meg) XML files I need to upload into SQL2005. I followed the example from http://support.microsoft.com/default.aspx/kb/316005/en-us with no major problems. Unfortunatly my data can be several fields deep within the xml file (sample below).

I'm created a vbs file to process the bulkload.

I'm not able to figure out how to create the mapping (schema) for this file structure. I'm trying to use a mapping schema simular to what is used in the example.

How do I modify the mapping schema for my format?

Please help educate me. I am a noob, so please keep it simple.

Thanks

Charles W

XML File

<ROOT>
<Customers>
<CustomerId><IDno><pdat>2111</pdat></IDno></CustomerId>
<CompanyName><pdat>2Sean Chai</pdat></CompanyName>
<City><pdat>NY</pdat></City>
</Customers>
<Customers>
<CustomerId><IDno><pdat>2112</pdat></IDno></CustomerId>
<CompanyName><pdat>2Tom Johnston</pdat></CompanyName>
<City><pdat>LA</pdat></City>
</Customers>
<Customers>
<CustomerId><IDno><pdat>2113</pdat></IDno></CustomerId>
<CompanyName><pdat>2Institute of Art 3</pdat></CompanyName>
</Customers>
</ROOT>

XSD File

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<xs:element name="ROOT" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="Customers"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Customers" sql:relation="Customer">
<xs:complexType>
<xs:sequence>
<xs:element ref="CustomerId" sql:field="CustomerId" />
<xs:element ref="CompanyName" sql:field="CompanyName" />
<xs:element minOccurs="0" ref="City" sql:field="City"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="CustomerId" type="xs:integer"/>
<xs:element name="CompanyName" type="xs:string"/>
<xs:element name="City" type="xs:NCName"/>
</xs:schema>

VBS

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=12T4581-CHUCK;database=MyDatabase;uid=XMLtest;pwd=XMLtest"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customer3.xsd", "c:\customers3.xml"
Set objBL = Nothing

FYI:

To solve my problem, I created a .net application to modify my xml file into a format that I can work with. It really turned out to be the best solution for me because I have to make some other modifications.

I can take a 600k xml file and stip out the data I need in under 1 minute. At this rate I can prep an entire years worth of data in under 15 minutes.

Now I need to master XSD files.....

Charles W

|||

This is the recommended way to achieve your mapping. Bulkload would not support your shape of having multiple layers of wrappers around your scalar type. So Bulkload would support:

<City>NY</City>

But not:

<City><pdat>NY</pdat></City>