FUNCTION BulkXMLLoad lparameters tcAlias, ; tcTable, ; tcDatabase, ; tcServer, ; tcUserName, ; tcPassword local lnSelect, ; lcSchema, ; lcData, ; lcReturn, ; loException as Exception, ; lcXSD, ; loBulkLoad * Create the XML data and schema files. lnSelect = select() select (tcAlias) lcSchema = forceext(tcTable, 'xsd') lcData = forceext(tcTable, 'xml') TRY cursortoxml(alias(), lcData, 1, 512 + 8 + 2 , 0, lcSchema) &&Original lcReturn = '' catch to loException lcReturn = loException.Message endtry * Convert the XSD into a format acceptable by SQL Server. Add the SQL * namespace, convert the <xsd:choice> start and end tags to <xsd:sequence>, * use the sql:datatype attribute for DateTime fields, and specify the table * imported into with the sql:relation attribute. if empty(lcReturn) lcXSD = filetostr(lcSchema) lcXSD = strtran(lcXSD, ':xml-msdata">', ; ':xml-msdata" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">') lcXSD = strtran(lcXSD, 'IsDataSet="true">', ; 'IsDataSet="true" sql:is-constant="1">') lcXSD = strtran(lcXSD, '<xsd:choice maxOccurs="unbounded">', ; '<xsd:sequence>') lcXSD = strtran(lcXSD, '</xsd:choice>', ; '</xsd:sequence>') lcXSD = strtran(lcXSD, 'type="xsd:dateTime"', ; 'type="xsd:dateTime" sql:datatype="dateTime"') lcXSD = strtran(lcXSD, 'minOccurs="0"', ; 'sql:relation="' + lower(ALLTRIM(tcTable)) + '" minOccurs="0"') strtofile(lcXSD, lcSchema) * Instantiate the SQLXMLBulkLoad object, set its ConnectionString and other * properties, and call Execute to perform the bulk import. try loBulkLoad = createobject('SQLXMLBulkLoad.SQLXMLBulkload.4.0') lcConnString = 'Provider=SQLOLEDB.1;Initial Catalog=' + tcDatabase + ; ';Data Source=' + tcServer + ';Persist Security Info=False;' if empty(tcUserName) lcConnString = lcConnString + 'Integrated Security=SSPI' else lcConnString = lcConnString + 'User ID=' + tcUserName + ; ';Password=' + tcPassword endif empty(tcUserName) loBulkLoad.ConnectionString = lcConnString *** Can set the ErrorLogFile property to the name of a file to write import *** errors to loBulkLoad.KeepNulls = .T. loBulkLoad.IgnoreDuplicateKeys=.F. loBulkLoad.Execute(lcSchema, lcData) lcReturn = '' catch to loException lcReturn = loException.Message endtry * Clean up. erase (lcSchema) erase (lcData) endif empty(lcReturn) select (lnSelect) return lcReturn ENDFUNCTIA