Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cheechwan
Starting Member
11 Posts |
 Posted - 2006-11-22 : 02:25:25
|
| Dear Experts,Im trying to load an XML raw file data into my SQL server table using VB script as refer to below, but i have encounter a problem that some of the data could not be fully load into the table. Below here are the VB script, table structure, xml schema and xml source file . Could u please vefify what are the issue.XML source file======================<?xml version="1.0" ?><cgl> <customer> <ctc>AY1</ctc> <cid>81930848</cid> <cti>MDM</cti> <cnm>[TAN] YIN HIM</cnm> <rgn>603</rgn> <dob>1956-11-25</dob> <gen>F</gen> <ipd> <ipt>I</ipt> <ipn>561125-05-5014A</ipn> <ipc>MALAYSIA</ipc> </ipd> <exp>2008-11-30</exp> <pf1></pf1> <pf2>012</pf2> <pf3>E</pf3> </customer></cgl>table structure================CREATE TABLE [dbo].[TMP_CGL_PROFILE] ( [CARD_TYPE] [varchar] (50) COLLATE Latin1_General_BIN NULL , [CARD_NUM] [varchar] (50) COLLATE Latin1_General_BIN NULL , [TITLE] [varchar] (10) COLLATE Latin1_General_BIN NULL , [C_NAME] [varchar] (50) COLLATE Latin1_General_BIN NULL , [REGIONAL_CODE] [varchar] (50) COLLATE Latin1_General_BIN NULL , [DOB] [varchar] (50) COLLATE Latin1_General_BIN NULL , [SEX] [varchar] (3) COLLATE Latin1_General_BIN NULL , [IC_TYPE] [varchar] (50) COLLATE Latin1_General_BIN NULL , [IC_NUM] [varchar] (50) COLLATE Latin1_General_BIN NULL , [NATIONALITY] [varchar] (50) COLLATE Latin1_General_BIN NULL , [EXPIRED_DATE] [varchar] (50) COLLATE Latin1_General_BIN NULL , [EMAIL_STATUS] [varchar] (50) COLLATE Latin1_General_BIN NULL , [HANDPHONE] [varchar] (50) COLLATE Latin1_General_BIN NULL , [LANG] [varchar] (50) COLLATE Latin1_General_BIN NULL ) ON [PRIMARY]GOXML Schema===========<?xml version="1.0" ?><Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:xml:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql" > <ElementType name="ctc" dt:type="string" /> <ElementType name="cid" dt:type="string" /> <ElementType name="cti" dt:type="string" /> <ElementType name="cnm" dt:type="string" /> <ElementType name="rgn" dt:type="string" /> <ElementType name="dob" dt:type="string" /> <ElementType name="gen" dt:type="string" /> <ElementType name="ipt" dt:type="string" /> <ElementType name="ipn" dt:type="string" /> <ElementType name="ipc" dt:type="string" /> <ElementType name="exp" dt:type="string" /> <ElementType name="pf1" dt:type="string" /> <ElementType name="pf2" dt:type="string" /> <ElementType name="pf3" dt:type="string" /> <ElementType name="cgl" sql:is-constant="1"> <element type="customer" /> </ElementType> <ElementType name="customer" sql:relation="TMP_CGL_PROFILE"> <element type="ctc" sql:field="CARD_TYPE" /> <element type="cid" sql:field="CARD_NUM" /> <element type="cti" sql:field="TITLE" /> <element type="cnm" sql:field="C_NAME" /> <element type="rgn" sql:field="REGIONAL_CODE" /> <element type="dob" sql:field="DOB" /> <element type="gen" sql:field="SEX" /> <element type="exp" sql:field="EXPIRED_DATE" /> <element type="pf1" sql:field="EMAIL_STATUS" /> <element type="pf2" sql:field="HANDPHONE" /> <element type="pf3" sql:field="LANG" /> </ElementType> </Schema>VB Script========Dim objBL As Object LoadXML = False On Error GoTo handler_err Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") 'objBL.ConnectionString = "provider=SQLOLEDB.1;data source=188.9.10.243;database=HKWCReporting;uid=sa;pwd=changesa" objBL.ConnectionString = "provider=SQLOLEDB.1;data source=10.1.1.43;database=HKWCReporting;uid=CRMsa;pwd=changesa" objBL.ErrorLogFile = App.Path & "\error.log" 'objBL.Execute "c:\CustomerMapping.txt", "c:\Customers.txt" objBL.Execute App.Path & "\CGLProfile.xml", App.Path & "\CGL_DATA.txt" Set objBL = Nothing LoadXML = True Exit Functionhandler_err: LoadXML = False Set oDB = Nothing MsgBox Err.Description |
|
|
|
|
|
|
|
Load Bulk XML data into SQL table.