[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

SQLXMLBULKLOAD relationship expected

XSD schema (abridged version attached) to map the SQL fields to, however I am getting a "Schema: relationship expected on 'prod'." error. This occurs on the sub element "prod". I have fixed on other XML feeds in the past by setting sql:is-constant="1", however this time the feed is supplied with attributes and so cannot be set as constant. I have set the file sql table relationship to "merchant" element and I am looking to write the data to just one SQL table "InputGlobalFeed" and so I believe should not require a relationship attribute set. Any suggestions most welcome.
<xsd:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element sql:is-constant="1" name="merchantProductFeed">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element sql:relation="InputGlobalFeed" minOccurs="0" name="merchant">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element minOccurs="0" maxOccurs="unbounded" name="prod">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element sql:field="IGFProgramId" minOccurs="0" name="pId" />
                    <xsd:element sql:field="IGFModel" minOccurs="0" name="modelNumber" />
                    <xsd:element sql:is-constant="1" minOccurs="0" name="text">
                      <xsd:complexType>
                        <xsd:sequence>
                          <xsd:element sql:field="IGFMerchantProductName" minOccurs="0" name="name" />
                          <xsd:element sql:field="IGFProductDescription" minOccurs="0" name="desc" />
                        </xsd:sequence>
                      </xsd:complexType>
                    </xsd:element> 
                    <xsd:element sql:field="IGFDeliveryTime" minOccurs="0" name="delTime" />
                    <xsd:element sql:field="IGFProductAdded" minOccurs="0" name="valFrom" />
                    <xsd:element minOccurs="0" name="valTo" />
                    <xsd:element minOccurs="0" name="comAmount" />
                  </xsd:sequence>
                  <xsd:attribute sql:field="IGFMerchantProductId" name="id" use="optional" />
                  <xsd:attribute name="pre_order" use="optional" />
                  <xsd:attribute sql:field="IGFPromoText" name="web_offer" use="optional" />
                  <xsd:attribute sql:field="IGFInStock" name="in_stock" use="optional" />
                  <xsd:attribute name="stock_quantity" use="optional" />
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
            <xsd:attribute sql:field="IGFMerchantSiteId" name="id" use="optional" />
            <xsd:attribute sql:field="IGFMerchantName" name="name" use="optional" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Open in new window

0
Simon Cripps
Asked:
Simon Cripps
1 Solution
 
Simon CrippsOwnerAuthor Commented:
Have not been able to map this straight into one SQL table due to the nature of the XML given to me. As I did not want to tamper with the XML and insert a transform statement, I came to the solution of creating a new SQL table to mirror tyhe XML format, then after the mapping had taken place to the two table perform an SQL update to merger the data.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now