homepage Welcome to WebmasterWorld Guest from 54.161.191.254
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / XML Development
Forum Library, Charter, Moderators: httpwebwitch

XML Development Forum

    
Bulk Load using attributes not elements
newwavecafe




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

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




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

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>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / XML Development
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved