XML Bulk Load schema question

I was wondering if someone could point me in the right direction with a
schema file for importing XML into a DB - the XML file has multiple
sublevels, all of which need to be imported into a single table.

I have it functioning "to an extent", meaning part of the file is imported -
however, some of the sublevels have been driving me nuts, and I am not sure
how to get it to work for all of the columns.

ANY HELP WOULD BE GREATLY APPRECIATED - I've searched high and low for any
documentation that would explain how to write up the schema to be able to
import.

Testing the schema I have created so far works - however, it does not import
all of the record columns in the same row, and I am getting some nulls.
<headerModule> has both elements with data as well as elements with
children. The children are loading, but the other elements without children
are not (serviceProvider and type).

In addition, it loads all of the data in from the headermodule(1000 rows),
then loads basicmodule (1000 rows) starting on row 1001 - meaning, it doesnt
load headermodule and basicmodule together, it is loading them as separate
blocks of data.

I am just unsure how you go about allowing the sublevel elements to be included in the import into the single table. I have seen this done before, but the last thread I could find that referenced it was written in Spanish :)

The XML file is as follows:

< xml version="1.0" encoding="ISO-8859-1" >
<!DOCTYPE broadWorksCDR>
<broadWorksCDR version="11.1">
   <cdrData>
<headerModule>
   <recordId>
<eventCounter> </eventCounter>
<systemId> </systemId>
<date> </date>
<systemTimeZone> </systemTimeZone>
   </recordId>
   <serviceProvider> </serviceProvider>
   <type> </type>
</headerModule>
<basicModule>
   <userNumber> </userNumber>
   <groupNumber> </groupNumber>
   <direction> </direction>
   <callingNumber> </callingNumber>
   <callingPresentationIndicator> </callingPresentationIndicator>
   <dialedDigits> </dialedDigits>
   <calledNumber> </calledNumber>
   <networkTranslatedNumber> </networkTranslatedNumber>
   <networkTranslatedGroup> </networkTranslatedGroup>
   <startTime> </startTime>
   <userTimeZone> </userTimeZone>
   <localCallId> </localCallId>
   <remoteCallId> </remoteCallId>
   <answerIndicator> </answerIndicator>
   <answerTime> </answerTime>
   <releaseTime> </releaseTime>
   <terminationCause> </terminationCause>
   <carrierIdentificationCode> </carrierIdentificationCode>
   <callCategory> </callCategory>
   <networkCallType> </networkCallType>
   <chargeIndicator> </chargeIndicator>
   <typeOfNetwork> </typeOfNetwork>
   <releasingParty> </releasingParty>
</basicModule>
<centrexModule>
   <group> </group>
   <department> </department>
   <accountCode> </accountCode>
   <authorizationCode> </authorizationCode>
   <callingPartyCategory> </callingPartyCategory>
   <originalCalledNumber> </originalCalledNumber>
   <originalCalledPresentationIndicator>   </originalCalledPresentationIndicator>
               <originalCalledReason> </originalCalledReason>
   <redirectingNumber> </redirectingNumber>
   <redirectingPresentationIndicator> </redirectingPresentationIndicator>
   <redirectingReason> </redirectingReason>
   <serviceExtensionList>
                 <serviceExtension>
         <serviceName> </serviceName>
         <invocationTime> </invocationTime>
         <callId> </callId>
         <to> </to>
           <from> </from>
                     <conferenceId> </conferenceId>
         <role> </role>
         <bridge> </bridge>
         <owner> </owner>
         <ownerDN> </ownerDN>
         <title> </title>
         <projectCode> </projectCode>
           <facResult> </facResult>
   </serviceExtension>
           </serviceExtensionList>
</centrexModule>
<ipModule>
   <route> </route>
   <networkCallID> </networkCallID>
   <codec> </codec>
   <AccessDeviceAddress> </AccessDeviceAddress>
   <correlationInfo>
<key> </key>
<creator> </creator>
<originatorNetwork> </originatorNetwork>
<terminatorNetwork> </terminatorNetwork>
   </correlationInfo>
</ipModule>
<partialCallBeginModule>
   <failoverCorrelationId> </failoverCorrelationId>
