Importing XML feed for SQL BULK load

External Since: Apr 02, 2009 Posts: 1

Hi there, I'm very new at this, but I'm trying to import products from an XML
feed to my database. I'm having two issues, but I think I understand one of
them.

The main problem I'm having is that I keep getting the error message:
"duplicate element definition 'product'"

Looking at the XML, you can see why this might be the case:

<?xml version="1.0" encoding="UTF-8"?>
<merchandiser xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:
noNamespaceSchemaLocation="merchandiser.xsd">
<header>
<merchantId>24724</merchantId>
<merchantName>UNIQLO</merchantName>
<createdOn>2009-03-30/17:26:53</createdOn>
</header>
<product product_id="233984" name="Pique polo shirt"
sku_number="2000009298826">
<category>
<primary>T-shirts & Polos</primary>
<secondary>Men</secondary>
</category>
<URL>
<product>http://click.linksynergy.com/fs-bin/click?xxxxxx</product>
<productImage>http://www.uniqlo.co.uk/blah/blah/blah.jpg</productImage>
<buy/>
</URL>
<description>
<short>Pique polo shirt</short>
<long/>
</description>
<discount currency="GBP">
<amount/>
<type>amount</type>
</discount>
<price currency="GBP">
<sale begin_date="" end_date="">12.99</sale>
<retail>12.99</retail>
</price>
<brand>Uniqlo</brand>
<shipping>
<cost currency="GBP">
<amount/>
<currency>GBP</currency>
</cost>
<information/>
<availability>In stock</availability>
</shipping>
<keywords/>
<upc/>
<m1/>
<pixel>http://ad.linksynergy.com/fs-bin/show?xxxxx</pixel>
</product>
-- snip --

My schema looks like this:
<?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="primary" dt:type="string"/>
<ElementType name="secondary" dt:type="string"/>
<ElementType name="product" dt:type="string"/>
<ElementType name="productImage" dt:type="string"/>
<ElementType name="short" dt:type="string"/>
<ElementType name="retail" dt:type="string"/>
<ElementType name="brand" dt:type="string"/>
<AttributeType name="name" dt:type="string"/>
<AttributeType name="sku_number" dt:type="string"/>
<AttributeType name="currency" dt:type="string"/>

<ElementType name="merchandiser" sql:is-constant="1">
<element type="product" />
</ElementType>

<ElementType name="product" sql:relation="AffiliatesDataLinkShare">
<attribute type="name" sql:field="ProductName" />
<attribute type="sku_number" sql:field="SKU" />
<element type="brand" sql:field="Brand"/>
<element type="category" />
<element type="URL"/>
<element type="description" />
<element type="price"/>
</ElementType>

<ElementType name="category" sql:is-constant="1">
<element type="primary" sql:field="PrimaryCategory"/>
<element type="secondary" sql:field="SecondaryCategory"/>
</ElementType>

<ElementType name="URL" sql:is-constant="1">
<element type="product" sql:field="ProductURL"/>
<element type="productImage" sql:field="ImageURL"/>
</ElementType>

<ElementType name="description" sql:is-constant="1">
<element type="short" sql:field="Description"/>
</ElementType>

<ElementType name="price" sql:is-constant="1">
<attribute type="currency" sql:field="Currency" />
<element type="retail" sql:field="Price" />
</ElementType>

</Schema>

As you can see, Product is an element within URL but also wrapping element...
I've no idea if it's something I can fix, or a limitation of BULK load, or a
violation of XML standards? Any help would be greatly appreciated!

Also, I believe I'm using XDR, which might be a bit out-dated? Is this true?

Thanks again.

External Since: Apr 02, 2009 Posts: 3

I've re-written my schema in XSD to see if it helped any... Unfortunately it
doesn't look like it has Sad

I get the error: "relationship expected on 'product'".

Any advice would be massively appreciated!

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">


<xs:element name="primary" type="xs:string"/>
<xs:element name="secondary" type="xs:string"/>
<xs:element name="product" type="xs:string"/>
<xs:element name="productImage" type="xs:string"/>
<xs:element name="short" type="xs:string"/>
<xs:element name="retail" type="xs:string"/>


<xs:attribute name="currency" type="xs:string"/>
<xs:attribute name="product_id" type="xs:string"/>
<xs:attribute name="name" type="xs:string"/>


<xs:element name="merchandiser">
<xs:complexType>
<xs:sequence>
<xs:element name="product">
<xs:complexType>
<xs:sequence>
<xs:element name="category">
<xs:complexType>
<xs:sequence>
<xs:element ref="primary"/>
<xs:element ref="secondary"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="URL">
<xs:complexType>
<xs:sequence>
<xs:element ref="product"/>
<xs:element ref="productImage"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="description">
<xs:complexType>
<xs:sequence>
<xs:element ref="short"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="price">
<xs:complexType>
<xs:sequence>
<xs:element ref="retail"/>
</xs:sequence>
<xs:attribute ref="currency"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute ref="product_id"/>
<xs:attribute ref="name"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

--
Message posted via http://www.sqlmonster.com