SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Bulk load with multiple element types.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mn757
Starting Member

United Kingdom
15 Posts

Posted - 02/13/2013 :  06:48:32  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/13/2013 :  06:58:27  Show Profile  Reply with Quote
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

United Kingdom
15 Posts

Posted - 02/13/2013 :  07:33:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000