XML Bulkload into SQL Server

[ge0rge]

Hello.
I'm using the Bulkload 4.0 to import data elements from an XML file into a SQL server (2003) database.
The Bulkload process works fine until it's get to a certain section of the XML file that has multiple child elements all with the same name as shown below:

<ICD9S>
<ICD9LINE>
<ICD9 />
<ICD9DESC />
</ICD9LINE>
<ICD9LINE>
<ICD9 />
<ICD9DESC />
</ICD9LINE>
<ICD9LINE>
<ICD9 />
<ICD9DESC />
</ICD9LINE>
<ICD9LINE>
<ICD9 />
<ICD9DESC />
</ICD9LINE>
<ICD9LINE>
<ICD9 />
<ICD9DESC />
</ICD9LINE>
</ICD9S>

My associated XSD Schema for this section looks like this:

<xsd:element name="ICD9S" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ICD9LINE" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ICD9" sql:field="" type="xsd:string" />
<xsd:element name="ICD9DESC" sql:field="" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>

**NOTE: There should be a total of 5 ICD9LINE sections in the above schema but I left them out.

Whenever I run the little bulkload script I get an error saying that the ICD9LINE is unexpected according to the parent model of ICD9S.

Any help would be greatly appreciated.

[Toom]

I have exactly the same problem. It looks like sql:is-constant only works the way it is documented before you specify sql:relation. Afterwards it works like sql:mapped and also ignores all the child elements.

If anybody has a solution or a workaround please post it. It is dissapointing for me not to able to use bulk load just because annotations are a mess.