Importing Access DB: Importing data into MS SQL 2000 from XML file

Hi Everone,

I'm writing a script to import data from an XML file, and this tutorial is
really an awesome guide :
http://www.kbalertz.com/Q316005/Imp...Component.aspx ... problem
though is here's a snippet of my XML file:

<planets>
<sun rise="6:23" set="20:33"/>
<moon rise="18:54" set="4:26"/>
<mercury rise="7:50" set="22:11"/>
<venus rise="4:24" set="17:38"/>
<mars rise="9:45" set="23:40"/>
<jupiter rise="17:23" set="4:20"/>
<saturn rise="10:09" set="23:53"/>
<uranus rise="1:38" set="13:10"/>
<neptune rise="0:23" set="11:13"/>
<pluto rise="20:38" set="7:25"/>
</planets
<moon>
<phase date="7/6/2006" text="Waxing Gibbous">9</phase>
<phase date="7/7/2006" text="Waxing Gibbous">10</phase>
<phase date="7/8/2006" text="Waxing Gibbous">11</phase>
<phase date="7/9/2006" text="Waxing Gibbous">12</phase>
<phase date="7/10/2006" text="Waning Gibbous">14</phase>
</moon
The tutorial seems to work well with entries in XML that look like this:
<nighttime>
<txtshort>A moonlit sky</txtshort>
<txtlong>A moonlit sky</txtlong>
<weathericon>33</weathericon>
<hightemperature>100</hightemperature>
<lowtemperature>74</lowtemperature>
<realfeelhigh>108</realfeelhigh>
<realfeellow>74</realfeellow>
</nighttime
But when the XML file has more info (not sure technically what it's called)
like the date and text options above in the phase tag, I'm not sure how to
import these into MS SQL. In MS Access I did do an import from this XML
file, but alas it didn't see these entries in the phase tags either.

Suggestions? I'm trying to use the XML Bulk Load component, but I'm sure
I'm missing something. Thanks for any suggestions ...

Sam

--
Sam Alexander - sam.alexander(at)sidebandbbs(dot)com
http://www.samandflip.com or telnet://sidebandbbs.com
--
"Data is not information, Information is not knowledge, Knowledge is not
understanding, Understanding is not wisdom." -- Cliff Stoll

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--Re: Importing data into MS SQL 2000 from XML file
By: Sam Alexander to All on Fri Jun 09 2006 09:20 am

Okay I'm getting alittle further... setting my prior question to the side,
now I'm simply trying to get data to import into the database. HEre's what
I've done.

First thing was I installed SQLXML 3.0 sp3 from MS's website and created
my schema and vbs scripts. HEre's my test import:

mydata.xml

<?xml version="1.0"?>
<mydata>
<units>
<temp>F</temp>
<dist>mi</dist>
<speed>mph</speed>
<pres>in</pres>
<prec>in</prec>
</units>
</mydata
schema.xml

<?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="temp" dt:type="nvarchar" />
<ElementType name="dist" dt:type="nvarchar" />
<ElementType name="speed" dt:type="nvarchar" />
<ElementType name="pres" dt:type="nvarchar" />
<ElementType name="prec" dt:type="nvarchar" /
<ElementType name="mydata" sql:is-constant="1">
<element type="Units" />
</ElementType
<ElementType name="Units"sql:relation="Units">
<element type="temp"sql:field="temp" />
<element type="dist"sql:field="dist" />
<element type="speed"sql:field="speed" />
<element type="pres"sql:field="pres" />
<element type="prec"sql:field="prec" />
</ElementType
</Schema
import.vbs
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=mydbserver;database=mydb;uid=myusername;pwd =mypassword"
objBL.ErrorLogFile = "d:\xml\error.log"
objBL.Execute "d:\xml\schema.xml", "d:\xml\mydata.xml"
Set objBL = Nothing
(I do have my server and login info above ...)

Also I do have a table called Units in the database and here's the layout
of it:
unitstempnvarchar255
unitsdistnvarchar255
unitsspeednvarchar255
unitspresnvarchar255
unitsprecnvarchar255

When I run this I get no message nor any error log. I can test it by
changing the password and I get the unable to connect error, so I know it's
connecting to the server.

ANy suggestions? I checked the MS SQL Server processes anddidn't see any
clue that the script was logging in, but since I'm getting no errors nor
any logs showing what's happening I'm at a loss.

Thanks for any ideas or starting points on where to look. I'm at a total
loss on what to check. Thanks ...

Sam

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--|||I've noticed you have 'Units' and 'units' - XML is often (always?)
case-sensitive so this may not work.|||To: markc600
Re: Re: Importing data into MS SQL 2000 from XML file
By: markc600 to comp.databases.ms-sqlserver on Fri Jun 09 2006 09:31 am

> From Newsgroup: comp.databases.ms-sqlserver
>
> I've noticed you have 'Units' and 'units' - XML is often (always?)
> case-sensitive so this may not work.
> -- Synchronet 3.13b-Linux NewsLink 1.84

Hi Marc,

Thanks for the reply, and I'll check this out. Since making my post
earlier I've gotten my import to work, but to a point.

My XML file starts with this:
<?xml version="1.0" ?>
<vendor_database xmlns="http://www.vendorurl.com">
-snip-

and here's how my schema.xml file starts ...

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
<xsd:element name="vendor_database" sql:is-constant="true" >
-snip-

If I remove the xmlns entry from the XML data file the import runs fine,
but leaving it in there the import runs but nothing is imported. How do I
work this into the schema file? This vendor file is downloaded
automatically and no way to have this entry deleted from the xml file I"m
importing into my database.

So at this point it's just about there, just need to figure how what to do
with the xmlns entry.

Thanks --

Sam

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--

Friday, March 9, 2012

Importing data into MS SQL 2000 from XML file

No comments:

Post a Comment

Importing Access DB

Blog Archive

About Me