Here is my situation:
I need to import an Amazon XML file into SQL Server 2000 using SQLXMLBulkLoad 3.0. I have created the schema file and it doesn't work. Here is the XML:
--------------- XML ------------------------
<?xml version="1.0" encoding="UTF-8" ?>
<DataFeeds>
<Item>
<ASIN>AMAZON_ASIN</ASIN>
<SalesRank>1234</SalesRank
>
<SmallImage xmlns:aws="
http://webservices.amazon.com/AWSECommerceService">
<aws:URL>
http://IMAGE_URL<
/aws:URL>
<aws:Height Units="pixels">50</aws:Hei
ght>
<aws:Width Units="pixels">50</aws:Wid
th>
</SmallImage>
</Item>
</DataFeeds>
------------- End XML --------------------------
----
Here is my schema file:
---------------- Schema --------------------------
---
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="
http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-mic
rosoft-com
:mapping-s
chema"
xmlns:aws="
http://webservices.amazon.com/AWSECommerceService"
elementFormDefault="unqual
ified">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Item_Image"
parent="tblAmazonBase_Load
"
parent-key="vcASIN"
child="tblAmazonImage_Load
"
child-key="vcASIN" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Item" sql:relation="tblAmazonBas
e_Load" type="ItemType" />
<xsd:complexType name="ItemType" >
<xsd:sequence>
<xsd:element name="ASIN" sql:field="vcASIN" type="xsd:string" />
<xsd:element name="SalesRank" sql:field="intSalesRank" type="xsd:integer" />
<xsd:element name="SmallImage" sql:relation="tblAmazonIma
ge_Load"
sql:relationship="Item_Ima
ge"
type="ImageType" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="ImageType">
<xsd:sequence>
<xsd:element name="URL" sql:field="vcSmallImageUrl
" type="xsd:string" />
<xsd:element name="Height" sql:field="intSmallImageHe
ight" type="xsd:integer" />
<xsd:element name="Width" sql:field="intSmallImageWi
dth" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
------------------- End of Schema --------------------------
--------
Here is my table structure:
CREATE TABLE [tblAmazonBase_Load] (
[vcASIN] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL ,
[intSalesRank] [int] NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [tblAmazonImage_load] (
[vcASIN] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_
AS NOT NULL ,
[vcSmallImageUrl] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
AS NOT NULL ,
[intSmallImageHeight] [int] NULL ,
[intSmallImageWidth] [int] NULL
) ON [PRIMARY]
GO
I'm not worried about foreign keys right now, I just want to get the import working.
Now, here is the problem: the namespace "aws:" in the XML element names is not letting the data load. If I take off the NOT NULL constraint from the image table (vcSmallImageUrl), it will run without any errors, but it will only load ASIN in the table, with NULLs for the image fields. If I leave the NOT NULL constraint on the table, I get this error:
"No data was provided for column 'vcSmallImageUrl' on table 'tblAmazonImage_Load', and this column cannot contain NULL values."
I have tried numerous things with namespaces, and nothing I try works. I would just replace all "aws:" with empty string in the XML file, but it is too large (we're talking anywhere from 400MB to 45GB). Yes, I said 45GB! So, I really need to get this import working without having to modify the XML file.
Here is the small VBScript that I'm using to test this:
set objBL = CreateObject("SQLXMLBulkLo
ad.SQLXMLB
ulkload.3.
0")
objBL.ConnectionString = "provider=SQLOLEDB;Data Source=db;database=db;User
Id=sa;Password=password;"
objBL.ErrorLogFile = "c:\DataFeeds\Schemas\amaz
on.log"
objBL.CheckConstraints = False
objBL.Execute "c:\DataFeeds\Schemas\Amaz
onSchema3.
xml", "c:\DataFeeds\Amazon.xml"
set objBL=Nothing
msgbox("Amazon XML Load Done")
Any suggestions?