Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Bulk load with multiple element types.

Author  Topic 

mn757
Starting Member

15 Posts

Posted - 2013-02-13 : 06:48:32
Hi, I'm using Bulk Load to import from xml file, which works fine for most xml files. However, I have a schema where there are multiple element types within the main element type.

e.g

<ROOT>

<Customers>
<CustomerId>9999</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<Test><City>NY</City></Test>

</Customers>

</ROOT>

It ignores the City element altogether.

I've tried adding an extra <element type="Test" /> to the mapping file but it just ignores it. Can anyone help ?

many thanks

Martin.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 06:58:27
you need to modify your xsd schema accordingly to make Test complexType and add element City inside it

something like

<xs:element name="Test">
<xs:complexType>
<xs:sequence>
<xs:element name="City" type="xs:string" minOccurs="0" maxOccurs="unbounded" />
....
</xs:sequence>
</xs:complexType>
</xs:element>


the minOccurs part is required if its optional and maxOccurs is required if it can repeat'
similarly add sections for other elements inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mn757
Starting Member

15 Posts

Posted - 2013-02-13 : 07:33:54
Thanks for reply. The sample schema I'm using follows a different format to that.

<?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="CustomerId" dt:type="int" />
<ElementType name="CompanyName" dt:type="string" />
<ElementType name="City" dt:type="string" />


<ElementType name="ROOT" sql:is-constant="1">
<element type="Customers" />
<element type="Test" />
</ElementType>

<ElementType name="Customers" sql:relation="Customer">
<element type="CustomerId" sql:field="CustomerId" />
<element type="CompanyName" sql:field="CompanyName" />
<element type="City" sql:field="City" />
</ElementType>




</Schema>

I have tried adding a new element type under the root, but all fields are entered into database except the City

Sorry if I'm missing something..

thanks

Martin.

Go to Top of Page
   

- Advertisement -