</partialCallBeginModule>
<partialCallEndModule>
   <failoverCorrelationId> </failoverCorrelationId>
</partialCallEndModule>
   </cdrData>
</broadWorksCDR>

My Schema so far:

< xml version="1.0" >
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

   <ElementType name="eventCounter" dt:type="string" />
   <ElementType name="systemId" dt:type="string" />
   <ElementType name="date" dt:type="string" />
   <ElementType name="systemTimeZone" dt:type="string" />
   <ElementType name="userNumber" dt:type="string" />
   <ElementType name="groupNumber" dt:type="string" />
   <ElementType name="direction" dt:type="string" />
   <ElementType name="callingNumber" dt:type="string" />
   <ElementType name="callingPresentationIndicator" dt:type="string" />
   <ElementType name="dialedDigits" dt:type="string" />
   <ElementType name="calledNumber" dt:type="string" />
   <ElementType name="networkTranslatedNumber" dt:type="string" />
   <ElementType name="networkTranslatedGroup" dt:type="string" />
   <ElementType name="startTime" dt:type="string" />
   <ElementType name="userTimeZone" dt:type="string" />
   <ElementType name="localCallId" dt:type="string" />
   <ElementType name="remoteCallId" dt:type="string" />
   <ElementType name="answerIndicator" dt:type="string" />
   <ElementType name="answerTime" dt:type="string" />
   <ElementType name="releaseTime" dt:type="string" />
   <ElementType name="terminationCause" dt:type="string" />
   <ElementType name="carrierIdentificationCode" dt:type="string" />
   <ElementType name="callCategory" dt:type="string" />
   <ElementType name="networkCallType" dt:type="string" />
   <ElementType name="chargeIndicator" dt:type="string" />
   <ElementType name="typeOfNetwork" dt:type="string" />
   <ElementType name="releasingParty" dt:type="string" />

   <ElementType name="broadWorksCDR" sql:is-constant="1">
<element type="cdrData" />
   </ElementType>

   <ElementType name="cdrData" sql:is-constant="1">
<element type="headerModule" />
<element type="basicModule" />
   </ElementType>

   <ElementType name="headerModule" sql:is-constant="1">
<element type="recordId" />
   </ElementType>

   <ElementType name="recordId" sql:relation="cdrdata">
<element type="eventCounter" sql:field="eventcounter" />
<element type="systemId" sql:field="systemId" />
<element type="date" sql:field="date" />
   <element type="systemTimeZone" sql:field="systemtimezone" />
   </ElementType>

     <ElementType name="basicModule" sql:relation="cdrdata">
<element type="userNumber" sql:field="userNumber" />
<element type="groupNumber" sql:field="groupNumber" />
<element type="direction" sql:field="direction" />
<element type="callingNumber" sql:field="callingNumber" />
<element type="callingPresentationIndicator" sql:field="callingPresentationIndicator" />
<element type="dialedDigits" sql:field="dialedDigits" />
<element type="calledNumber" sql:field="calledNumber" />
<element type="networkTranslatedNumber" sql:field="networkTranslatedNumber" />
<element type="networkTranslatedGroup" sql:field="networkTranslatedGroup" />
<element type="startTime" sql:field="startTime" />
<element type="userTimeZone" sql:field="userTimeZone" />
<element type="localCallId" sql:field="localCallId" />
<element type="remoteCallId" sql:field="remoteCallId" />
<element type="answerIndicator" sql:field="answerIndicator" />
<element type="answerTime" sql:field="answerTime" />
<element type="releaseTime" sql:field="releaseTime" />
<element type="terminationCause" sql:field="terminationCause" />
<element type="carrierIdentificationCode" sql:field="carrierIdentificationCode" />
<element type="callCategory" sql:field="callCategory" />
<element type="networkCallType" sql:field="networkCallType" />
<element type="chargeIndicator" sql:field="chargeIndicator" />
<element type="typeOfNetwork" sql:field="typeOfNetwork" />
<element type="releasingParty" sql:field="releasingParty" />
   </ElementType>

</Schema>

Someone, anyone, please help! I'll wash your car and stuff! I'm really in
a bit of a bind.

Thanks much!

Answer this question