locked
sqlxml bulk load - schema relationship expected ... RRS feed

  • Question

  • Hi ...

    I am trying once again to load data from an xml file into a sql table using sqlxml com.  I have started small only trying to insert one column into the table.  I get the following error:

    Schema: relationship expected on 'country'.

    Below are the code details:

    Table:

    CREATE TABLE [CanadianDecoded] (
        [RecordID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
        [InsertDateTime] [datetime] NOT NULL CONSTRAINT [DF_CanadianDecoded_InsertedDateTime] DEFAULT (getdate()),
        [StationID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [ValidDateTime] [datetime] NULL ,
        [XMLDateTime] [datetime] NULL ,
        [Country] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
        CONSTRAINT [PK_CANDIANDecoded_U_C] PRIMARY KEY  CLUSTERED
        (
            [RecordID]
        ) WITH  FILLFACTOR = 70  ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    XML DATA:

    <?xml version='1.0' encoding='ISO-8859-1'?>
     <siteData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://dd.weatheroffice.gc.ca/EC_sites/schema/site.xsd">
     <location>
     <continent>North America</continent>
     <country code="cn">Canada</country>
     <province code="on">Ontario</province>
     <name code="s0000458">Toronto</name>
     <region>City of Toronto</region>
     </location>
     </siteData>

    XML SCHEMA:

    <xsdTongue Tiedchema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlnsTongue Tiedql="urnTongue Tiedchemas-microsoft-com:mapping-schema">
               
      <xsd:element name="siteData" sql:is-constant="1" >
        <xsd:complexType>
          <xsdTongue Tiedequence>
            <xsd:element name="location" sql:relation="CanadianDecoded">
              <xsd:complexType>
                <xsdTongue Tiedequence>
                  <xsd:element name="country" sql:relation="CanadianDecoded">
                    <xsd:complexType>
                      <xsd:attribute name="code" type="xsdTongue Tiedtring" sql:field="Country"/>
                    </xsd:complexType>
                  </xsd:element>
                </xsdTongue Tiedequence>
              </xsd:complexType>
            </xsd:element>
          </xsdTongue Tiedequence>
        </xsd:complexType>
      </xsd:element>
    </xsdTongue Tiedchema>           
               
    Thanks,
    Chris



    Wednesday, April 2, 2008 4:33 PM

Answers

  • Here is the schema in a code snippet ...


    Code Snippet
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
                
      <xsd:element name="siteData" sql:is-constant="1" >
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="location" sql:relation="CanadianDecoded">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="country" sql:relation="CanadianDecoded">
                    <xsd:complexType>
                      <xsd:attribute name="code" type="xsd:string" sql:field="Country"/>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:sequence>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>            
                


    Wednesday, April 2, 2008 4:36 PM

All replies

  • Here is the schema in a code snippet ...


    Code Snippet
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
                
      <xsd:element name="siteData" sql:is-constant="1" >
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="location" sql:relation="CanadianDecoded">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="country" sql:relation="CanadianDecoded">
                    <xsd:complexType>
                      <xsd:attribute name="code" type="xsd:string" sql:field="Country"/>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:sequence>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>            
                


    Wednesday, April 2, 2008 4:36 PM
  • The country element is a nested element of complex type.   So, SqlXml is expecting the country element to be a child table and expects a parent-child relationship annotation.

     

    However, since the nested country element is part the CanadianDecoded relation, you'll need to remove the sql:relation from the country element and add sql:is-constant="true".

     

    e.g.

    Code Snippet

     

           <xsd:element name="location" sql:relation="CanadianDecoded">
              <xsd:complexType>
                <xsd:sequence>
                  <xsd:element name="country" sql:is-constant="true">
                    <xsd:complexType>

     

     

     

    HTH,

    An

    Thursday, December 11, 2008 9:41 AM
  • Even this IS the "Answers", but it does NOT work.

    I created tables as above, copied xml file and xsd files, but SQLXML says:

    Schema: relationship expected on 'country'.
        at SQLXMLBULKLOADLib.ISQLXMLBulkLoad4.Execute(String bstrSchemaFile, Object vDataFile)


    Friday, September 28, 2012 12:41 AM
  • This solution does NOT work as well.

     Schema: the constant/fixed element 'country' can not have attributes.
        at SQLXMLBULKLOADLib.ISQLXMLBulkLoad4.Execute(String bstrSchemaFile, Object vDataFile)

    Friday, September 28, 2012 12:45 AM
  • Hi.  I'm facing the same error.  Were you able to resolve your issue?
    Monday, June 22, 2020 3:03 PM