CODEWITHSUNDEEP
sql-servervbscriptsqlxml
LJ Wilson
LJ Wilson

Reputation: 14427

XML Bulk Load issue into SQL Server

I am trying to use XML Bulk Load (sql server 2008). I am almost there, but I think my schema file is wrong. The error I am getting is this:

VBS Error Msg

Here is what I have:

SQL Table Structure:

SQL Table Structure

Schema File:

<?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="weight" dt:type="string" />
    <ElementType name="fwd" dt:type="float" />
    <ElementType name="aft" dt:type="float" />

    <ElementType name="CGs" sql:is-constant="1">
        <element type="gross" />
    </ElementType>

    <ElementType name="gross"  sql:relation="tblCGLimits">
      <element type="weight"  sql:field="weight" />
      <element type="fwd" sql:field="fwd" />
      <element type="aft"        sql:field="aft" />
   </ElementType>
</Schema>

XML File:

<?xml version="1.0" encoding="utf-8" ?>
<CGs>
    <gross weight="8000">
        <fwd>196.5</fwd>
        <aft>208.88162</aft>
    </gross>
    <gross weight="8001">
        <fwd>196.495</fwd>
        <aft>208.8825148</aft>
    </gross>
    <gross weight="8002">
        <fwd>196.49</fwd>
        <aft>208.8834096</aft>
    </gross>
</CGs>

And the VBScript I am using:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MyServer;database=MyDB;uid=MyUser;pwd=MyPW"
objBL.ErrorLogFile = "c:\XMLError.log"
objBL.Execute "c:\Schema.xml", "c:\CGLimits.xml"
Set objBL = Nothing

Upvotes: 2

Views: 833

Answers (1)

Jirka Hanika
Jirka Hanika

Reputation: 13549

Your XSD file specifies that weight values are elements, rather than attributes, which would look as follows:

<?xml version="1.0" encoding="utf-8" ?>
<CGs>
    <gross>
        <weight>8000</weight>
        <fwd>196.5</fwd>
        <aft>208.88162</aft>
    </gross>
    <gross>
        <weight>8001</weight>
        <fwd>196.495</fwd>
        <aft>208.8825148</aft>
    </gross>
    <gross>
        <weight>8002</weight>
        <fwd>196.49</fwd>
        <aft>208.8834096</aft>
    </gross>
</CGs>

To correct this, change 

<element type="weight" sql:field="weight"/>

to

<attribute type="weight" sql:field="weight"/>

in the XSD file.

Upvotes: 2

Related Questions