Bulk load with multiple element types.

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/

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources