Welcome to WebmasterWorld Guest from 54.144.124.152

Forum Moderators: httpwebwitch

Message Too Old, No Replies

Bulk Load using attributes not elements

     

newwavecafe

6:11 pm on Dec 10, 2006 (gmt 0)

5+ Year Member



Hello
I am new to XML and I am looking to find a way of importing data from an XML file into a table (SQL Server).

My XML looks like this. It is taken from an XML feed, and there are no <xml> or <schema> tags (not sure if that is a problem?)

<Location>
<row Location="Oxford" LocationId="37">
<row Location="London" LocationId="15">
<row Location="Manchester" LocationId="23">
</Location>

Here is my xml file which tries to map the data onto a table:

<?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="Location" dt:type="string" />
<ElementType name="LocationID" dt:type="int" />

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

<ElementType name="row" sql:relation="tblLocation">
<attribute type="Location" sql:field="Location" />
<attribute type="LocationID" sql:field="LocationID" />
</ElementType>
</Schema>

I use a vbs file using CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") to run these files, but I can't get it to work. I would be very grateful if some body could point me in the right direction.

Many thanks
Ben

newwavecafe

11:29 am on Dec 11, 2006 (gmt 0)

5+ Year Member



Hi
I have found the solution, if anyone is interested, this mapping schema worked:

<?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" >

<AttributeType name="LocationId" dt:type="int" />
<AttributeType name="Location" dt:type="string" />

<ElementType name="Location" sql:is-constant="1">
<element type="row" />
</ElementType>

<ElementType name="row" sql:relation="fxLocation">
<attribute type="LocationId" sql:field="LocationID" />
<attribute type="Location" sql:field="Location" />
</ElementType>

</Schema>

 

Featured Threads

Hot Threads This Week

Hot Threads This Month