Hi,

I have the following problem which I suspect is a bug with SQLXML.
Can anyone out there confirm ???

I'm using the SQLXMLBulkLoad3Class class to insert an XML document
into SQL Server (with SQLXML 3.0 SP2).

Here is the XML document:
<?xml version="1.0" encoding="UTF-8"?>
<BulkTest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="testBulkLoad.xsd">
<SousStationHP>
<Code>Code 1</Code>
<CompteurEnergie>
<Code>Code 2</Code>
<Fournisseur>
<NomFournisseur>Fournisseur 1</NomFournisseur>
</Fournisseur>
<Mesureur>
<Fournisseur>
<NomFournisseur>Fournisseur 2</NomFournisseur>
</Fournisseur>
<Reference>AZERTY</Reference>
</Mesureur>
</CompteurEnergie>
</SousStationHP>
</BulkTest>

Now the problem is that my "Fournisseur" table does include 2 entries
when I'm done with the loading, but both ForeignKey fields point to
the same value which is the one that corresponds to the "Mesureur"
element.

The "Fournisseur" information for my "CompteurEnergie" element is
lost!!!

Can anyone help ?

Thanks,

Phil


Here is the schema I'm using:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Fournisseur_REL" parent-key="PrimaryKey"
child="Fournisseur" child-key="ForeignKey"/>
<sql:relationship name="Mesureur_REL" parent-key="PrimaryKey"
child="Mesureur" child-key="ForeignKey"/>
<sql:relationship name="SousStationHP_REL" parent-key="PrimaryKey"
child="SousStationHP" child-key="ForeignKey"/>
<sql:relationship name="CompteurEnergie_REL"
parent-key="PrimaryKey" child="CompteurEnergie"
child-key="ForeignKey"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType name="Appareil">
<xs:sequence>
<xs:element name="Code" type="xs:string" minOccurs="0"/>
<xs:element ref="Fournisseur" minOccurs="0"
sql:relationship="Fournisseur_REL" sql:key-fields="PrimaryKey"/>
</xs:sequence>
</xs:complexType>
<xs:element name="CompteurEnergie">
<xs:complexType>
<xs:complexContent>
<xs:extension base="Appareil">
<xs:sequence>
<xs:element ref="Mesureur" sql:relationship="Mesureur_REL"
sql:key-fields="PrimaryKey"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:element>
<xs:element name="SousStationHP">
<xs:complexType>
<xs:sequence>
<xs:element name="Code" type="xs:string"/>
<xs:sequence maxOccurs="unbounded">
<xs:element ref="CompteurEnergie" minOccurs="0"
sql:relationship="CompteurEnergie_REL" sql:key-fields="PrimaryKey"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="BulkTest">
<xs:complexType>
<xs:sequence>
<xs:element ref="SousStationHP" minOccurs="0"
maxOccurs="unbounded" sql:relationship="SousStationHP_REL"
sql:key-fields="PrimaryKey"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Mesureur">
<xs:complexType>
<xs:complexContent>
<xs:extension base="Appareil">
<xs:sequence>
<xs:element name="Reference" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:element>
<xs:element name="Fournisseur">
<xs:complexType>
<xs:sequence>
<xs:element name="NomFournisseur" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

For all tables, ForeignKey is an int and PrimaryKey is an Identity
Int.

"Phil" <pwdng (AT) hotmail (DOT) com> wrote

You will probably need to flatten your XML.
http://sqlxml.org/faqs.aspx?faq=49

--
Bryant

Hi,

Can you please explain ?
I don't understand why you say that.
I can't see why the problem should come from my XML document as my
data is not table related. In any case, there is no way to make it
"flat"...

Anyone else has an idea?
I'm really stuck with this and I don't want to write a program to do
the bulk load operation!

Thanks,

Phil

"Bryant Likes" <bryant (AT) suespammers (DOT) org> wrote