SQLXML Load Error

ckmalice on Wed, 05 Dec 2018 08:36:35

Dear All,

I am new on using SQLXML Bulk Load object to import XML to SQL server for further checking, for the test i would like to current several tables from XML

• ListRecords - The list of people
• ListRecordsDate - Date information for people in listRecords, which i would like to store both DateType and Date Information into this table

Currently I have the following XML with XSD definition

<?xml version="1.0" encoding="utf-8"?>
	<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
	<xs:annotation>
		<xs:appinfo>  
			<sql:relationship name="ListRecordsDescriptions"
								parent="ListRecords"
								parent-key="RecordID"
								child="ListDescriptions"
								child-key="RecordID" />  
		</xs:appinfo>
		<xs:appinfo>  
				<sql:relationship name="ListRecordsDates"
									parent="ListRecords"
									parent-key="RecordID"
									child="ListDates"
									child-key="RecordID" />  
		</xs:appinfo>
		<xs:appinfo>  
				<sql:relationship name="ListDatesDateDetails"
									parent="ListDates"
									parent-key="DateID"
									child="ListDateDetails"
									child-key="DateID" />  
		</xs:appinfo>
	</xs:annotation>
		<xs:element name="List" sql:is-constant="1">
		<xs:complexType>
			<xs:sequence>
				<xs:sequence>

					<xs:element ref="Records" />

				</xs:sequence>
			</xs:sequence>

		</xs:complexType>
	</xs:element>
	<xs:element name="Records" sql:is-constant="1">
		<xs:complexType>
			<xs:sequence>
				<xs:sequence>
					<xs:element minOccurs="0" maxOccurs="unbounded" ref="Person" />
				</xs:sequence>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	<xs:element name="Person" sql:relation="ListRecords">
		<xs:complexType>
			<xs:sequence>
				<xs:element minOccurs="0" maxOccurs="1" ref="Gender" />
				<xs:element minOccurs="0" maxOccurs="1" ref="ActiveStatus" />
				<xs:element minOccurs="0" maxOccurs="1" ref="Deceased" />
				<xs:element minOccurs="0" maxOccurs="1" ref="Descriptions" />
				<xs:element minOccurs="0" maxOccurs="1" ref="DateDetails" />
			</xs:sequence>
			<xs:attribute name="id" type="xs:string" use="required" sql:field="RecordID" />
			<xs:attribute name="action" use="required" sql:field="action" sql:datatype="nvarchar(10)">
				<xs:simpleType>
					<xs:restriction base="xs:NMTOKEN">
						<xs:enumeration value="del" />
						<xs:enumeration value="chg" />
						<xs:enumeration value="add" />
					</xs:restriction>
				</xs:simpleType>
			</xs:attribute>
			<xs:attribute name="date" type="xs:string" use="required" sql:field="date" sql:datatype="nvarchar(MAX)"/>
		</xs:complexType>
	</xs:element>
	<xs:element name="Gender" type="xs:string" sql:field="Gender" sql:datatype="nvarchar(MAX)" />
	<xs:element name="ActiveStatus" type="xs:string" sql:field="ActiveStatus" sql:datatype="nvarchar(MAX)" />
	<xs:element name="Deceased" type="xs:string" sql:field="Deceased" sql:datatype="nvarchar(MAX)" />
	<xs:element name="Descriptions" sql:is-constant="1">
		<xs:complexType>
			<xs:sequence>
				<xs:element minOccurs="1" maxOccurs="unbounded" ref="Description" />
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	<xs:element name="Description" sql:relation="ListDescriptions" sql:relationship="ListRecordsDescriptions">
		<xs:complexType>
			<xs:attribute name="Description1" type="xs:string" use="required" sql:field="Description1" sql:datatype="nvarchar(MAX)" />
			<xs:attribute name="Description2" type="xs:string" sql:field="Description2" sql:datatype="nvarchar(MAX)" />
			<xs:attribute name="Description3" type="xs:string" sql:field="Description3" sql:datatype="nvarchar(MAX)" />
		</xs:complexType>
	</xs:element>
	<xs:element name="DateDetails" sql:is-constant="1">
		<xs:complexType>
			<xs:sequence>
				<xs:element minOccurs="1" maxOccurs="unbounded" ref="Date" />
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	<xs:element name="Date" sql:relation="ListDates" sql:relationship="ListRecordsDates">
		<xs:complexType>
			<xs:sequence>
				<xs:element minOccurs="1" maxOccurs="unbounded" ref="DateValue" />
			</xs:sequence>
			<xs:attribute name="DateType" type="xs:string" use="required" sql:field="DateType" sql:datatype="nvarchar(MAX)" />
		</xs:complexType>
	</xs:element>
	<xs:element name="DateValue" sql:relation="ListDateDetails" sql:relationship="ListRecordsDates ListDatesDateDetails">
		<xs:complexType>
			<xs:simpleContent>
				<xs:extension base="xs:string">
					<xs:attribute name="Day" type="xs:string" sql:field="Day" sql:datatype="nvarchar(MAX)" />
					<xs:attribute name="Month" type="xs:string" sql:field="Month" sql:datatype="nvarchar(MAX)" />
					<xs:attribute name="Year" type="xs:string" sql:field="Year" sql:datatype="nvarchar(MAX)" />
					<xs:attribute name="Dnotes" type="xs:string" sql:field="Dnotes" sql:datatype="nvarchar(MAX)" />
				</xs:extension>
			</xs:simpleContent>
		</xs:complexType>
	</xs:element>
</xs:schema>

The following is the corresponding XML to be tested (as it is just a part of them)

<?xml version="1.0" encoding="utf-8"?><List><Records><Person id="1" action="chg" date="24-Nov-2017"><Gender>Female</Gender><ActiveStatus>Active</ActiveStatus><Deceased>No</Deceased><Descriptions><Description Description1="3" Description2="1" /><Description Description1="3" Description2="2" /></Descriptions><DateDetails><Date DateType="Date of Birth"><DateValue Year="1967" /><DateValue Day="22" Month="Sep" Year="1980" /></Date></DateDetails></Person></Records></List>

However I have encountered the following error