Do not use on any
shared computer
August 30, 2008 09:39am pdt
 
[x]
Attachment Details

How do I import XML into SQL Server 2000 using SQLXMLBulkLoad when an element has a different namespace?

Tags: Microsoft, SQL Server, 2000, SQLXMLBulkLoad, XML, XSD Schema, SQLXMLBulkLoad
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:Height>
    <aws:Width Units="pixels">50</aws:Width>
  </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-microsoft-com:mapping-schema"
            xmlns:aws="http://webservices.amazon.com/AWSECommerceService"
            elementFormDefault="unqualified">

<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="tblAmazonBase_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="tblAmazonImage_Load"
                                                             sql:relationship="Item_Image"
                               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="intSmallImageHeight" type="xsd:integer" />
    <xsd:element name="Width" sql:field="intSmallImageWidth" 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("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;Data Source=db;database=db;User Id=sa;Password=password;"
objBL.ErrorLogFile = "c:\DataFeeds\Schemas\amazon.log"
objBL.CheckConstraints = False

objBL.Execute "c:\DataFeeds\Schemas\AmazonSchema3.xml", "c:\DataFeeds\Amazon.xml"
set objBL=Nothing
msgbox("Amazon XML Load Done")


Any suggestions?
Start your free trial to view this solution
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Question Stats
Zone: Web Development
Question Asked By: kevacoustic
Solution Provided By: dbaSQL
Participating Experts: 1
Solution Grade: A
Views: 166
Translate:
Loading Advertisement...
 
[+][-]Expert Comment by dbaSQL

Rank: Guru

Expert Comment by dbaSQL:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Author Comment by kevacoustic
Author Comment by kevacoustic:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Accepted Solution by dbaSQL

Rank: Guru

Accepted Solution by dbaSQL:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Administrative Comment by modus_operandi
Administrative Comment by modus_operandi:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
[+][-]Administrative Comment by Night-Eagle
Administrative Comment by Night-Eagle:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
20080723-EE-VQP-34 / EE_QW_2_20070628