XSD to SQL DDL

gary8877 · Sep 8, 2006

My question is:

Is it possible to or is there a tool that can generate a SQL DDL Script or a Logical Data model from the XSD file. I am working on a Reporting DataStore project that requires conversion from XML to relational database for efficient reporting. I do have the XSD already. I want to:

1. Create a Data Model (SQL DDL) that can load the extracted XML data.
2. Extract XML from an XML data type column in SQL Server 2005 using XSD file.
3. Load the extracted XML data to Data Model.

Any suggestions will be appreciated.

Thanks
Gary

k5tm · Sep 8, 2006

You might have a look at the Altove XML Spy family here.

Tom Morrison

http://www.liant.com

gary8877 · Sep 10, 2006

Great! Thanks a lot Tom - You rock. Can you also please suggest which is the best way of extracting XML from SQL Server xml column? Can an XSLT convert XML to relational database?

Thanks
Gary

k5tm · Sep 11, 2006

Unfortunately, Gary, the XML-SQL interface is not an area in which I have any expertise. Almost all my work is strictly in XSLT. So, the answer to your first question is: No.

XSLT cannot directly convert XML to relational database (without the use of external functions, which are outside the scope of the XSLT standard). One could use an indirect method by going through a text file, but that is probably not a good solution unless your goals are quite limited in scope. What XML/XSLT processor are you using?

Tom Morrison

http://www.liant.com

gary8877 · Sep 14, 2006

Hi Tom
Thanks for the reply. I did look at XML Spy and it seems that it can:

1. Create a Data Model (SQL DDL) from XSD File.

2. XML Map Force another tool from Altova can genrate SQL Insert statement for inserting into the tables.

So that has been resolved with your previous answer.

But the problem is that the xsd file cannot be validated and thats why I am having problem converting it to SQL Database format. I get a message:

The schema is potentially invalid according to the following error(s):

Some of "include" and/or "import" and/or "redefine" statements in the following files have no schemaLocation attribute and will be ignored!
C:\Documents and Settings\SinghG\My Documents\CIAT Data Model\Aug 23 2006\schema0.xsd

Here is the xsd file. Can you please have a look and see if you can figure out the problem:
----------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="

http://www.w3.org/2001/XMLSchema"

xmlns:q1="

http://microsoft.com/wsdl/types/"

xmlns:q2="

http://microsoft.com/wsdl/types/"

xmlns:q3="

http://microsoft.com/wsdl/types/"

xmlns:q4="

http://microsoft.com/wsdl/types/"

xmlns:q5="

http://microsoft.com/wsdl/types/"

xmlns:q6="

http://microsoft.com/wsdl/types/"

xmlns:q7="

http://microsoft.com/wsdl/types/"

elementFormDefault="qualified">
<xs:import namespace="

http://microsoft.com/wsdl/types/"/>

