Importing data into MS SQL 2000 from XML file

[Sam Alexander]

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/Impo...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>
<hightemperatur e>100</hightemperature >
<lowtemperature >74</lowtemperature>
<realfeelhigh>1 08</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(a t)sidebandbbs(d ot)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]--

[Sam Alexander]

Importing data into MS SQL 2000 from XML file

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:sche mas-microsoft-com:xml-data"
xmlns:dt="urn:s chemas-microsoft-com:xml:datatyp es"
xmlns:sql="urn: schemas-microsoft-com:xml-sql" >

<ElementType name="temp" dt:type="nvarch ar" />
<ElementType name="dist" dt:type="nvarch ar" />
<ElementType name="speed" dt:type="nvarch ar" />
<ElementType name="pres" dt:type="nvarch ar" />
<ElementType name="prec" dt:type="nvarch ar" />

<ElementType name="mydata" sql:is-constant="1">
<element type="Units" />
</ElementType>

<ElementType name="Units" sql:relation="U nits">
<element type="temp" sql:field="temp " />
<element type="dist" sql:field="dist " />
<element type="speed" sql:field="spee d" />
<element type="pres" sql:field="pres " />
<element type="prec" sql:field="prec " />
</ElementType>

</Schema>


import.vbs
Set objBL = CreateObject("S QLXMLBulkLoad.S QLXMLBulkLoad")
objBL.Connectio nString = "provider=SQLOL EDB.1;data
source=mydbserv er;database=myd b;uid=myusernam e;pwd=mypasswor d"
objBL.ErrorLogF ile = "d:\xml\error.l og"
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:
units temp nvarchar 255
units dist nvarchar 255
units speed nvarchar 255
units pres nvarchar 255
units prec nvarchar 255


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


---
Sam Alexander - sam.alexander(a t)sidebandbbs(d ot)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]--

[markc600@hotmail.com]

Re: Importing data into MS SQL 2000 from XML file


I've noticed you have 'Units' and 'units' - XML is often (always?)
case-sensitive so this may not work.

[Sam Alexander]

Re: Importing data into MS SQL 2000 from XML file

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
[color=blue]
> 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[/color]

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_databas e xmlns="http://www.vendorurl.c om">
-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_da tabase" 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


---
Sam Alexander - sam.alexander(a t)sidebandbbs(d ot)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]--