Reference ID problem for import to SQL

I want to import the XML into MS SQL using SQLXMLBulkLoad, this is working,
all data are insert into tables, but I want a reference element (foreign key
to a Id in a higher level element), that inherits a ID from another element.
I have a regular XML file and a mapping XSD file.
XML file:
<DKMaster>
<continent>
<name>Europa</name>
<country>
<name>Denmark</name>
<location>
<name>Kolding</name>
</location>
............ ' recursive
</country>
............ 'recursive
</continent>
..............' recursive
</DKMaster>
XSD file:
<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="Cont_Loc" parent="continent" parent-key="Id"
child="location" child-key="ContinentId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="location" sql:key-fields="Id" sql:relation="Location"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
<xsd:element name="ContinentId" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I want to reference the Continent/Id to Location/ContinentID, how is this
possible ?
Database structure:
Table Continent
- Id int P.K.
- Name varchar(50)
- CreationDate datetime
Table Location
- Id int P.K.
- Name varchar(50)
- CreationDate datetime
- ContinentId int F.K.
"Magni" wrote:

> I want to import the XML into MS SQL using SQLXMLBulkLoad, this is working,
> all data are insert into tables, but I want a reference element (foreign key
> to a Id in a higher level element), that inherits a ID from another element.
> I have a regular XML file and a mapping XSD file.
> XML file:
> <DKMaster>
> <continent>
> <name>Europa</name>
> <country>
> <name>Denmark</name>
> <location>
> <name>Kolding</name>
> </location>
> ............ ' recursive
> </country>
> ............ 'recursive
> </continent>
> ..............' recursive
> </DKMaster>
> XSD file:
> <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="Cont_Loc" parent="continent" parent-key="Id"
> child="location" child-key="ContinentId" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="location" sql:key-fields="Id" sql:relation="Location"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> <xsd:element name="ContinentId" type="xsd:integer" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I want to reference the Continent/Id to Location/ContinentID, how is this
> possible ?
|||Hi Magni,
The location element should be defined as a child of the continent element
in the schema.. (see the example below)
<xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
sql:relationship="Cont_Loc">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="location" ref="location" sql:relation="Continent"
sql:relationship="Cont_Loc"/> <<<<<<<<<<<<<<< add this
<xsd:element name="Id" type="xsd:integer" />
<xsd:element name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
However I see one more problem though..
Your data has
<continent>
<country>
<location>
In your schema you have not defined the <country> element though.
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Magni" <Magni@.discussions.microsoft.com> wrote in message
news:C95EEFDB-7C5E-4E4C-B6E0-3B1BE0F079F5@.microsoft.com...[vbcol=seagreen]
> Database structure:
> Table Continent
> - Id int P.K.
> - Name varchar(50)
> - CreationDate datetime
> Table Location
> - Id int P.K.
> - Name varchar(50)
> - CreationDate datetime
> - ContinentId int F.K.
> "Magni" wrote:
working,[vbcol=seagreen]
key[vbcol=seagreen]
element.[vbcol=seagreen]
recursive[vbcol=seagreen]
sql:relation="Continent"[vbcol=seagreen]
this[vbcol=seagreen]
|||"Chandra Kalyanaraman [MSFT]" wrote:

