Sublevels of elements with attributes

[mandrakeway]

Hi,

I am having hard time building a XML Schema used during an XML Bulk Load.

The XML file comes from an external source which means that I am not allowed to improve its structure. It contains sublevels of elements with attributes.

My goal is to load the data once, by carrying the attributes of the first element (location) with the data from the lowest element (dollars and percent). In previous tests, I was able to load either the locations or the dollars / percent, but not them all together. I can generate one or two tables into the database, it does not matter.

I will appreciate any help.

Thank you.

1) XML File

Here is an extraction of the file.

<?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?>
<NewDataSet>
<Location name=&quot;Chicago, IL&quot; id = &quot;12345&quot; >
<houses year=&quot;2001&quot;>
<dollars>15469.412</dollars>
<percent>10.23</percent>
</houses>
<appartments year=&quot;2001&quot;>
<dollars>45872.2</dollars>
<percent>5.3</percent>
</appartments>
<houses year=&quot;2002&quot;>
<dollars>872369.154</dollars>
<percent>34.475</percent>
</houses>
<appartments year=&quot;2002&quot;>
<dollars>65748.21</dollars>
<percent>12.75</percent>
</appartments>
</Location>
<Location name=&quot;Detroit, MI&quot; id = &quot;45878&quot; >
<houses year=&quot;2001&quot;>
<dollars>578945.12</dollars>
<percent>21.45</percent>
</houses>
<appartments year=&quot;2001&quot;>
<dollars>65479.0</dollars>
<percent>58.326</percent>
</appartments>
<houses year=&quot;2002&quot;>
<dollars>5471.23</dollars>
<percent>2.341</percent>
</houses>
<appartments year=&quot;2002&quot;>
<dollars>479636</dollars>
<percent>18.24</percent>
</appartments>
</Location>
</NewDataSet>

2) XML Schema

Here is the schema that I wrote

<xsd:schema xmlns:xsd=&quot;

http://www.w3.org/2001/XMLSchema&quot;

xmlns:sql=&quot;urn:schemas-microsoft-com:mapping-schema&quot;>

<xsd:element name=&quot;NewDataSet&quot; sql:mapped=&quot;false&quot;>
<xsd:complexType>

<xsd:sequence>

<xsd:element name=&quot;Location&quot; sql:relation=&quot;Locations&quot;>
<xsd:complexType>
<xsd:sequence>

<xsd:element name=&quot;houses&quot;>
<xsd:complexType>
<xsd:sequence>
<xsd:element name=&quot;dollars&quot; sql:field=&quot;HousesDollars&quot; type=&quot;xsd:string&quot; />
<xsd:element name=&quot;percent&quot; sql:field=&quot;HousesDollars&quot; type=&quot;xsd:string&quot; />
</xsd:sequence>
<xsd:attribute name=&quot;year&quot; sql:field=&quot;HousesYear&quot; type=&quot;xsd:string&quot;/>
</xsd:complexType>
</xsd:element>

<xsd:element name=&quot;appartments&quot;>
<xsd:complexType>
<xsd:sequence>
<xsd:element name=&quot;dollars&quot; sql:field=&quot;AppartmentsDollars&quot; type=&quot;xsd:string&quot; />
<xsd:element name=&quot;percent&quot; sql:field=&quot;AppartmentsPercent&quot; type=&quot;xsd:string&quot; />
</xsd:sequence>
<xsd:attribute name=&quot;year&quot; sql:field=&quot;AppartmentsYear&quot; type=&quot;xsd:string&quot;/>
</xsd:complexType>
</xsd:element>

</xsd:sequence>
<xsd:attribute name=&quot;name&quot; sql:field=&quot;LocationName&quot; type=&quot;xsd:string&quot;/>
<xsd:attribute name=&quot;id&quot; sql:field=&quot;LocationId&quot; type=&quot;xsd:string&quot;/>

</xsd:complexType>
</xsd:element>

</xsd:sequence>

</xsd:complexType>
</xsd:element>

</xsd:schema>

I am not getting any error but nothing is loaded. I believe that this schema does not match the data structure.