newbie needs help!! - should be very easy

newbie needs help!! - should be very easy

Post by Hanssen Bar » Sat, 27 Jul 2002 23:34:43



Hi guys,

I wanted to insert some data from an xml into a sql2000 table, using a
vbscript.
The vbs runs without any errors, but noo data is inserted into the table.

Can anyone take a look at the following files and tell me what I'm doing
wrong?
Is there a tool that I need to have for SQL2000, a dll, ...?

Any help is appreciated.

TIA,

Bart.

this is the content of the xml - file:
*************
<?xml version = '1.0'?>
<Employees>
 <EMPLOYEE>
  <ID>1</ID>
  <NAME>Hanssen Bart</NAME>
 </EMPLOYEE>
 <EMPLOYEE>
  <ID>3</ID>
  <NAME>Verrykt Peter</NAME>
 </EMPLOYEE>
</Employees>
*************

the mapping schema:
*************
<?xml version="1.0" ?>
<Schema name="test"
        xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">

    <ElementType name="ID"/>
    <ElementType name="NAME"/>

    <ElementType name="EMPLOYEE" sql:relation="Employees">
 <Element type="ID" sql:field="ID"/>
 <Element type="NAME" sql:field="Name"/>
    </ElementType>

    <ElementType name="Employees">
        <Element type="EMPLOYEE"/>
    </ElementType>

</Schema>
*************

...and the code of the vbs:
*************
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = "Provider=SQLOLEDB;Data
Source=vac-dev.vacnet.be;UID=sa;PWD=intruder90210;DATABASE=Sportuitslagen"
msgbox "start"
objbulkload.errorlogfile = "c:\projects\sport\bulkloaderrors.xml"
objBulkLoad.SchemaGen = true
objBulkLoad.Execute "c:\E Should be very easympSchema.xml", "c:\emp.xml"
msgbox "end"
Set objBulkLoad = nothing
*************

 
 
 

newbie needs help!! - should be very easy

Post by Bryant Like » Sun, 28 Jul 2002 08:14:15



[snip]

Quote:>     <ElementType name="Employees">
>         <Element type="EMPLOYEE"/>
>     </ElementType>

The Employees element need to be declared as a constant.
 <ElementType name="Employees" sql:is-constant="1">

--
Bryant

 
 
 

newbie needs help!! - should be very easy

Post by Hanssen Bar » Tue, 30 Jul 2002 23:11:36


Hi Bryant,

thnx for the quick response.

I now use a xsd schema to insert the values into the table.

However, I have one more qustion.
Is it possible to insert the data into a table depending on the value of an
element.

Eg.
Element Value contains 'SPT' ---> insert into table 1
Element Value contains 'DIV' ---> insert into table 2

I tried using :

 <xsd:simpleType name="NiveauType">
   <xsd:restriction base="xsd:string">
    <xsd:pattern value="[D][I][V]" />
   </xsd:restriction>
 </xsd:simpleType>

 <xsd:element name="WB_BLOGS" sql:relation="Divisies">
  <xsd:complexType>
   <xsd:sequence>
    <xsd:element name="NIVEAU" type="NiveauType"  sql:field="niveau" />
    <xsd:element name="SPT_C_ID" type="xsd:string" />
    <xsd:element name="COM_C_ID" type="xsd:string"/>
    <xsd:element name="DIV_C_ID" type="xsd:string"/>
    <xsd:element name="SYSCODE_DIV" type="xsd:string"/>
    <xsd:element name="NAAM" type="xsd:string"/>
   </xsd:sequence>
  </xsd:complexType>
 </xsd:element>

But it doesn't seem to work.

Any ideas?

Bart.




> [snip]
> >     <ElementType name="Employees">
> >         <Element type="EMPLOYEE"/>
> >     </ElementType>

> The Employees element need to be declared as a constant.
>  <ElementType name="Employees" sql:is-constant="1">

> --
> Bryant

 
 
 

newbie needs help!! - should be very easy

Post by Bryant Like » Wed, 31 Jul 2002 08:20:36



[snip]

Quote:> Is it possible to insert the data into a table depending on the value of
an
> element.

Not directly with Bulk Loading. However, you could use an XSL transformation
to sort the elements into two different names and then load them into to
different tables.

--
Bryant

 
 
 

newbie needs help!! - should be very easy

Post by Hanssen Bar » Sat, 03 Aug 2002 16:03:03


Thanks Bryant.
It worked!!

Bart.




> [snip]
> > Is it possible to insert the data into a table depending on the value of
> an
> > element.

> Not directly with Bulk Loading. However, you could use an XSL
transformation
> to sort the elements into two different names and then load them into to
> different tables.

> --
> Bryant