Problems with sql:overflow-field and sql:datatype="xml" in XML bulk load

I am bulk loading data from an XML file into three tables in SS2005. All is well except for a section that I would like to load into a column of type XML in the table.

I have tried using sql:datatype="xml" and it does load into the XML column but there are two problems:

1) It adds two namespace tags to each element which I don't want.

2) It does not include the outermost tag <Form> but starts with the next tag in.

I then tried using sql:overflow-field and have a problem with it, also.

1) It includes the parent tag <Study> which I do not want. If I try to put the overflow annotation on the <Form> tag I get an error that it sees that as a column.

Here is the XSD:

<?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">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="StudyLoc"
parent="Study"
parent-key="StudyID"
child="StudyLocation"
child-key="StudyID" />
<sql:relationship name="StudyDoc"
parent="Study"
parent-key="StudyID"
child="StudyDocument"
child-key="StudyID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Study" sql:relation="Study">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Form" sql:datatype="xml" sql:field="XMLForm" />
<xsd:element minOccurs="0" name="LocationIDs" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="int" type="xsd:integer" sql:relation="StudyLocation" sql:relationship="StudyLoc" sql:field="LocationID" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element minOccurs="0" name="Documents" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="StudyDocument" sql:relation="StudyDocument" sql:relationship="StudyDoc">
<xsd:complexType>
<xsd:attribute name="DocumentID" type="xsd:integer" sql:field="StudyDocumentID" />
<xsd:attribute name="DocumentName" type="xsd:string" />
<xsd:attribute name="DocumentLocation" type="xsd:string" />
<xsd:attribute name="DocumentStatusCode" type="xsd:string" sql:field="StudyDocumentStatusCode" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ID" type="xsd:integer" sql:field="StudyID" />
<xsd:attribute name="Name" type="xsd:string" sql:field="StudyTitle" />
<xsd:attribute name="AssessmentCount" type="xsd:integer" />
<xsd:attribute name="StartDate" type="xsd:date" sql:datatype="dateTime" sql:field="StudyStartDate" />
<xsd:attribute name="EndDate" type="xsd:date" sql:datatype="dateTime" sql:field="StudyEndDate" />
<xsd:attribute name="TypeID" type="xsd:string" sql:field="StudyTypeID" />
<xsd:attribute name="ProjectID" type="xsd:integer" />
<xsd:attribute name="StudyStatus" type="xsd:string" sql:field="StudyStatusCode" />
<xsd:attribute name="AssessmentsCompleted" type="xsd:integer" />
<xsd:attribute name="SampleTarget" type="xsd:integer" />
<xsd:attribute name="SamplesCollected" type="xsd:integer" />
<xsd:attribute name="LastModifiedDate" type="xsd:date" sql:datatype="dateTime" />
<xsd:attribute name="AutoApprove" type="xsd:boolean" sql:field="AutoApproveFlag" default="false" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

Here is the XML (the part in red is what I want loaded into the XML column):

<?xml version="1.0" encoding="utf-8"?>
<Study xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ID="124" Name="Test2" AssessmentCount="24" StartDate="2006-12-31T00:00:00" EndDate="2007-02-07T00:00:00" TypeID="1" ProjectID="2" StudyStatus="A" AssessmentsCompleted="0" SampleTarget="0" SamplesCollected="0" LastModifiedDate="0001-01-01T00:00:00" >
<Form>
<SiteInformation>
<Groups />
</SiteInformation>
<DataCollection>
<Groups>
<FormGroup Label="Data" ID="12" Required="false" Observation="true" ItemDetail="false" OrdinalPosition="1">
<Criteria>
<FormCriterion ID="4" Name="Default Text Field" Label="My Field" InputType="textbox" DependentOn="0" LastModifiedDate="2007-01-29T14:26:45" LastModifiedUser="inmar/cchacon" DefaultOrdinalPosition="3">
<Properties>
<FormCriterionProperty ID="28" Name="MinLength" Value="" />
<FormCriterionProperty ID="29" Name="MaxLength" Value="" />
<FormCriterionProperty ID="30" Name="DefaultValue" Value="" />
<FormCriterionProperty ID="31" Name="RegularExpression" Value="" />
</Properties>
<ListItems />
</FormCriterion>
<FormCriterion ID="5" Name="Default Check Box Group" Label="My Check" InputType="checkbox" DependentOn="0" LastModifiedDate="2006-11-27T08:32:00" LastModifiedUser="cchacon" DefaultOrdinalPosition="1">
<Properties />
<ListItems />
</FormCriterion>
<FormCriterion ID="41" Name="Sample Plan Criteria" Label="No More" InputType="planitem" DependentOn="0" LastModifiedDate="2006-12-08T16:48:12" LastModifiedUser="cchacon" DefaultOrdinalPosition="9">
<Properties />
<ListItems />
</FormCriterion>
</Criteria>
</FormGroup>
</Groups>
</DataCollection>
</Form>
<LocationIDs />
<Documents />
</Study>

And this is the table it's loading into:

CREATE TABLE Study
(
StudyID int IDENTITY (1,1) ,
StudyTitle varchar(30) NULL ,
AssessmentCount int NULL ,
StudyStartDate datetime NULL ,
StudyEndDate datetime NULL ,
StudyTypeID varchar(10) NULL ,
ProjectID int NULL ,
StudyStatusCode varchar(10) NULL ,
AssessmentsCompleted int NULL ,
SampleTarget int NULL ,
SamplesCollected int NULL ,
LastModifiedDate datetime NULL ,
AutoApproveFlag bit NOT NULL ,
XMLForm xml NULL ,
)
go

Any help would be appreciated! All the other XML files I've bulk loaded have worked great but loading a fragment into an XML column has not worked like I want it to.

Thanks!

You might try setting

minOccurs="0"

on the elements you do not want

|||

Thanks for the response. When I use sql:overflow-field on the Study tag to tell it to put all the unconsumed data, i.e., the <Form> tag and all it contains, into the XMLForm field, I get the <Study> tag with it since the overflow field picks up the parent tag.

If I try putting minOccurs=0 on the Study tag I get the error:

Attribute 'minOccurs' is not allowed on global 'element' declarations

If I try putting the overflow tag on the next level below Form, assuming I'll get Form then as the parent tag, I get an error that the Form tag needs a relation and relationship. And if I mark it as a constant, then it ignores it and I get Study as the parent tag again.

So if I use overflow I get a parent tag <Study> which I don't want and if I use xml I miss the <Form> tag which I do want.

|||ok, I am offically stumped. Perhaps if you checked with a pure XML forum they could help.