I have a XML File below,

<?xml version="1.0" encoding="utf-8"?>
<Employees xmlns="http://tempuri.org/MySchema.xsd">
   <Employee ID="1">
      <FirstName>Klaus</FirstName>
      <LastName>Salchner</LastName>
      <PhoneNumber>410-727-5112</PhoneNumber>
      <EmailAddress>[email protected]</EmailAddress>
   </Employee>
   <Employee ID="2">
      <FirstName>Peter</FirstName>
      <LastName>Pan</LastName>
      <PhoneNumber>604-111-1111</PhoneNumber>
   </Employee>
</Employees>

I have created a table as below in sql server,
CREATE TABLE Employee (
   ID nvarchar(10),
   FirstName NVARCHAR(50),
   LastName NVARCHAR(50),
PhoneNumber NVARCHAR(50),
EmailAddress NVARCHAR(100))


I am trying to import the xml data into the sql table using the schema,the schema is as below

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://startrack:xml-data"
                                   xmlns:sql="http://startrack:mapping-schema">

   <xs:element name="Employees" sql:is-constant="1">
      <xs:complexType>
      <xs:choice minOccurs="1" maxOccurs="*">
         <xs:element name="Employee" type="Employee" sql:relation="Employee"/>
      </xs:choice>
      </xs:complexType>
   </xs:element>
      <xs:complexType name="Employee" >
         <xs:sequence>
            <xs:element name="FirstName" type="xs:string"
                        minOccurs="1" maxOccurs="1" sql:field="FirstName"/>
            <xs:element name="LastName" type="xs:string"
                        minOccurs="1" maxOccurs="1" sql:field="LastName"/>
            <xs:element name="PhoneNumber" type="xs:string"
                        minOccurs="1" maxOccurs="1" sql:field="PhoneNumber" />
            <xs:element name="EmailAddress" type="xs:string"
                        minOccurs="1" maxOccurs="1" sql:field="EmailAddress" />
         </xs:sequence>
         <xs:attribute name="ID" type="xs:string" />
      </xs:complexType>
</xs:schema>

I import by running the below script in the DTS, The PROBLEM IS THAT the DTS runs without any error but the data is not imported to the table.

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
    objBL.ConnectionString = "provider=SQLOLEDB.1;data source=Myserver;database=MyDB;uid=MYUSER;pwd=MYPWD "
    objBL.ErrorLogFile = "z:\error.log"
    objBL.Execute "z:\employee_mapping.xml", "z:\employee.xml"
    Set objBL = Nothing

Is there any syntax that i missed in the schema?

Any help appreciated. I am a Newbie to XML and Schema's.

Thanks.

Your schema isn't a valid schema because your namespaces are not correctly defined:

The following are the correct namespace declarations:

xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"

After you change those to the correct names you might get other errors but hopefully you can work them out on your own.

/- Sam Judson : Wrox Technical Editor -/