> Hi Magni,
> The location element should be defined as a child of the continent element
> in the schema.. (see the example below)
> <xsd:element name="continent" sql:key-fields="Id" sql:relation="Continent"
> sql:relationship="Cont_Loc">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="location" ref="location" sql:relation="Continent"
> sql:relationship="Cont_Loc"/> <<<<<<<<<<<<<<< add this
> <xsd:element name="Id" type="xsd:integer" />
> <xsd:element name="name" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> However I see one more problem though..
> Your data has
> <continent>
> <country>
> <location>
> In your schema you have not defined the <country> element though.
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Magni" <Magni@.discussions.microsoft.com> wrote in message
> news:C95EEFDB-7C5E-4E4C-B6E0-3B1BE0F079F5@.microsoft.com...
> working,
> key
> element.
> recursive
> sql:relation="Continent"
> this
>
>
Hi Chandra
I am ahaving a Same kind of Issue
<Student>
<School>
<ApCourse>
</ApCourse>
</School>
</Student>
I am trying to Insert into ApCourse, the value from School --> SchoolID
I tried with your IDea of Using a reference ,but its throwing me a Error
Hi,
I have to Insert into 3 tables data coming as a XML file
DistrictStudent PK--> Auto ID
SchoolStudent PK -->DistrictStudent_AutoId
Tablle is referenced by Foreign key
SchoolStudentAPCourse: FK_SchoolStudentAPCourse_SchoolStudent
SchoolStudentAPCourse PK -->SchoolStudentId
I am able to Insert Data into two tables, but when I want to Insert into the
third table( SchoolStudentAPCourse ), its giving me Error
Here is the Schema formy File
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:mstns="http://tempuri.org/XMLSchema1.xsd">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="DSSS"parent="DistrictStudent"
parent-key="AutoId"
child="SchoolStudent"
child-key="DistrictStudent_AutoId" />
<sql:relationship name="SchoolStudentAPCourse" parent="SchoolStudent"
parent-key="SchoolStudentId"
child="SchoolStudentApCourse"
child-key="SchoolStudentId"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:elementname="Student" sql:relation="DistrictStudent"
sql:key-fields="DataVersionId">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DataVersionId"
type="xsd:int"sql:field="DataVersionId" />
<xsd:element
name="SchoolDistrictOrganizationId"type="xsd:int"sql:field="SchoolDistrictOrganizationId" />
<xsd:element
name="DistrictStudentCode"type="xsd:string"sql:field="DistrictStudentCode" />
<xsd:element name="SSID"type="xsd:int"sql:field="SSID" />
<xsd:element name="SSN"type="xsd:string"sql:field="SSN" />
<xsd:element
name="FirstName"type="xsd:string"sql:field="FirstName"/>
<xsd:element
name="MiddleName"type="xsd:string"sql:field="MiddleName" />
<xsd:element
name="LastName"type="xsd:string"sql:field="LastName"/>
<xsd:element
name="BirthDate"type="xsd:date"sql:field="BirthDate" />
<xsd:element
name="GenderTypeCode"type="xsd:string"sql:field="GenderTypeCode"/>
<xsd:element
name="RaceTypeId"type="xsd:int"sql:field="RaceTypeId" />
<xsd:element
name="DisabililtyTypeId"type="xsd:int"sql:field="DisabilityTypeId"/>
<xsd:element
name="PrimaryLanguageId"type="xsd:int"sql:field="PrimaryLanguageId"/>
<xsd:element
name="LanguageSpokenAtHomeId"type="xsd:int"sql:field="LanguageSpokenAtHomeId"/>
<xsd:element
name="ZipCode"type="xsd:string"sql:field="ZipCode"/>
<xsd:element
name="ZipCodeExt"type="xsd:string"sql:field="ZipCodeExt"/>
<xsd:element
name="ExpectedGradYear"type="xsd:string"sql:field="ExpectedGradYear"/>
<xsd:element name="ResidentSchoolDistrictOrganizationId"
type="xsd:int"sql:field="ResidentSchoolDistrictOrganizationId"/>
<xsd:element
name="PreSchool"type="xsd:int"sql:field="PreSchool"/>
<xsd:element
name="IsHomeless"type="xsd:int"sql:field="IsHomeless"/>
<xsd:element
name="IsForeignExchange"type="xsd:int"sql:field="IsForeignExchange"/>
<xsd:element
name="IsHomeBasedStudentAttendingPartTime"type="xsd:int"sql:field="IsHomeBasedStudentAttendingPartTime"/>
<xsd:element
name="IsApprovedPrivateSchoolStudentAttendingPartT ime"type="xsd:int"sql:field="IsApprovedPrivateSchoolStudentAttending PartTime"/>
<xsd:element
name="DateEnrolledInDistrict"type="xsd:date"sql:field="DateEnrolledInDistrict"/>
<xsd:element
name="DateExitedDistrict"type="xsd:date"sql:field="DateExitedDistrict"/>
<xsd:element
name="ELLEnrollmentDate"type="xsd:date"sql:field="ELLEnrollmentDate"/>
<xsd:element
name="ELLExitDate"type="xsd:date"sql:field="IsApprovedPrivateSchoolStudentAttending PartTime"/>
<xsd:element
name="GradeLevelId"type="xsd:int"sql:field="GradeLevelId" />
<xsd:element name="GPA"type="xsd:decimal"sql:field="GPA"
/>
<xsd:element
name="FreeReducedMealStatus"type="xsd:int"sql:field="FreeReducedMealStatus" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy"/>
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated"/>
<xsd:element
name="MatchType"type="xsd:string"sql:field="MatchType"/>
<xsd:element name="School" sql:relation="SchoolStudent"
sql:key-fields="DistrictStudent_AutoId"sql:relationship="DSSS">
<xsd:complexType>
<xsd:sequence>
<!--<xsd:element name="SchoolStudentId" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xsd:long" />-->
<xsd:element
name="DataVersionId"type="xsd:int"sql:field="DataVersionId" />
<xsd:element
name="DistrictStudentCode"type="xsd:string"sql:field="DistrictStudentCode" />
<xsd:element
name="SchoolDistrictOrganizationId"type="xsd:int"sql:field="SchoolDistrictOrganizationId" />
<xsd:element
name="SchoolOrganizationId"type="xsd:int"sql:field="SchoolOrganizationId" />
<xsd:element
name="IsPrimarySchool"type="xsd:int"sql:field="IsPrimarySchool" />
<xsd:element
name="DateEnrolledInSchool"type="xsd:date"sql:field="DateEnrolledInSchool" />
<xsd:element
name="DateExitedSchool"type="xsd:date"sql:field="DateExitedSchool" />
<xsd:element
name="EnrollmentStatusTypeId"type="xsd:int"sql:field="EnrollmentStatusTypeId" />
<xsd:element
name="NumDaysAttended"type="xsd:decimal"sql:field="NumDaysAttended" />
<xsd:element
name="NumDaysEnrolled"type="xsd:decimal"sql:field="NumDaysEnrolled" />
<xsd:element
name="NumUnexcusedAbsences"type="xsd:int"sql:field="NumUnexcusedAbsences" />
<xsd:element
name="IsSchoolChoice"type="xsd:int"sql:field="IsSchoolChoice" />
<xsd:element
name="IsLAPReading"type="xsd:int"sql:field="IsLAPReading" />
<xsd:element
name="IsLAPMath"type="xsd:int"sql:field="IsLAPMath" />
<xsd:element
name="IsTASReading"type="xsd:int"sql:field="IsTASReading" />
<xsd:element
name="IsTASMath"type="xsd:int"sql:field="IsTASMath" />
<xsd:element
name="IsTitleIMigrant"type="xsd:int"sql:field="IsTitleIMigrant" />
<xsd:element
name="IsSection504"type="xsd:int"sql:field="IsSection504" />
<xsd:element
name="Is21stCentury"type="xsd:int"sql:field="Is21stCentury" />
<xsd:element
name="IsSupplementalServices"type="xsd:int"sql:field="IsSupplementalServices" />
<xsd:element name="IsGifted"type="xsd:int"sql:field="IsGifted" />
<xsd:element
name="IsBilingualProgram"type="xsd:int"sql:field="IsBilingualProgram" />
<xsd:element
name="IsSpecialEd"type="xsd:int"sql:field="IsSpecialEd" />
<xsd:element
name="SpecialEdLREPartATypeId"type="xsd:int"sql:field="SpecialEdLREPartATypeId" />
<xsd:element
name="SpecialEdLREPartBTypeId"type="xsd:int"sql:field="SpecialEdLREPartBTypeId" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy" />
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated" />
<xsd:element name="APCourses" sql:relation="SchoolStudentAPCourse"
sql:relationship="SchoolStudentAPCourse">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="APCourse"type="xsd:int"sql:field="APCourseId" />
<xsd:element
name="UpdatedBy"type="xsd:int"sql:field="UpdatedBy" />
<xsd:element
name="LastUpdated"type="xsd:date"sql:field="LastUpdated" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here is the Input File XML
<?xml version="1.0" encoding="utf-8" ?>
<SSID>
<Student>
<DataVersionId>5</DataVersionId>
<SchoolDistrictOrganizationId>206</SchoolDistrictOrganizationId>
<DistrictStudentCode>12345</DistrictStudentCode>
<SSID>123456789</SSID>
<SSN>123456789</SSN>
<FirstName>Cat</FirstName>
<MiddleName>Peka</MiddleName>
<LastName>Boo</LastName>
<BirthDate>12/31/1986</BirthDate>
<GenderTypeCode>f</GenderTypeCode>
<RaceTypeId>1</RaceTypeId>
<DisabililtyTypeId>1</DisabililtyTypeId>
<PrimaryLanguageId>2</PrimaryLanguageId>
<LanguageSpokenAtHomeId>1</LanguageSpokenAtHomeId>
<ZipCode>98123</ZipCode>
<ZipCodeExt>1234</ZipCodeExt>
<ExpectedGradYear>2006</ExpectedGradYear>
<ResidentSchoolDistrictOrganizationId>29103</ResidentSchoolDistrictOrganizationId>
<PreSchool>1</PreSchool>
<IsHomeless>1</IsHomeless>
<IsForeignExchange>1</IsForeignExchange>
<IsHomeBasedStudentAttendingPartTime>1</IsHomeBasedStudentAttendingPartTime>
<IsApprovedPrivateSchoolStudentAttendingPartTime>1 </IsApprovedPrivateSchoolStudentAttendingPartTime>
<DateEnrolledInDistrict>01/01/2002</DateEnrolledInDistrict>
<DateExitedDistrict />
<ELLEnrollmentDate />
<ELLExitDate />
<GradeLevelId>15</GradeLevelId>
<GPA>3.4</GPA>
<FreeReducedMealStatus>1</FreeReducedMealStatus>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/27/2004</LastUpdated>
<MatchType>S</MatchType>
<School>
<DataVersionId>5</DataVersionId>
<DistrictStudentCode>12345</DistrictStudentCode>
<SchoolDistrictOrganizationId>206</SchoolDistrictOrganizationId>
<SchoolOrganizationId>14</SchoolOrganizationId>
<IsPrimarySchool>1</IsPrimarySchool>
<DateEnrolledInSchool>07/22/2002</DateEnrolledInSchool>
<DateExitedSchool>09/25/2002</DateExitedSchool>
<EnrollmentStatusTypeId>4</EnrollmentStatusTypeId>
<NumDaysAttended>25.0</NumDaysAttended>
<NumDaysEnrolled>21.1</NumDaysEnrolled>
<NumUnexcusedAbsences>5</NumUnexcusedAbsences>
<IsSchoolChoice>2</IsSchoolChoice>
<IsLAPReading>3</IsLAPReading>
<IsLAPMath>4</IsLAPMath>
<IsTASReading>5</IsTASReading>
<IsTASMath>6</IsTASMath>
<IsTitleIMigrant>7</IsTitleIMigrant>
<IsSection504>8</IsSection504>
<Is21stCentury>9</Is21stCentury>
<IsSupplementalServices>0</IsSupplementalServices>
<IsGifted>1</IsGifted>
<IsBilingualProgram>2</IsBilingualProgram>
<IsSpecialEd>3</IsSpecialEd>
<SpecialEdLREPartATypeId>1</SpecialEdLREPartATypeId>
<SpecialEdLREPartBTypeId>3</SpecialEdLREPartBTypeId>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/25/2004</LastUpdated>
<APCourses>
<APCourse>1</APCourse>
<UpdatedBy>2</UpdatedBy>
<LastUpdated>08/25/2004</LastUpdated>
</APCourses>
</School>
</Student>
</SSID>