title: Importing multiple XML files into remote SQL Server table
body: <p>This is the gist of my client-provided XML files. Files are named as Rep[yr][mo]. I tried using a generated schema from Visual Studio to upload one into my DB to start with, but it kept telling me that certain nodes were missing references no matter what I did. Any advice?</p>
  
  <pre><code>&lt;xml&gt;
  &lt;reports&gt;
      &lt;report name="Report 1"&gt;
          &lt;client id="Client 1"&gt;
              &lt;stat stat_id="client_stats"&gt;
                  &lt;monthStats&gt;0&lt;/monthStats&gt; 
                  &lt;yearStats&gt;0&lt;/yearStats&gt; 
              &lt;/stat &gt;
          &lt;/client&gt;
          &lt;client id="Client 2"&gt;
              &lt;stat stat_id="client_stats"&gt;
                  &lt;monthStats&gt;0&lt;/monthStats&gt; 
                  &lt;yearStats&gt;0&lt;/yearStats&gt; 
               &lt;/stat &gt;
          &lt;/client&gt;
      &lt;/report&gt;
      &lt;report name="Report 2"&gt;
          &lt;client id="Client 1"&gt;
              &lt;stat stat_id="client_stats"&gt;
                  &lt;monthStats&gt;0&lt;/monthStats&gt; 
                  &lt;yearStats&gt;0&lt;/yearStats&gt; 
              &lt;/stat &gt;
          &lt;/client&gt;
          &lt;client id="Client 2"&gt;
              &lt;stat stat_id="client_stats"&gt;
                  &lt;monthStats&gt;0&lt;/monthStats&gt; 
                  &lt;yearStats&gt;0&lt;/yearStats&gt; 
               &lt;/stat &gt;
          &lt;/client&gt;
      &lt;/report&gt;
  &lt;/reports&gt;
  &lt;/xml&gt;
  </code></pre>
  
  <p>Here's the generated schema:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"     attributeFormDefault="unqualified" xmlns:sql="urn:schemas-microsoft-com:xml-sql" &gt;
  &lt;xs:element name="xml"&gt;
      &lt;xs:complexType&gt;
          &lt;xs:sequence&gt;
              &lt;xs:element name="reports" sql:is-constant="true"&gt;
                  &lt;xs:complexType&gt;
                      &lt;xs:sequence&gt;
                          &lt;xs:element name="report" minOccurs="0" maxOccurs="unbounded"&gt;
                              &lt;xs:complexType&gt;
                                  &lt;xs:sequence&gt;
                                      &lt;xs:element name="client" minOccurs="0" maxOccurs="unbounded"&gt;
                                          &lt;xs:complexType&gt;
                                              &lt;xs:sequence&gt;
                                                  &lt;xs:element name="stat" minOccurs="0"&gt;
                                                      &lt;xs:complexType&gt;
                                                          &lt;xs:sequence&gt;
                                                              &lt;xs:element name="monthStats" type="xs:decimal" minOccurs="0" sql:field="monthStats"/&gt;
                                                              &lt;xs:element name="yearStats" type="xs:decimal" minOccurs="0" sql:field="yearStats"/&gt;
                                                          &lt;/xs:sequence&gt;
                                                          &lt;xs:attribute name="stat_id" type="xs:string" use="optional" sql:field="stat_id"/&gt;
                                                      &lt;/xs:complexType&gt;
                                                  &lt;/xs:element&gt;
                                              &lt;/xs:sequence&gt;
                                              &lt;xs:attribute name="id" type="xs:unsignedShort" use="optional" sql:field="clientID"/&gt;
                                          &lt;/xs:complexType&gt;
                                      &lt;/xs:element&gt;
                                  &lt;/xs:sequence&gt;
                                  &lt;xs:attribute name="name" type="xs:string" use="optional" sql:field="reportName"/&gt;
                              &lt;/xs:complexType&gt;
                          &lt;/xs:element&gt;
                      &lt;/xs:sequence&gt;
                  &lt;/xs:complexType&gt;
              &lt;/xs:element&gt;
          &lt;/xs:sequence&gt;
      &lt;/xs:complexType&gt;
  &lt;/xs:element&gt;
  &lt;/xs:schema&gt;
  </code></pre>
  
  <p>And I'm trying to import it with this vbs:</p>
  
  <pre><code>Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")
  objBL.ConnectionString = "Provider=SQLOLEDB; Data Source=[source];Initial Catalog=[database];User ID='[id]';Password='[pw]'"
  objBL.ErrorLogFile = "[path]"
  objBL.Execute "[the xsd]", "[xml file]"
  Set objBL = Nothing
  
  Dim strPathToSchema As String = "c:\path\FileName.xsd"
  Dim strPathToData As String = "c:\path\FileName.xml"
  Dim dtXMLData As New DataTable()
  
  dtXMLData.ReadXMLSchema(strPathToSchema)
  dtXMLData.ReadXML(strPathToData)
  </code></pre>
  
  <p>But I'm getting the error: Schema: relationship expected on 'reports'</p>
  
  <p>The client's initial goal was to have everything in one giant table, but if you've got any ideas as to better structuring, it'd be much appreciated!</p>