reduce databse size: foreign

Showing posts with label foreign. Show all posts

Wednesday, March 21, 2012

Referential Integrity - Which Column violates this?

Hi All,

I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

Any one can shed some light here? Many thanks!

CyherusDo you have any ddl that's behind the VB application? Can you match it with the values you want to insert?|||nope, I am basically connecting to the SQL server via odbc.. and using INSERT INTO queries to add records as I read from a text file..
Since the SQL Server is able to throw me an error description that says which table and column name violates the RI, I thought I would be able to manage this with codes. That is create the primary record in the primary table (affected table) and resume to insert the record in the foreign table again..

Cyherus|||It should be done with sp, not with FE-based action queries.|||The text of the error message (in the errors collection) will tell you which foreign key caused the problem. The code is the same for them all, so it only tells you that a foreign key was the problem.

-PatP|||If you do it in sp you can customize the way errors are returned to the client. And of course Errors (rdoErrors) collection should be looped to retrieve ALL the errors that came from the server.|||Alright.. as per your advise.. I am now performing these actions on the BE via sp.
now, I am using both return value, out parameters and capture @.@.ERROR.
Issue is that the system it prompting me before I can capture the errors returned.. sp example:

ALTER PROCEDURE dbo.sp_insert_bl
(
@.AAA varchar(12),
@.BBB int,
)
AS
DECLARE @.err int

SET NOCOUNT ON
BEGIN TRAN
INSERT INTO dbo.Table ([AAA], [BBB])
VALUES (@.AAA, @.BBB)
SELECT @.err = @.@.ERROR
IF @.err <> 0
BEGIN
ROLLBACK TRAN
RETURN @.err
END
ELSE
BEGIN
SELECT @.blid = SCOPE_IDENTITY()
COMMIT TRAN
RETURN 0
END

oh yes, using ado to execute exec the sp
cmd.parameter.........
cmd.execute
cmd.parameter("return value")

do you suggest using RAISERROR?|||Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?

Referential Integrity

Hi guys,

Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?

For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.

It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.

This is on behalf of a guy at work so "requirements" may change. ;)

Anyone got some ideas??

Cheers,
ShaunI see three options:

1) Try / Error approach: just delete, catch eventual error and handle it
2) In a SQL Server using ADo: Use OpenSchema to retrieve foreign key constraints
3) query SQL Server's schema tables

If these hints are not sufficient, I'll eloborate on your preferred option.|||There's always the "deleted item" option. Instead of removing it just mark it as deleted and then filter out deleted items in your search results. I used to think that was a lazy way of doing it, but now I kind of think it's helpful when dealing with product databases. Unless of course you expect to be removing many items over a short period of time.|||select object_name(fkeyid) from sysforeignkeys where rkeyid = object_id(tablename)

This will give you the table names which have a foreign key constraint on the table.|||what about the referential integrity option

the only reason i can think of why you'd want to select first before deleting the author, is if you had failed to properly implement referential integrity and were facing the prospect of perhaps leaving orphaned books around

with referential integrity declared, here's what you do

1. delete author

if this is successful, there were no books that would've been "orphaned"

if the delete is unsuccessful, there were, so then issue your error message|||thanks for all the suggestions guys. much appreciated. look like the same sort of stuff I was suggesting to the guy.

the reason he wants to do this is because he is expecting stupid users who won't understand what they are deleting by deleting a author (following the example).

I think he is intending to go with a deleted flag (soft delete) at this point.

Monday, March 12, 2012

Referencing a composite foreign key

I can't find any examples on how to do the below. Can anyone advise?
I have an 'Address' table which has a primary key constructed from
'postcode' and 'house no'.
I have numerous other tables that reference this table with a foreign
key - how do I reference a composite foreign key such as this - It
isn't a matter of just entering an integer as I won't know what the
composite value for a postcode and house no is.
Am I right in thinking that I need to make the 'postcode' and 'house
no' a composite UNIQUE to ensure these are unique and add an additional
integer primary key to the address entity which the other tables can
then reference?
Thanks,
DJWYou can reference a composite key via:
alter table MyTable
add constraint FK1_MyTable foreign key (postcode, houseno)
references OtherTable (postcode, houseno)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"dwj" <danielwatkinslearn@.hotmail.com> wrote in message
news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>I can't find any examples on how to do the below. Can anyone advise?
> I have an 'Address' table which has a primary key constructed from
> 'postcode' and 'house no'.
> I have numerous other tables that reference this table with a foreign
> key - how do I reference a composite foreign key such as this - It
> isn't a matter of just entering an integer as I won't know what the
> composite value for a postcode and house no is.
> Am I right in thinking that I need to make the 'postcode' and 'house
> no' a composite UNIQUE to ensure these are unique and add an additional
> integer primary key to the address entity which the other tables can
> then reference?
> Thanks,
> DJW
>|||How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>|||Precisely. As long as ('QW1 4BP', 90) exists in OtherTable, then the INSERT
will succeed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:438d4ecc$1_1@.glkas0286.greenlnk.net...
How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...
> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is
a
> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be
deleting.
>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> > Hi, Group
> >
> > Please let me know how can I do the following:
> >
> > Will prevent the user from deleting an entry if the value is used in a
> > foreign key.
> >
> > Best regard
> > Mario
> >
> >
>

Friday, March 9, 2012

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="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>