|
|
|
date: Mon, 7 Jul 2008 05:16:08 -0700,
group: microsoft.public.sqlserver.xml
back
RE: Problems with xml bulkload
Additional info - the db table it goes into
CREATE TABLE DailyFileRecords(
FN varchar(40),
DATE datetime,
FTYPE int,
IIN varchar(10),
CRN varchar(10),
LUHN varchar(10),
LICENSEE_FORENAME varchar(21),
LICENSEE_SURNAME varchar(21),
Result int,
QueryType int,
ErrorCode varchar(21),
Org varchar(122),
POBox varchar(13),
Subprem varchar(30),
Buildname varchar(21),
Buildnum varchar(21),
Depthoro varchar(81),
Thoro varchar(81),
Deplocal varchar(35),
Local varchar(35),
Town varchar(21),
County varchar(21),
Postcode varchar(8),
Premises varchar(21),
Address varchar(21),
ContAddress varchar(21),
TownCity varchar(21),
CountyReg varchar(21),
PostcodeZip varchar(16),
Country varchar(21),
GENDER varchar(6),
DOB datetime
SENIOR_ID VARCHAR(25),
DISABLED_ID varchar(13),
EMAIL varchar(50),
ETHNIC_ORIGIN VARCHAR(20),
PREF_POST char(1),
PREF_EMAIL char(1),
PREF_SMS char(1),
MOBILE varchar(15),
LICENCE_CATEGORY varchar(36),
LICENCE_TYPE varchar(19),
START_DATE datetime,
START_TIME datetime,
CHANNEL_ID varchar(7),
SERIAL_NO varchar(12),
AMOUNT float,
MOPEX int,
SYSTEM_DATE datetime,
SYSTEM_TIME datetime,
Record int
)
"jojo123" wrote:
> Thanks for the reply - I run the script outside the dts and it runs - no
> errors but again no data goes into the table
>
> sample data (xml) is below:
> <?xml version="1.0" encoding="utf-8"?>
> <NL xmlns="EAF1NL.xsd">
> <HDR>
> <VER>1.1</VER>
> <FN>EAF1NL20080708.xml</FN>
> <DATE>20080708 14:07:59</DATE>
> <FTYPE>1</FTYPE>
> </HDR>
> <REC>
> <LICENCE_NO>
> <IIN>10000001</IIN>
> <CRN>100000001</CRN>
> <LUHN>1</LUHN>
> </LICENCE_NO>
> <LICENSEE_FORENAME>John</LICENSEE_FORENAME>
> <LICENSEE_SURNAME>Smith</LICENSEE_SURNAME>
> <LICENSEE_ADDRESS>
> <Result>1</Result>
> <QueryType>1</QueryType>
> <ErrorCode />
> <Org />
> <POBox />
> <Subprem />
> <Buildname>The Cottage</Buildname>
> <Buildnum />
> <Depthoro>Fox Lane</Depthoro>
> <Thoro />
> <Deplocal>Wicks</Deplocal>
> <Local />
> <Town>Bridgeford</Town>
> <County>Nottinghamshire</County>
> <Postcode>N14 7LY</Postcode>
> <Premises />
> <Address />
> <ContAddress />
> <TownCity />
> <CountyReg />
> <PostcodeZip />
> <Country />
> </LICENSEE_ADDRESS>
> <GENDER>Male</GENDER>
> <DOB>11/08/1983</DOB>
> <SENIOR_ID />
> <DISABLED_ID />
> <EMAIL />
> <ETHNIC_ORIGIN />
> <PREF_POST>Y</PREF_POST>
> <PREF_EMAIL />
> <PREF_SMS />
> <MOBILE />
> <LICENCE_CATEGORY>New Member</LICENCE_CATEGORY>
> <LICENCE_TYPE>Full Licence</LICENCE_TYPE>
> <START_DATE />
> <START_TIME />
> <CHANNEL_ID>1234567</CHANNEL_ID>
> <SERIAL_NO />
> <AMOUNT>199.00</AMOUNT>
> <MOPEX>1</MOPEX>
> <SYSTEM_DATE>01/05/2008</SYSTEM_DATE>
> <SYSTEM_TIME>11:28:30</SYSTEM_TIME>
> </REC>
> <TRL>
> <VER>1.1</VER>
> <TTXNS>1</TTXNS>
> </TRL>
> </NL>
> "Bob" wrote:
>
> > Hard to tell from that. Have you been able to get the code to run just as
> > VBScript, outside DTS? I'd start there.
> >
> > If it does run successfuly, you may need to look at what service account DTS
> > runs under and what permissions it has.
> >
> > A _small_ amount of sample data and target table DDL might help too ... if
> > you're really stuck.
> >
> > "jojo123" wrote:
> >
> > > Hi
> > >
> > > I have the vbscript below in my dts package and xml schema, where I specify
> > > which sql table field the data would go into - whilst I don't get any errors
> > > - there's no data going into the table - it's completely empty - any ideas?
> > > I'm trying to load the data into a single table
> > >
> > >
> > > VB Script :
> > >
> > > Function Main()
> > > Set objBulkLoad =
> > > CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> > > objBulkLoad.ConnectionString = "provider=SQLOLEDB;data
> > > source=localhost;database=myproj; integrated security=SSPI"
> > > objBulkLoad.ErrorLogFile = "c:\error.log"
> > > objBulkLoad.Execute "c:\EAF1NL.xsd", "c:\EAF1NL.xml"
> > > Set objBulkLoad = Nothing
> > > Main = DTSTaskExecResult_Success
> > > End Function
> > >
> > >
> > >
> > >
> > > schema
> > > ----------
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <xsd:schema id="EAF1NL" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > > xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> > > <xsd:element name="NL" type="NLF" sql:relation="DailyFileRecords" />
> > > <xsd:complexType name="NLF">
> > > <xsd:sequence>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="HDR" type="Header"
> > > sql:is-constant="1" />
> > > <xsd:element minOccurs="0" maxOccurs="unbounded" name="REC"
> > > type="Record" sql:is-constant="1" />
> > > <xsd:element minOccurs="1" maxOccurs="1" name="TRL" type="Trailer"
> > > sql:is-constant="1" />
> > > </xsd:sequence>
> > > </xsd:complexType>
> > > <xsd:complexType name="Header">
> > > <xsd:sequence>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="VER" sql:is-constant="1">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="3" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="FN" sql:field="FN"
> > > sql:datatype="varchar(40)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="32" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="DATE" type="xsd:date"
> > > sql:field="DATE" sql:datatype="datetime" />
> > > <xsd:element minOccurs="1" maxOccurs="1" name="FTYPE"
> > > type="xsd:string" sql:field="FTYPE" sql:datatype="int" />
> > > </xsd:sequence>
> > > </xsd:complexType>
> > > <xsd:complexType name="Record">
> > > <xsd:sequence>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENCE_NO"
> > > type="licence" sql:is-constant="1"/>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_FORENAME"
> > > sql:field="LICENSEE_FORENAME" sql:datatype="varchar(21)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="21" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_SURNAME"
> > > sql:field="LICENSEE_SURNAME" sql:datatype="varchar(21)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="21" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_ADDRESS"
> > > type="pafaddress" sql:is-constant="1" />
> > > <xsd:element minOccurs="1" maxOccurs="1" name="GENDER"
> > > sql:field="GENDER" sql:datatype="varchar(6)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="6" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="DOB" type="xsd:date"
> > > sql:field="DOB" sql:datatype="datetime"/>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="SENIOR_ID"
> > > sql:field="SENIOR_ID" sql:datatype="varchar(25)">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="25" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="DISABLED_ID"
> > > sql:field="DISABLED_ID" sql:datatype="varchar(13)">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="13" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="EMAIL"
> > > sql:field="EMAIL" sql:datatype="varchar(50)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="50" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="ETHNIC_ORIGIN"
> > > sql:field="ETHNIC_ORIGIN" sql:datatype="varchar(20)">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="20" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_POST"
> > > sql:field="PREF_POST" sql:datatype="char" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="1" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_EMAIL"
> > > sql:field="PREF_EMAIL" sql:datatype="char" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="1" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_SMS"
> > > sql:field="PREF_SMS" sql:datatype="char" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="1" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="MOBILE"
> > > sql:field="MOBILE" sql:datatype="varchar(15)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="15" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENCE_CATEGORY"
> > > sql:field="LICENCE_CATEGORY" sql:datatype="varchar(36)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="36" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENCE_TYPE"
> > > sql:field="LICENCE_TYPE" sql:datatype="varchar(19)" >
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="19" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="START_DATE"
> > > type="xsd:date" sql:field="START_DATE" sql:datatype="datetime" />
> > > <xsd:element minOccurs="0" maxOccurs="1" name="START_TIME"
> > > type="xsd:date" sql:field="START_TIME" sql:datatype="datetime"/>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="CHANNEL_ID"
> > > sql:field="CHANNEL_ID" sql:datatype="varchar(7)">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="7" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="0" maxOccurs="1" name="SERIAL_NO"
> > > sql:field="SERIAL_NO" sql:datatype="varchar(12)">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:maxLength value="12" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="AMOUNT"
> > > type="xsd:double" sql:field="AMOUNT" sql:datatype="float" />
> > > <xsd:element minOccurs="1" maxOccurs="1" name="MOPEX" type="xsd:byte"
> > > sql:field="MOPEX" sql:datatype="int" />
> > > <xsd:element minOccurs="1" maxOccurs="1" name="SYSTEM_DATE"
> > > type="xsd:date" sql:field="SYSTEM_DATE" sql:datatype="datetime" />
> > > <xsd:element minOccurs="1" maxOccurs="1" name="SYSTEM_TIME"
> > > type="xsd:date" sql:field="SYSTEM_TIME" sql:datatype="datetime" />
> > > </xsd:sequence>
> > > </xsd:complexType>
> > > <xsd:complexType name="licence">
> > > <xsd:sequence>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="IIN" sql:field="IIN"
> > > sql:datatype="varchar(10)">
> > > <xsd:simpleType>
> > > <xsd:restriction base="xsd:string">
> > > <xsd:minLength value="8" />
> > > <xsd:maxLength value="8" />
> > > </xsd:restriction>
> > > </xsd:simpleType>
> > > </xsd:element>
> > > <xsd:element minOccurs="1" maxOccurs="1" name="CRN" sql:field="CRN"
date: Mon, 7 Jul 2008 09:18:01 -0700
author: jojo123
RE: Problems with xml bulkload
OK,
finally got this to work by:
1) removing the xmlns attribute in the NL element. So I had:
<NL>
instead of
<NL xmlns="EAF1NL.xsd">
2) Changed the date format in the DATE element to:
<DATE>2008/07/08 14:07:59</DATE>
Man that was painful!
"jojo123" wrote:
> Additional info - the db table it goes into
>
> CREATE TABLE DailyFileRecords(
> FN varchar(40),
> DATE datetime,
> FTYPE int,
> IIN varchar(10),
> CRN varchar(10),
> LUHN varchar(10),
> LICENSEE_FORENAME varchar(21),
> LICENSEE_SURNAME varchar(21),
> Result int,
> QueryType int,
> ErrorCode varchar(21),
> Org varchar(122),
> POBox varchar(13),
> Subprem varchar(30),
> Buildname varchar(21),
> Buildnum varchar(21),
> Depthoro varchar(81),
> Thoro varchar(81),
> Deplocal varchar(35),
> Local varchar(35),
> Town varchar(21),
> County varchar(21),
> Postcode varchar(8),
> Premises varchar(21),
> Address varchar(21),
> ContAddress varchar(21),
> TownCity varchar(21),
> CountyReg varchar(21),
> PostcodeZip varchar(16),
> Country varchar(21),
> GENDER varchar(6),
> DOB datetime
> SENIOR_ID VARCHAR(25),
> DISABLED_ID varchar(13),
> EMAIL varchar(50),
> ETHNIC_ORIGIN VARCHAR(20),
> PREF_POST char(1),
> PREF_EMAIL char(1),
> PREF_SMS char(1),
> MOBILE varchar(15),
> LICENCE_CATEGORY varchar(36),
> LICENCE_TYPE varchar(19),
> START_DATE datetime,
> START_TIME datetime,
> CHANNEL_ID varchar(7),
> SERIAL_NO varchar(12),
> AMOUNT float,
> MOPEX int,
> SYSTEM_DATE datetime,
> SYSTEM_TIME datetime,
> Record int
> )
>
> "jojo123" wrote:
>
> > Thanks for the reply - I run the script outside the dts and it runs - no
> > errors but again no data goes into the table
> >
> > sample data (xml) is below:
> > <?xml version="1.0" encoding="utf-8"?>
> > <NL xmlns="EAF1NL.xsd">
> > <HDR>
> > <VER>1.1</VER>
> > <FN>EAF1NL20080708.xml</FN>
> > <DATE>20080708 14:07:59</DATE>
> > <FTYPE>1</FTYPE>
> > </HDR>
> > <REC>
> > <LICENCE_NO>
> > <IIN>10000001</IIN>
> > <CRN>100000001</CRN>
> > <LUHN>1</LUHN>
> > </LICENCE_NO>
> > <LICENSEE_FORENAME>John</LICENSEE_FORENAME>
> > <LICENSEE_SURNAME>Smith</LICENSEE_SURNAME>
> > <LICENSEE_ADDRESS>
> > <Result>1</Result>
> > <QueryType>1</QueryType>
> > <ErrorCode />
> > <Org />
> > <POBox />
> > <Subprem />
> > <Buildname>The Cottage</Buildname>
> > <Buildnum />
> > <Depthoro>Fox Lane</Depthoro>
> > <Thoro />
> > <Deplocal>Wicks</Deplocal>
> > <Local />
> > <Town>Bridgeford</Town>
> > <County>Nottinghamshire</County>
> > <Postcode>N14 7LY</Postcode>
> > <Premises />
> > <Address />
> > <ContAddress />
> > <TownCity />
> > <CountyReg />
> > <PostcodeZip />
> > <Country />
> > </LICENSEE_ADDRESS>
> > <GENDER>Male</GENDER>
> > <DOB>11/08/1983</DOB>
> > <SENIOR_ID />
> > <DISABLED_ID />
> > <EMAIL />
> > <ETHNIC_ORIGIN />
> > <PREF_POST>Y</PREF_POST>
> > <PREF_EMAIL />
> > <PREF_SMS />
> > <MOBILE />
> > <LICENCE_CATEGORY>New Member</LICENCE_CATEGORY>
> > <LICENCE_TYPE>Full Licence</LICENCE_TYPE>
> > <START_DATE />
> > <START_TIME />
> > <CHANNEL_ID>1234567</CHANNEL_ID>
> > <SERIAL_NO />
> > <AMOUNT>199.00</AMOUNT>
> > <MOPEX>1</MOPEX>
> > <SYSTEM_DATE>01/05/2008</SYSTEM_DATE>
> > <SYSTEM_TIME>11:28:30</SYSTEM_TIME>
> > </REC>
> > <TRL>
> > <VER>1.1</VER>
> > <TTXNS>1</TTXNS>
> > </TRL>
> > </NL>
> > "Bob" wrote:
> >
> > > Hard to tell from that. Have you been able to get the code to run just as
> > > VBScript, outside DTS? I'd start there.
> > >
> > > If it does run successfuly, you may need to look at what service account DTS
> > > runs under and what permissions it has.
> > >
> > > A _small_ amount of sample data and target table DDL might help too ... if
> > > you're really stuck.
> > >
> > > "jojo123" wrote:
> > >
> > > > Hi
> > > >
> > > > I have the vbscript below in my dts package and xml schema, where I specify
> > > > which sql table field the data would go into - whilst I don't get any errors
> > > > - there's no data going into the table - it's completely empty - any ideas?
> > > > I'm trying to load the data into a single table
> > > >
> > > >
> > > > VB Script :
> > > >
> > > > Function Main()
> > > > Set objBulkLoad =
> > > > CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> > > > objBulkLoad.ConnectionString = "provider=SQLOLEDB;data
> > > > source=localhost;database=myproj; integrated security=SSPI"
> > > > objBulkLoad.ErrorLogFile = "c:\error.log"
> > > > objBulkLoad.Execute "c:\EAF1NL.xsd", "c:\EAF1NL.xml"
> > > > Set objBulkLoad = Nothing
> > > > Main = DTSTaskExecResult_Success
> > > > End Function
> > > >
> > > >
> > > >
> > > >
> > > > schema
> > > > ----------
> > > > <?xml version="1.0" encoding="utf-8"?>
> > > > <xsd:schema id="EAF1NL" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > > > xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> > > > <xsd:element name="NL" type="NLF" sql:relation="DailyFileRecords" />
> > > > <xsd:complexType name="NLF">
> > > > <xsd:sequence>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="HDR" type="Header"
> > > > sql:is-constant="1" />
> > > > <xsd:element minOccurs="0" maxOccurs="unbounded" name="REC"
> > > > type="Record" sql:is-constant="1" />
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="TRL" type="Trailer"
> > > > sql:is-constant="1" />
> > > > </xsd:sequence>
> > > > </xsd:complexType>
> > > > <xsd:complexType name="Header">
> > > > <xsd:sequence>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="VER" sql:is-constant="1">
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="3" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="FN" sql:field="FN"
> > > > sql:datatype="varchar(40)" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="32" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="DATE" type="xsd:date"
> > > > sql:field="DATE" sql:datatype="datetime" />
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="FTYPE"
> > > > type="xsd:string" sql:field="FTYPE" sql:datatype="int" />
> > > > </xsd:sequence>
> > > > </xsd:complexType>
> > > > <xsd:complexType name="Record">
> > > > <xsd:sequence>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENCE_NO"
> > > > type="licence" sql:is-constant="1"/>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_FORENAME"
> > > > sql:field="LICENSEE_FORENAME" sql:datatype="varchar(21)" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="21" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_SURNAME"
> > > > sql:field="LICENSEE_SURNAME" sql:datatype="varchar(21)" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="21" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_ADDRESS"
> > > > type="pafaddress" sql:is-constant="1" />
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="GENDER"
> > > > sql:field="GENDER" sql:datatype="varchar(6)" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="6" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="1" maxOccurs="1" name="DOB" type="xsd:date"
> > > > sql:field="DOB" sql:datatype="datetime"/>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="SENIOR_ID"
> > > > sql:field="SENIOR_ID" sql:datatype="varchar(25)">
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="25" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="DISABLED_ID"
> > > > sql:field="DISABLED_ID" sql:datatype="varchar(13)">
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="13" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="EMAIL"
> > > > sql:field="EMAIL" sql:datatype="varchar(50)" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="50" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="ETHNIC_ORIGIN"
> > > > sql:field="ETHNIC_ORIGIN" sql:datatype="varchar(20)">
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="20" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_POST"
> > > > sql:field="PREF_POST" sql:datatype="char" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="1" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_EMAIL"
> > > > sql:field="PREF_EMAIL" sql:datatype="char" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="1" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_SMS"
> > > > sql:field="PREF_SMS" sql:datatype="char" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="1" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
> > > > <xsd:element minOccurs="0" maxOccurs="1" name="MOBILE"
> > > > sql:field="MOBILE" sql:datatype="varchar(15)" >
> > > > <xsd:simpleType>
> > > > <xsd:restriction base="xsd:string">
> > > > <xsd:maxLength value="15" />
> > > > </xsd:restriction>
> > > > </xsd:simpleType>
> > > > </xsd:element>
date: Mon, 7 Jul 2008 19:21:01 -0700
author: Bob
RE: Problems with xml bulkload
Thanks for that -
I've made the changes below - but I get an error data mapping to column
'IIN' was already found in the data. Map sure no two schema definitions map
to the same column? Abit baffled
Thanks
"Bob" wrote:
> OK,
>
> finally got this to work by:
> 1) removing the xmlns attribute in the NL element. So I had:
>
> <NL>
>
> instead of
>
> <NL xmlns="EAF1NL.xsd">
>
> 2) Changed the date format in the DATE element to:
>
> <DATE>2008/07/08 14:07:59</DATE>
>
>
> Man that was painful!
>
>
> "jojo123" wrote:
>
> > Additional info - the db table it goes into
> >
> > CREATE TABLE DailyFileRecords(
> > FN varchar(40),
> > DATE datetime,
> > FTYPE int,
> > IIN varchar(10),
> > CRN varchar(10),
> > LUHN varchar(10),
> > LICENSEE_FORENAME varchar(21),
> > LICENSEE_SURNAME varchar(21),
> > Result int,
> > QueryType int,
> > ErrorCode varchar(21),
> > Org varchar(122),
> > POBox varchar(13),
> > Subprem varchar(30),
> > Buildname varchar(21),
> > Buildnum varchar(21),
> > Depthoro varchar(81),
> > Thoro varchar(81),
> > Deplocal varchar(35),
> > Local varchar(35),
> > Town varchar(21),
> > County varchar(21),
> > Postcode varchar(8),
> > Premises varchar(21),
> > Address varchar(21),
> > ContAddress varchar(21),
> > TownCity varchar(21),
> > CountyReg varchar(21),
> > PostcodeZip varchar(16),
> > Country varchar(21),
> > GENDER varchar(6),
> > DOB datetime
> > SENIOR_ID VARCHAR(25),
> > DISABLED_ID varchar(13),
> > EMAIL varchar(50),
> > ETHNIC_ORIGIN VARCHAR(20),
> > PREF_POST char(1),
> > PREF_EMAIL char(1),
> > PREF_SMS char(1),
> > MOBILE varchar(15),
> > LICENCE_CATEGORY varchar(36),
> > LICENCE_TYPE varchar(19),
> > START_DATE datetime,
> > START_TIME datetime,
> > CHANNEL_ID varchar(7),
> > SERIAL_NO varchar(12),
> > AMOUNT float,
> > MOPEX int,
> > SYSTEM_DATE datetime,
> > SYSTEM_TIME datetime,
> > Record int
> > )
> >
> > "jojo123" wrote:
> >
> > > Thanks for the reply - I run the script outside the dts and it runs - no
> > > errors but again no data goes into the table
> > >
> > > sample data (xml) is below:
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <NL xmlns="EAF1NL.xsd">
> > > <HDR>
> > > <VER>1.1</VER>
> > > <FN>EAF1NL20080708.xml</FN>
> > > <DATE>20080708 14:07:59</DATE>
> > > <FTYPE>1</FTYPE>
> > > </HDR>
> > > <REC>
> > > <LICENCE_NO>
> > > <IIN>10000001</IIN>
> > > <CRN>100000001</CRN>
> > > <LUHN>1</LUHN>
> > > </LICENCE_NO>
> > > <LICENSEE_FORENAME>John</LICENSEE_FORENAME>
> > > <LICENSEE_SURNAME>Smith</LICENSEE_SURNAME>
> > > <LICENSEE_ADDRESS>
> > > <Result>1</Result>
> > > <QueryType>1</QueryType>
> > > <ErrorCode />
> > > <Org />
> > > <POBox />
> > > <Subprem />
> > > <Buildname>The Cottage</Buildname>
> > > <Buildnum />
> > > <Depthoro>Fox Lane</Depthoro>
> > > <Thoro />
> > > <Deplocal>Wicks</Deplocal>
> > > <Local />
> > > <Town>Bridgeford</Town>
> > > <County>Nottinghamshire</County>
> > > <Postcode>N14 7LY</Postcode>
> > > <Premises />
> > > <Address />
> > > <ContAddress />
> > > <TownCity />
> > > <CountyReg />
> > > <PostcodeZip />
> > > <Country />
> > > </LICENSEE_ADDRESS>
> > > <GENDER>Male</GENDER>
> > > <DOB>11/08/1983</DOB>
> > > <SENIOR_ID />
> > > <DISABLED_ID />
> > > <EMAIL />
> > > <ETHNIC_ORIGIN />
> > > <PREF_POST>Y</PREF_POST>
> > > <PREF_EMAIL />
> > > <PREF_SMS />
> > > <MOBILE />
> > > <LICENCE_CATEGORY>New Member</LICENCE_CATEGORY>
> > > <LICENCE_TYPE>Full Licence</LICENCE_TYPE>
> > > <START_DATE />
> > > <START_TIME />
> > > <CHANNEL_ID>1234567</CHANNEL_ID>
> > > <SERIAL_NO />
> > > <AMOUNT>199.00</AMOUNT>
> > > <MOPEX>1</MOPEX>
> > > <SYSTEM_DATE>01/05/2008</SYSTEM_DATE>
> > > <SYSTEM_TIME>11:28:30</SYSTEM_TIME>
> > > </REC>
> > > <TRL>
> > > <VER>1.1</VER>
> > > <TTXNS>1</TTXNS>
> > > </TRL>
> > > </NL>
> > > "Bob" wrote:
> > >
> > > > Hard to tell from that. Have you been able to get the code to run just as
> > > > VBScript, outside DTS? I'd start there.
> > > >
> > > > If it does run successfuly, you may need to look at what service account DTS
> > > > runs under and what permissions it has.
> > > >
> > > > A _small_ amount of sample data and target table DDL might help too ... if
> > > > you're really stuck.
> > > >
> > > > "jojo123" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I have the vbscript below in my dts package and xml schema, where I specify
> > > > > which sql table field the data would go into - whilst I don't get any errors
> > > > > - there's no data going into the table - it's completely empty - any ideas?
> > > > > I'm trying to load the data into a single table
> > > > >
> > > > >
> > > > > VB Script :
> > > > >
> > > > > Function Main()
> > > > > Set objBulkLoad =
> > > > > CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> > > > > objBulkLoad.ConnectionString = "provider=SQLOLEDB;data
> > > > > source=localhost;database=myproj; integrated security=SSPI"
> > > > > objBulkLoad.ErrorLogFile = "c:\error.log"
> > > > > objBulkLoad.Execute "c:\EAF1NL.xsd", "c:\EAF1NL.xml"
> > > > > Set objBulkLoad = Nothing
> > > > > Main = DTSTaskExecResult_Success
> > > > > End Function
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > schema
> > > > > ----------
> > > > > <?xml version="1.0" encoding="utf-8"?>
> > > > > <xsd:schema id="EAF1NL" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > > > > xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> > > > > <xsd:element name="NL" type="NLF" sql:relation="DailyFileRecords" />
> > > > > <xsd:complexType name="NLF">
> > > > > <xsd:sequence>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="HDR" type="Header"
> > > > > sql:is-constant="1" />
> > > > > <xsd:element minOccurs="0" maxOccurs="unbounded" name="REC"
> > > > > type="Record" sql:is-constant="1" />
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="TRL" type="Trailer"
> > > > > sql:is-constant="1" />
> > > > > </xsd:sequence>
> > > > > </xsd:complexType>
> > > > > <xsd:complexType name="Header">
> > > > > <xsd:sequence>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="VER" sql:is-constant="1">
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="3" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="FN" sql:field="FN"
> > > > > sql:datatype="varchar(40)" >
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="32" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="DATE" type="xsd:date"
> > > > > sql:field="DATE" sql:datatype="datetime" />
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="FTYPE"
> > > > > type="xsd:string" sql:field="FTYPE" sql:datatype="int" />
> > > > > </xsd:sequence>
> > > > > </xsd:complexType>
> > > > > <xsd:complexType name="Record">
> > > > > <xsd:sequence>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENCE_NO"
> > > > > type="licence" sql:is-constant="1"/>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_FORENAME"
> > > > > sql:field="LICENSEE_FORENAME" sql:datatype="varchar(21)" >
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="21" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_SURNAME"
> > > > > sql:field="LICENSEE_SURNAME" sql:datatype="varchar(21)" >
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="21" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="LICENSEE_ADDRESS"
> > > > > type="pafaddress" sql:is-constant="1" />
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="GENDER"
> > > > > sql:field="GENDER" sql:datatype="varchar(6)" >
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="6" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="1" maxOccurs="1" name="DOB" type="xsd:date"
> > > > > sql:field="DOB" sql:datatype="datetime"/>
> > > > > <xsd:element minOccurs="0" maxOccurs="1" name="SENIOR_ID"
> > > > > sql:field="SENIOR_ID" sql:datatype="varchar(25)">
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="25" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="0" maxOccurs="1" name="DISABLED_ID"
> > > > > sql:field="DISABLED_ID" sql:datatype="varchar(13)">
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="13" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="0" maxOccurs="1" name="EMAIL"
> > > > > sql:field="EMAIL" sql:datatype="varchar(50)" >
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="50" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="0" maxOccurs="1" name="ETHNIC_ORIGIN"
> > > > > sql:field="ETHNIC_ORIGIN" sql:datatype="varchar(20)">
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="20" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_POST"
> > > > > sql:field="PREF_POST" sql:datatype="char" >
> > > > > <xsd:simpleType>
> > > > > <xsd:restriction base="xsd:string">
> > > > > <xsd:maxLength value="1" />
> > > > > </xsd:restriction>
> > > > > </xsd:simpleType>
> > > > > </xsd:element>
> > > > > <xsd:element minOccurs="0" maxOccurs="1" name="PREF_EMAIL"
> > > > > sql:field="PREF_EMAIL" sql:datatype="char" >
> > > > > <xsd:simpleType>
date: Tue, 8 Jul 2008 03:46:07 -0700
author: jojo123
|
