|
Home > Archive > MS SQL XML > October 2006 > SQLXMLBulkLoad - relationship error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQLXMLBulkLoad - relationship error
|
|
| Trillium 2006-10-24, 6:49 pm |
| I am using SQLXMLBulkLoad 3 to insert data into SQL Server 2000 database. I
have SchemaGen and SGDropTables both true, so the tables are dropped and
created for the new data. Everything works fine except for one relationship
that causes an error. When I comment this relationship out of the schema,
the upload works perfectly - except of course that the inserted data cannot
be related because the item is not included.
The relationship in question includes a varchar(10) field (all the rest are
integers). The parent table is created and loads correctly. However the
child table is created as a varchar(1), and errors out as too small for the
values. Is there some problem with varchar relationships?
Here are the 2 fields from the tables created in the database:
From the parent table:
[RecordType] [varchar] (10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
From the child table:
[RecordType] [varchar] (1) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
Here is a snippet of the XSD showing element and annotations in question:
<!-- ... -->
<xsd:element name="ParentTable" sql:relation="tblUpldParentTable">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="tblUpldOrganization" parent-key="OrganizationId"
child="tblUpldParentTable" child-key="OrganizationId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildTable" sql:relation="tblUpldChildTable">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="tblUpldParentTable" parent-key="OrganizationId
FiscalYear RecordType" child="tblUpldChildTable" child-key="OrganizationId
FiscalYear RecordType" />
</xsd:appinfo>
</xsd:annotation>
....
<!-- ... from the ParentTable element attributes: -->
<xsd:attribute name="FiscalYear" type="xsd:integer" sql:field="FiscalYear"
sql:RecordType="bigint" />
<xsd:attribute name="RecordType" type="xsd:string" sql:field="RecordType"
sql:RecordType="varchar(10)" />
</xsd:complexType>
</xsd:element><!--ParentTable-->
| |
| Monica Frintu [MSFT] 2006-10-24, 6:49 pm |
| Hello,
You have to use sql:datatype annotation like this :
sql:datatype="varchar(10)" .
Best regards,
--
Monica Frintu
"Trillium" wrote:
> I am using SQLXMLBulkLoad 3 to insert data into SQL Server 2000 database. I
> have SchemaGen and SGDropTables both true, so the tables are dropped and
> created for the new data. Everything works fine except for one relationship
> that causes an error. When I comment this relationship out of the schema,
> the upload works perfectly - except of course that the inserted data cannot
> be related because the item is not included.
>
> The relationship in question includes a varchar(10) field (all the rest are
> integers). The parent table is created and loads correctly. However the
> child table is created as a varchar(1), and errors out as too small for the
> values. Is there some problem with varchar relationships?
>
> Here are the 2 fields from the tables created in the database:
> From the parent table:
> [RecordType] [varchar] (10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
> From the child table:
> [RecordType] [varchar] (1) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
>
>
> Here is a snippet of the XSD showing element and annotations in question:
>
>
> <!-- ... -->
> <xsd:element name="ParentTable" sql:relation="tblUpldParentTable">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="tblUpldOrganization" parent-key="OrganizationId"
> child="tblUpldParentTable" child-key="OrganizationId" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ChildTable" sql:relation="tblUpldChildTable">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="tblUpldParentTable" parent-key="OrganizationId
> FiscalYear RecordType" child="tblUpldChildTable" child-key="OrganizationId
> FiscalYear RecordType" />
> </xsd:appinfo>
> </xsd:annotation>
> ...
> <!-- ... from the ParentTable element attributes: -->
> <xsd:attribute name="FiscalYear" type="xsd:integer" sql:field="FiscalYear"
> sql:RecordType="bigint" />
> <xsd:attribute name="RecordType" type="xsd:string" sql:field="RecordType"
> sql:RecordType="varchar(10)" />
> </xsd:complexType>
> </xsd:element><!--ParentTable-->
>
>
>
| |
| Trillium 2006-10-24, 6:49 pm |
| Thanks for your input, and duh, I typo'd on that in copying the snippet -
sorry. It was stated correctly in the actual schema document though.
I was going on vacation when I posted the question, so I ended up having the
sender modify their schema to solve the problem with that particular field.
However, I have run into this before, so I still wonder if there is something
special about using a varchar (##) field as a key?
"Monica Frintu [MSFT]" wrote:
[color=darkred]
> Hello,
>
> You have to use sql:datatype annotation like this :
> sql:datatype="varchar(10)" .
>
> Best regards,
> --
> Monica Frintu
>
>
> "Trillium" wrote:
>
| |
| Monica Frintu [MSFT] 2006-10-24, 6:49 pm |
| No, there is nothing special about it.
If you take a look at : http://msdn2.microsoft.com/en-us/library/ms172699.aspx
you'll see that there is no such sql:RecordType annotation, you have to use
sql:datatype.
Regards,
--
Monica Frintu
"Trillium" wrote:
[color=darkred]
> Thanks for your input, and duh, I typo'd on that in copying the snippet -
> sorry. It was stated correctly in the actual schema document though.
>
> I was going on vacation when I posted the question, so I ended up having the
> sender modify their schema to solve the problem with that particular field.
> However, I have run into this before, so I still wonder if there is something
> special about using a varchar (##) field as a key?
>
>
> "Monica Frintu [MSFT]" wrote:
>
|
|
|
|
|