Question
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
Replies
ckmalice on Wed, 05 Dec 2018 08:53:12
And one more question (sorry for separating)
If there is one more tag
<Information>
<Name>XXX</Name>
<Address>1 ABC Building</Address>
<Address>DEF Street</Address>
<Phone>2345679</Phone>
<Phone>4455113</Phone>
</Information>
May I know how i can make the XSD to let SQL import as 2 lines
The XML is generated from other parties so it is impossible to change any elements and attributes from the input file