<xs:element name="Assessment" type="Assessment" nillable="true"/>
<xs:complexType name="Assessment">
<xs:sequence>
<xs:element name="AssessmentID" type="q1:guid"/>
<xs:element name="AssessmentAltID" type="xs:string" minOccurs="0"/>
<xs:element name="RequestLogDate" type="xs:dateTime"/>
<xs:element name="CCACServiceRequired" type="xs:int"/>
<xs:element name="Client" type="Client" minOccurs="0"/>
<xs:element name="Referral" type="Referral" minOccurs="0"/>
<xs:element name="History" type="History" minOccurs="0"/>
<xs:element name="Outcomes" type="Outcomes" minOccurs="0"/>
<xs:element name="Supplementary" type="Supplementary" minOccurs="0"/>
<xs:element name="AdmissionStatus" type="AdmissionStatus" minOccurs="0"/>
<xs:element name="Signoff" type="Signoff" minOccurs="0"/>
<xs:element name="OtherInformation" type="OtherInformation" minOccurs="0"/>
<xs:element name="ScreeningItems" type="ScreeningItems" minOccurs="0"/>
<xs:element name="PostalCode" type="xs:string" nillable="true"/>
<xs:element name="CaseRecordNumber" type="xs:string" nillable="true"/>
<xs:element name="AgencyIdentifier" type="xs:string" nillable="true"/>
<xs:element name="RelatedAssessmentID" type="q2:guid"/>
<xs:element name="CatchmentOrganizationID" type="q3:guid"/>
<xs:element name="CatchmentOrganizationAltID" type="xs:string" minOccurs="0"/>
<xs:element name="AssessmentStartTypeDDID" type="xs:int"/>
<xs:element name="PurposeOfAxCopyDDID" type="xs:int"/>
<xs:element name="ReasonForCopyDDID" type="xs:int"/>
<xs:element name="CopyDescription" type="xs:string" nillable="true"/>
<xs:element name="AddressInCatchmentArea" type="xs:string" nillable="true"/>
<xs:element name="CaseManagerUserID" type="xs:int"/>
<xs:element name="OriginOfIntake" type="xs:int"/>
<xs:element name="AssessmentStatusDDID" type="xs:int"/>
<xs:element name="AssessmentResultStatusDDID" type="xs:int"/>
<xs:element name="AssessmentTypeDDID" type="xs:int"/>
<xs:element name="ConsentCollectData" type="xs:boolean"/>
<xs:element name="IsIntegrated" type="xs:boolean"/>
<xs:element name="IntegrationRequestCreatedInd" type="xs:boolean"/>
<xs:element name="OrganizationShortName" type="xs:string" nillable="true"/>
<xs:element name="ConsentShareDataDDID" type="xs:int"/>
<xs:element name="AssessmentReferenceDate" type="xs:string" nillable="true"/>
<xs:element name="ConsentShareDataDescription" type="xs:string" nillable="true"/>
<xs:element name="ConsentCollectDataDate" type="xs:dateTime"/>
<xs:element name="AddressInCatchmentAreaDDID" type="xs:int"/>
<xs:element name="TypeOfCommunicationAtIntakeInitial" type="xs:int"/>
<xs:element name="TypeOfCommunicationAtIntakeOther" type="xs:int"/>
<xs:element name="ResidentialStatus" type="xs:int"/>
<xs:element name="LivingArrangements" type="xs:int"/>
<xs:element name="AssessmentExpectedStartDate" type="xs:dateTime"/>
<xs:element name="SystemOutcomes" type="ArrayOfSystemOutcome" minOccurs="0"/>
<xs:element name="AssessingOrganizationID" type="q4:guid"/>
<xs:element name="AssessingOrganizationAltID" type="xs:string" minOccurs="0"/>
<xs:element name="CopySourceAssessmentID" type="q5:guid"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Client">
<xs:sequence>
<xs:element name="Clientid" type="q6:guid"/>
<xs:element name="FirstName" type="xs:string" nillable="true"/>
<xs:element name="Surname" type="xs:string" nillable="true"/>
<xs:element name="SecondName" type="xs:string" nillable="true"/>
<xs:element name="Gender" type="xs:int"/>
<xs:element name="BirthDate" type="xs:dateTime"/>
<xs:element name="InquiryAdultServices" type="xs:boolean"/>
<xs:element name="PediatricClientDescription" type="xs:string" nillable="true"/>
<xs:element name="NumericIdentifiers" type="NumericIdentifiers" minOccurs="0"/>
<xs:element name="ClientStatus" type="xs:int"/>
<xs:element name="ClientOriginalStatus" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="NumericIdentifiers">
<xs:sequence>
<xs:element name="HealthNumber" type="xs:string" nillable="true"/>
<xs:element name="HealthCardVersionCode" type="xs:string" nillable="true"/>
<xs:element name="ProvinceID" type="xs:int"/>
<xs:element name="ValidationCode" type="xs:int"/>
<xs:element name="ReasonNoOHIP" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Referral">
<xs:sequence>
<xs:element name="InitiateOrContinueRehabilitationServices" type="xs:int"/>
<xs:element name="InitiateOrContinuePalliativeServices" type="xs:int"/>
<xs:element name="ReferralReason" type="ArrayOfReferralReason" minOccurs="0"/>
<xs:element name="Treatments" type="Treatments" minOccurs="0"/>
<xs:element name="RequestSubmitDate" type="xs:dateTime"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ArrayOfReferralReason">
<xs:sequence>
<xs:element name="ReferralReasons" type="ReferralReason" nillable="true" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ReferralReason">
<xs:sequence>
<xs:element name="Description" type="xs:string" nillable="true"/>
<xs:element name="ReferralDetail" type="ReferralDetails" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="ID" type="q7:guid" use="required"/>
</xs:complexType>
<xs:complexType name="ReferralDetails">
<xs:sequence>
<xs:element name="Duration" type="ReferralDetailDuration" minOccurs="0"/>
<xs:element name="Severity" type="ReferralDetailSeverity" minOccurs="0"/>
<xs:element name="Frequency" type="ReferralDetailFrequency" minOccurs="0"/>
<xs:element name="Location" type="xs:string" minOccurs="0"/>
<xs:element name="AggravatingFactors" type="xs:string" minOccurs="0"/>
<xs:element name="RelievingFactors" type="xs:string" minOccurs="0"/>
<xs:element name="HasData" type="xs:boolean"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ReferralDetailDuration">
<xs:sequence>
<xs:element name="Description" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="ReferralDetailSeverity">
<xs:sequence>
<xs:element name="Description" type="xs:string" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="ReferralDetailFrequency">
<xs:sequence>
<xs:element name="Description" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="Treatments">
<xs:sequence>
<xs:element name="AdministrationOfMedication" type="xs:int"/>
<xs:element name="CentralLineManagement" type="xs:int"/>
<xs:element name="Dialysis" type="xs:int"/>
<xs:element name="FeedingTubeManagement" type="xs:int"/>
<xs:element name="IVTherapy" type="xs:int"/>
<xs:element name="NarcoticAnalgesicManagement" type="xs:int"/>
<xs:element name="NephostromyTubeManagement" type="xs:int"/>
<xs:element name="OstomyManagement" type="xs:int"/>
<xs:element name="Oxygen" type="xs:int"/>
<xs:element name="RespiratoryTherapy" type="xs:int"/>
<xs:element name="UrinaryCatheterManagement" type="xs:int"/>
<xs:element name="WoundCare" type="xs:int"/>
<xs:element name="OtherSpecify" type="xs:boolean"/>
<xs:element name="Other1" type="xs:int"/>
<xs:element name="Other2" type="xs:int"/>
<xs:element name="Other3" type="xs:int"/>
<xs:element name="Other4" type="xs:int"/>
<xs:element name="OtherNote1" type="xs:string" nillable="true"/>
<xs:element name="OtherNote2" type="xs:string" nillable="true"/>
<xs:element name="OtherNote3" type="xs:string" nillable="true"/>
<xs:element name="OtherNote4" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="History">
<xs:sequence>
<xs:element name="SurgeryInLast90Days" type="xs:int"/>
<xs:element name="TimeSinceLastHospitalStay" type="xs:int"/>
<xs:element name="EmergencyDeptCode" type="xs:int"/>
<xs:element name="InstrumentalActivitiesMealPreparation" type="xs:int"/>
<xs:element name="InstrumentalActivitiesOrdinaryHousework" type="xs:int"/>
<xs:element name="InstrumentalActivitiesManagingMedication" type="xs:int"/>
<xs:element name="InstrumentalActivitiesStairs" type="xs:int"/>
<xs:element name="StatusWorseThan90DaysAgo" type="xs:int"/>
<xs:element name="NutritionalIssuesNoticeableDecrease" type="xs:int"/>
<xs:element name="NutritionalIssuesWeightLoss" type="xs:int"/>
<xs:element name="NutritionalIssuesSpecialDiet" type="xs:int"/>
<xs:element name="PhysiologicalMeasurements" type="xs:boolean"/>
<xs:element name="Height" type="PhysiologicalMeasurement" minOccurs="0"/>
<xs:element name="Weight" type="PhysiologicalMeasurement" minOccurs="0"/>
<xs:element name="ModeOfIntake" type="xs:int"/>
<xs:element name="PainSymptomsFrequency" type="xs:int"/>
<xs:element name="PainSymptomsIntensity" type="xs:int"/>
<xs:element name="MostSeverePressureUlcer" type="xs:int"/>
<xs:element name="PresenceOfSkinUlcer" type="xs:int"/>
<xs:element name="MajorSkinProblems" type="xs:int"/>
<xs:element name="TraumaticInjury" type="xs:int"/>
<xs:element name="Vision" type="xs:int"/>
<xs:element name="Comprehension" type="xs:int"/>
<xs:element name="MoreImpairedDecisionMakingThan90DaysAgo" type="xs:int"/>
<xs:element name="SelfReportedMood" type="xs:int"/>
<xs:element name="BehaviourSymptoms" type="xs:int"/>
<xs:element name="Falls" type="xs:int"/>
<xs:element name="SmokesTobaccoDaily" type="xs:int"/>
<xs:element name="InformalHelpers" type="InformalHelpers" minOccurs="0"/>
<xs:element name="TreatmentsAndServicesIVTherapy" type="xs:int"/>
<xs:element name="TreatmentsAndServicesWoundCare" type="xs:int"/>
<xs:element name="ProblemFrequency" type="ProblemFrequency" minOccurs="0"/>
<xs:element name="DiseaseControl" type="DiseaseControl" minOccurs="0"/>
<xs:element name="MajorSkinProblemsDescription" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="PhysiologicalMeasurement">
<xs:sequence>
<xs:element name="Scale" type="xs:int"/>
<xs:element name="Quantity" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="InformalHelpers">
<xs:sequence>
<xs:element name="PrimaryHelperReport" type="xs:int"/>
<xs:element name="FamilyOrCloseFriendReport" type="xs:int"/>
<xs:element name="Primary" type="InformHelper" minOccurs="0"/>
<xs:element name="Secondary" type="InformHelper" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="InformHelper">
<xs:sequence>
<xs:element name="Surname" type="xs:string" nillable="true"/>
<xs:element name="FirstName" type="xs:string" nillable="true"/>
<xs:element name="LivesWithPerson" type="xs:int"/>
<xs:element name="RelationshipToPerson" type="xs:int"/>
<xs:element name="Phone" type="xs:string" nillable="true"/>
<xs:element name="SameAsHistorySourceContact" type="xs:int"/>
<xs:element name="ReceivingCCACServises" type="xs:int"/>
<xs:element name="PrimaryCaregiverToOtherPerson" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ProblemFrequency">
<xs:sequence>
<xs:element name="ChestPain" type="xs:int"/>
<xs:element name="PeripheralEdema" type="xs:int"/>
<xs:element name="NewOrWorseningCough" type="xs:int"/>
<xs:element name="Dizziness" type="xs:int"/>
<xs:element name="CaseFindingQuestionnaire" type="CaseFindingQuest" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="CaseFindingQuest">
<xs:sequence>
<xs:element name="ContactTraveledDestination" type="xs:string" nillable="true"/>
<xs:element name="ContactTraveled" type="xs:int"/>
<xs:element name="TraveledDestination" type="xs:string" nillable="true"/>
<xs:element name="Traveled" type="xs:int"/>
<xs:element name="Temp38C" type="xs:boolean"/>
<xs:element name="FeelingFeverish" type="xs:int"/>
<xs:element name="NewWorthCough" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="DiseaseControl">
<xs:sequence>
<xs:element name="Diseases" type="ArrayOfDisease" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ArrayOfDisease">
<xs:sequence>
<xs:element name="Diseases" type="Disease" nillable="true" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Disease">
<xs:sequence>
<xs:element name="ICD10Code" type="xs:string" nillable="true"/>
<xs:element name="DiseaseCode" type="xs:string" nillable="true"/>
<xs:element name="Diagnosis" type="xs:string" nillable="true"/>
<xs:element name="PrimaryInd" type="xs:boolean"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Outcomes">
<xs:sequence>
<xs:element name="SourceOfInformation" type="SourceOfInformation" minOccurs="0"/>
<xs:element name="ServiceUrgency" type="ServiceUrgency" minOccurs="0"/>
<xs:element name="AssessmentUrgency" type="xs:int"/>
<xs:element name="RequireShortTermServices" type="xs:int"/>
<xs:element name="ExpectedLengthOfStay" type="xs:int"/>
<xs:element name="ClientTypeInHomeServices" type="xs:int"/>
<xs:element name="ClientTypePlacementServices" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SourceOfInformation">
<xs:sequence>
<xs:element name="OtherDescription" type="xs:string" nillable="true"/>
<xs:element name="CommunitySupportAgencyDescription" type="xs:string" nillable="true"/>
<xs:element name="Other" type="xs:int"/>
<xs:element name="Hospital" type="xs:int"/>
<xs:element name="CommunitySupportAgency" type="xs:int"/>
<xs:element name="OtherHomeCare" type="xs:int"/>
<xs:element name="OfficeStaff" type="xs:int"/>
<xs:element name="Physician" type="xs:int"/>
<xs:element name="NonRelative" type="xs:int"/>
<xs:element name="Otherrelative" type="xs:int"/>
<xs:element name="Child" type="xs:int"/>
<xs:element name="Spouse" type="xs:int"/>
<xs:element name="Client" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ServiceUrgency">
<xs:sequence>
<xs:element name="Other" type="xs:string" nillable="true"/>
<xs:element name="SpeechLanguageTherapy" type="xs:int"/>
<xs:element name="SocialWork" type="xs:int"/>
<xs:element name="PlacementCoordinationServices" type="xs:int"/>
<xs:element name="LabServices" type="xs:int"/>
<xs:element name="Dietician" type="xs:int"/>
<xs:element name="OccupationalTherapy" type="xs:int"/>
<xs:element name="Physiotherapy" type="xs:int"/>
<xs:element name="PersonalSupport" type="xs:int"/>
<xs:element name="Nursing" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Supplementary">
<xs:sequence>
<xs:element name="SafetyIssues" type="SafetyIssues" minOccurs="0"/>
<xs:element name="DocumentedDoNotResucitateDateOfOrder" type="xs:dateTime"/>
<xs:element name="DocumentedDoNotResucitate" type="xs:int"/>
<xs:element name="DocumentedAdvancedMedicalDirectivesDateOfOrder" type="xs:dateTime"/>
<xs:element name="DocumentedAdvancedMedicalDirectives" type="xs:int"/>
<xs:element name="SurprisedIfClientDiedWithin12Months" type="xs:int"/>
<xs:element name="OtherHealthConcerns" type="xs:string" nillable="true"/>
<xs:element name="OtherHealthConcernsDDID" type="xs:int"/>
<xs:element name="NumberOfMedications" type="xs:int"/>
<xs:element name="Allergies" type="xs:string" nillable="true"/>
<xs:element name="AllergiesDDID" type="xs:int"/>
<xs:element name="FormalSupportSystemCurrentlyInPlace" type="FormalSupportSystemCurrentlyInPlace" minOccurs="0"/>
<xs:element name="RespiratoryDevices" type="RespiratoryDevices" minOccurs="0"/>
<xs:element name="BladderOrBowelIncontinence" type="BladderOrBowelIncontinence" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SafetyIssues">
<xs:sequence>
<xs:element name="DDID" type="xs:int"/>
<xs:element name="Description" type="xs:string" nillable="true"/>
<xs:element name="Other" type="xs:boolean"/>
<xs:element name="SuspectSubstanceAbuse" type="xs:boolean"/>
<xs:element name="SmokingInHome" type="xs:boolean"/>
<xs:element name="PhysicalEnvironment" type="xs:boolean"/>
<xs:element name="Pets" type="xs:boolean"/>
<xs:element name="Oxygen" type="xs:boolean"/>
<xs:element name="NeedForAdaptiveDevices" type="xs:boolean"/>
<xs:element name="IllegalMaterials" type="xs:boolean"/>
<xs:element name="AdequactOfEnvironmentalHeating" type="xs:boolean"/>
<xs:element name="AdequacyOfEnvironmentalCooling" type="xs:boolean"/>
<xs:element name="AbuseToClient" type="xs:boolean"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="FormalSupportSystemCurrentlyInPlace">
<xs:sequence>
<xs:element name="Frequency" type="xs:int"/>
<xs:element name="Description" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="RespiratoryDevices">
<xs:sequence>
<xs:element name="Other" type="UseOfRespiratoryDevice" minOccurs="0"/>
<xs:element name="Defibrillator" type="UseOfRespiratoryDevice" minOccurs="0"/>
<xs:element name="PaceMaker" type="UseOfRespiratoryDevice" minOccurs="0"/>
<xs:element name="Ventilator" type="UseOfRespiratoryDevice" minOccurs="0"/>
<xs:element name="Nebulizer" type="UseOfRespiratoryDevice" minOccurs="0"/>
<xs:element name="CPAP" type="UseOfRespiratoryDevice" minOccurs="0"/>
<xs:element name="Oxygen" type="UseOfRespiratoryDevice" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="UseOfRespiratoryDevice">
<xs:sequence>
<xs:element name="AssistentRequiredID" type="xs:int"/>
<xs:element name="Description" type="xs:string" minOccurs="0"/>
<xs:element name="Name" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="checked" type="xs:boolean" use="required"/>
</xs:complexType>
<xs:complexType name="BladderOrBowelIncontinence">
<xs:sequence>
<xs:element name="UseOfBladderBowelInPast3Days" type="UseOfBladderBowelInPast3Days" minOccurs="0"/>
<xs:element name="Bowel" type="xs:int"/>
<xs:element name="Bladder" type="xs:int"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="UseOfBladderBowelInPast3Days">
<xs:sequence>
<xs:element name="Bladder" type="Devices" minOccurs="0"/>
<xs:element name="Bowel" type="Devices" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Devices">
<xs:sequence>
<xs:element name="Ostomy" type="Device" minOccurs="0"/>
<xs:element name="Catheter" type="Device" minOccurs="0"/>
<xs:element name="PadsDiapers" type="Device" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Device">
<xs:sequence>
<xs:element name="Type" type="xs:string" nillable="true"/>
<xs:element name="PersistenceDDID" type="xs:int"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="AdmissionStatus">
<xs:sequence>
<xs:element name="HighIntensityNeedsDDID" type="xs:int"/>
<xs:element name="ClientRiskStatus" type="xs:int"/>
<xs:element name="ExpectedDischargeDate" type="xs:dateTime"/>
<xs:element name="NonAdmit" type="NonAdmit" minOccurs="0"/>
<xs:element name="Admit" type="Admit" minOccurs="0"/>
<xs:element name="HighIntensityNeeds" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="NonAdmit">
<xs:sequence>
<xs:element name="HomeNotSuitable" type="xs:boolean"/>
<xs:element name="NeedsMetAsOutPatient" type="xs:boolean"/>
<xs:element name="CriteriaServiceNotRequired" type="xs:boolean"/>
<xs:element name="NoOHIPCoverage" type="xs:boolean"/>
<xs:element name="NoProgressExpected" type="xs:boolean"/>
<xs:element name="RequiredServicesNotAvailable" type="xs:boolean"/>
<xs:element name="ConditionInappropriate" type="xs:boolean"/>
<xs:element name="FamilyParticipationNotAvailable" type="xs:boolean"/>
<xs:element name="ConditionChanged" type="xs:boolean"/>
<xs:element name="RefusedHomeCare" type="xs:boolean"/>
<xs:element name="AssessedForOtherProgram" type="xs:boolean"/>
<xs:element name="ReferredToHomeSupport" type="xs:boolean"/>
<xs:element name="ReferredToInstitution" type="xs:boolean"/>
<xs:element name="Other" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Admit">
<xs:sequence>
<xs:element name="AdmissionDate" type="xs:dateTime"/>
<xs:element name="AdmissionDelayReasons" type="AdmissionDelayReasons" minOccurs="0"/>
<xs:element name="ClientInCCACDesignatedArea" type="xs:boolean"/>
<xs:element name="ServiceWillResultInProgress" type="xs:boolean"/>
<xs:element name="FamilyWillParticipate" type="xs:boolean"/>
<xs:element name="HomeSuitable" type="xs:boolean"/>
<xs:element name="InNeedOfCriteriaService" type="xs:boolean"/>
<xs:element name="CannotBeOutpatient" type="xs:boolean"/>
<xs:element name="TreatAdequatelyAtHome" type="xs:boolean"/>
<xs:element name="UnderMedicalSupervisionForControlledAct" type="xs:boolean"/>
<xs:element name="ValidOHIP" type="xs:boolean"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="AdmissionDelayReasons">
<xs:sequence>
<xs:element name="Other" type="xs:string" nillable="true"/>
<xs:element name="EquipmentnotAvailable" type="xs:boolean"/>
<xs:element name="ServicesNotImmediatelyAvailable" type="xs:boolean"/>
<xs:element name="HospitalDischargeDelayed" type="xs:boolean"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Signoff">
<xs:sequence>
<xs:element name="AssessorSignoffs" type="ArrayOfAssessorSignoff" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ArrayOfAssessorSignoff">
<xs:sequence>
<xs:element name="AssessorSignoffs" type="AssessorSignoff" nillable="true" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="AssessorSignoff">
<xs:sequence>
<xs:element name="Date" type="xs:dateTime"/>
<xs:element name="AssessorName" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="AssessorID" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="OtherInformation">
<xs:sequence>
<xs:element name="NursePractitioner" type="NursePractitioner" minOccurs="0"/>
<xs:element name="HistorySource" type="HistorySource" minOccurs="0"/>
<xs:element name="SurgicalProcedure" type="SurgicalProcedure" minOccurs="0"/>
<xs:element name="WSIB" type="WSIB" minOccurs="0"/>
<xs:element name="Liability" type="Liability" minOccurs="0"/>
<xs:element name="ODBIssued" type="xs:int"/>
<xs:element name="HospitalLengthOfStay" type="xs:int"/>
<xs:element name="ResidenceCode" type="xs:string" nillable="true"/>
<xs:element name="PriorCode" type="xs:string" nillable="true"/>
<xs:element name="TreatmentUnitType" type="xs:int"/>
<xs:element name="TreatmentSite" type="xs:int"/>
<xs:element name="CulturaConsideration" type="xs:string" nillable="true"/>
<xs:element name="PreferedLanguage" type="xs:string" nillable="true"/>
<xs:element name="InterpreterNeeded" type="xs:int"/>
<xs:element name="LanguageBarrier" type="xs:int"/>
<xs:element name="PrimaryLanguage" type="xs:string" nillable="true"/>
<xs:element name="ReceivedCCAC" type="xs:int"/>
<xs:element name="TreatmentAddress" type="TreatmentAddress" minOccurs="0"/>
<xs:element name="ContactInfo" type="ContactInfo" minOccurs="0"/>
<xs:element name="ReferringPhysician" type="ReferringPhysician" minOccurs="0"/>
<xs:element name="AttendingPhysician" type="AttendingPhysician" minOccurs="0"/>
<xs:element name="FamilyPhysician" type="FamilyPhysician" minOccurs="0"/>
<xs:element name="UniquecaseIdentifier" type="xs:string" nillable="true"/>
<xs:element name="District" type="xs:int"/>
<xs:element name="Branch" type="xs:int"/>
<xs:element name="Program" type="xs:int"/>
<xs:element name="CAF" type="xs:string" nillable="true"/>
<xs:element name="PatientSubsidyCode" type="xs:int"/>
<xs:element name="PrimaryDiagnosisICDCode" type="xs:int"/>
<xs:element name="SecondaryDiagnosisICDCode" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="NursePractitioner">
<xs:sequence>
<xs:element name="OtherInformationPhysician" type="OtherInformationPhysician" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="OtherInformationPhysician">
<xs:sequence>
<xs:element name="Surname" type="xs:string" nillable="true"/>
<xs:element name="Fax" type="xs:string" nillable="true"/>
<xs:element name="Address" type="xs:string" nillable="true"/>
<xs:element name="Speciality" type="xs:string" nillable="true"/>
<xs:element name="BillingNumber" type="xs:string" nillable="true"/>
<xs:element name="BillingNumber1" type="xs:string" nillable="true"/>
<xs:element name="PhoneNumber" type="xs:string" nillable="true"/>
<xs:element name="FirstName" type="xs:string" nillable="true"/>
<xs:element name="Province" type="xs:int" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="HistorySource">
<xs:sequence>
<xs:element name="PrimarySource" type="PrimarySource" minOccurs="0"/>
<xs:element name="SecondarySource" type="SecondarySource" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="PrimarySource">
<xs:sequence>
<xs:element name="OtherInformationSource" type="OtherInformationSource" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="OtherInformationSource">
<xs:sequence>
<xs:element name="Surname" type="xs:string" nillable="true"/>
<xs:element name="Relationship" type="xs:int"/>
<xs:element name="Phone2" type="xs:string" nillable="true"/>
<xs:element name="Phone1" type="xs:string" nillable="true"/>
<xs:element name="FirstName" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SecondarySource">
<xs:sequence>
<xs:element name="OtherInformationSource" type="OtherInformationSource" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SurgicalProcedure">
<xs:sequence>
<xs:element name="Condition" type="xs:int"/>
<xs:element name="SurgeryCCPCodes" type="xs:string" nillable="true"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="WSIB">
<xs:sequence>
<xs:element name="Province" type="xs:int"/>
<xs:element name="Phone" type="xs:string" nillable="true"/>
<xs:element name="PostalCode" type="xs:string" nillable="true"/>
<xs:element name="MuniciaplityName" type="xs:string" nillable="true"/>
<xs:element name="AppartmentEntry" type="xs:string" nillable="true"/>
<xs:element name="NumberStreet" type="xs:string" nillable="true"/>
<xs:element name="EmployeeNameAddress" type="xs:string" nillable="true"/>
<xs:element name="DateOfAccident" type="xs:dateTime"/>
<xs:element name="ReferenceNumber" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Liability">
<xs:sequence>
<xs:element name="InsuranceCompanyNameClaimNumber" type="xs:string" nillable="true"/>
<xs:element name="WSIB" type="xs:int"/>
<xs:element name="Other" type="xs:int"/>
<xs:element name="LiabilityLiability" type="xs:int"/>
<xs:element name="ThirdPerson" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="TreatmentAddress">
<xs:sequence>
<xs:element name="OtherInformationAddress" type="OtherInformationAddress" minOccurs="0"/>
<xs:element name="SameAsPermanent" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="OtherInformationAddress">
<xs:sequence>
<xs:element name="UnitNumber" type="xs:string" minOccurs="0"/>
<xs:element name="StreetNumber" type="xs:string" minOccurs="0"/>
<xs:element name="StreetNumberSuffixDDID" type="xs:int" nillable="true"/>
<xs:element name="StreetName" type="xs:string" minOccurs="0"/>
<xs:element name="StreetType" type="xs:int" nillable="true"/>
<xs:element name="StreetDirection" type="xs:int" nillable="true"/>
<xs:element name="AccessNumber" type="xs:string" minOccurs="0"/>
<xs:element name="AddressLine1" type="xs:string" minOccurs="0"/>
<xs:element name="AddressLine2" type="xs:string" minOccurs="0"/>
<xs:element name="MunicipalityName" type="xs:string" minOccurs="0"/>
<xs:element name="Province" type="xs:int" nillable="true"/>
<xs:element name="PostalCode" type="xs:string" minOccurs="0"/>
<xs:element name="Country" type="xs:int" nillable="true"/>
<xs:element name="HomePhoneAreaCode" type="xs:string" minOccurs="0"/>
<xs:element name="HomePhoneNumber" type="xs:string" minOccurs="0"/>
<xs:element name="WorkPhoneAreaCode" type="xs:string" minOccurs="0"/>
<xs:element name="WorkPhoneNumber" type="xs:string" minOccurs="0"/>
<xs:element name="WorkPhoneExtensionNumber" type="xs:string" minOccurs="0"/>
<xs:element name="Branch" type="xs:string" minOccurs="0"/>
<xs:element name="MajorIntersection" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ContactInfo">
<xs:sequence>
<xs:element name="Contact2" type="Contact2" minOccurs="0"/>
<xs:element name="Contact1" type="Contact1" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="ddid" type="xs:int" use="required"/>
</xs:complexType>
<xs:complexType name="Contact2">
<xs:sequence>
<xs:element name="OtherInformationContact" type="OtherInformationSource" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="Contact1">
<xs:sequence>
<xs:element name="OtherInformationContact" type="OtherInformationSource" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ReferringPhysician">
<xs:sequence>
<xs:element name="OtherInformationPhysician" type="OtherInformationPhysician" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="AttendingPhysician">
<xs:sequence>
<xs:element name="OtherInformationPhysician" type="OtherInformationPhysician" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="FamilyPhysician">
<xs:sequence>
<xs:element name="OtherInformationPhysician" type="OtherInformationPhysician" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ScreeningItems">
<xs:sequence>
<xs:element name="CognitiveSkillsForDailyDecisionMaking" type="xs:int"/>
<xs:element name="Dyspnea" type="xs:int"/>
<xs:element name="SelfReportedHealth" type="xs:int"/>
<xs:element name="ActivitiesOfDailyLivingBathingDDID" type="xs:int"/>
<xs:element name="ActivitiesOfDailyLivingBathing" type="xs:string" nillable="true"/>
<xs:element name="ActivitiesOfDailyLivingPersonalHygieneDDID" type="xs:int"/>
<xs:element name="ActivitiesOfDailyLivingPersonalHygiene" type="xs:string" nillable="true"/>
<xs:element name="ActivitiesOfDailyLivingToiletUseDDID" type="xs:int"/>
<xs:element name="ActivitiesOfDailyLivingDressingLowerBodyDDID" type="xs:int"/>
<xs:element name="ActivitiesOfDailyLivingDressingLowerBody" type="xs:string" nillable="true"/>
<xs:element name="ActivitiesOfDailyLivingToiletUse" type="xs:string" nillable="true"/>
<xs:element name="ActivitiesOfDailyLivingLocomotionDDID" type="xs:int"/>
<xs:element name="ActivitiesOfDailyLivingLocomotion" type="xs:string" nillable="true"/>
<xs:element name="ScreenSystemResult" type="xs:string" nillable="true"/>
<xs:element name="ScreenAssessorResultDDID" type="xs:int"/>
<xs:element name="ScreenAssessorResult" type="xs:string" nillable="true"/>
<xs:element name="ExperiencingRecurrance" type="xs:int"/>
<xs:element name="ConditionsMakeUnstable" type="xs:int"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ArrayOfSystemOutcome">
<xs:sequence>
<xs:element name="SystemOutcomes" type="SystemOutcome" nillable="true" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SystemOutcome">
<xs:sequence>
<xs:element name="OutcomeDescription" type="xs:string" nillable="true"/>
<xs:element name="SystemResult" type="xs:string" nillable="true"/>
<xs:element name="UserDisagree" type="xs:boolean"/>
<xs:element name="UserResult" type="xs:string" nillable="true"/>
<xs:element name="UserExplanation" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
</xs:schema>

Thanks
Gary

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

XSD to SQL DDL

gary8877

IS-IT--Management

k5tm

Programmer

gary8877

IS-IT--Management

k5tm

Programmer

gary8877

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor