>- see footer for list info -<
Morning All,
The xml file I have produced within CF is in UTF-8 format, see example
below, I have tried converting ToString and using 'windows-1251' encoding by
doing a replace call with no luck, any further help would be appreciated,
looking through the various feeds on the web it would indicate that nvarchar
fields etc with UTF-8 is a weird combination so tried a number of different
things as I said with no luck
Anyway, I have included most of the relevant parts below:
<?xml version="1.0" encoding="UTF-8"?>
<query columns="3" rows="4">
<row>
<number>0121 242 1111</number>
<numbertype>Telephone - Home</numbertype>
<patid>12825</patid>
</row>
<row>
<number>0121 743 1111</number>
<numbertype>Telephone - Home</numbertype>
<patid>12826</patid>
</row>
<row>
<number>0121 603 1111</number>
<numbertype>Telephone - Home</numbertype>
<patid>12827</patid>
</row>
<row>
<number>07984 004 111</number>
<numbertype>Telephone - Home</numbertype>
<patid>12828</patid>
</row>
</query>
I am passing the info to the stored proc as follows:
<cfstoredproc procedure="spr_APPM8_INSERT_TEMP_CONTACT_TESTING"
datasource="###" username="#####" password="######" returncode="Yes">
<cfprocparam type="In" value="#xmlFile#" cfsqltype="CF_SQL_VARCHAR"
variable="XMLDOC">
<cfprocresult name="res_APPM8_INSERT_TEMP_CONTACT_TESTING">
</cfstoredproc>
My Stored Procedure is as follows:
CREATE PROCEDURE spr_APPM8_INSERT_TEMP_CONTACT_TESTING
@XMLDOC ntext
AS
declare @xml_hndl int
--prepare the XML Document by executing a system stored procedure
exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC
--insert into table
Insert Into APPM8_TempContactTesting
(PatID, NumberType, MSISDN)
Select
patid, numbertype,number
From
OPENXML(@xml_hndl, '/query/row', 1)
With
(
patid int '@patid',
numbertype nvarchar(50) '@numbertype',
number nvarchar(50) '@number'
)
-- Clear the XML document from memory
exec sp_xml_removedocument @xml_hndl
GO
Format of the table is as follows:
ContactID int 4 0
PatID nvarchar 50 1
NumberType nvarchar 50 1
MSISDN nvarchar 50 1
Lee
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam
Sent: 09 September 2006 18:53
To: 'Coldfusion Development'
Subject: RE: [CF-Dev] Import of 10k records from a tab delimited txt
>- see footer for list info -<
Cool,
With snakes kind assistance, I now have the uploaded file into a suitable
XML format so just need to pass that into the SP (apparently as a text
format) and then perform the logic of updating and inserting in the SP.
How's your conditional logic in SP everyone?
Lee
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Simon Baynes
Sent: 09 September 2006 18:50
To: Coldfusion Development
Subject: Re: [CF-Dev] Import of 10k records from a tab delimited txt
>- see footer for list info -<
Well it that case I would still suggest that you just get the records in a
temporary table with the minimum of fuss and then do the recrod checking and
inseertin within the database. This way you put less strain on CF and in the
DB where it belongs.
On 9/9/06, Lee Fortnam <[EMAIL PROTECTED]> wrote:
> >- see footer for list info -<
> Sadly not. Have been discussing it with Snake at the moment, looking
> at converting the Tab File into XML, passing to a SP and looping
> through it that way.
>
> Lee
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Simon Baynes
> Sent: 09 September 2006 18:43
> To: Coldfusion Development
> Subject: Re: [CF-Dev] Import of 10k records from a tab delimited txt
>
> >- see footer for list info -<
> Can you not move the file to the DB server? If you can you can use a
> DTS package to import the file into a temporary table, then you can
> run a stored proceedure to do all the stuff you are doing in CF? This
> is preferrable as SQL 2000 is built for operations like this CF is not.
>
> On 9/9/06, Snake <[EMAIL PROTECTED]> wrote:
> > >- see footer for list info -<
> > Is there any rule to new/updated records.
> >
> > I.E.
> >
> > Do they upload a new file (with a different filename) when there are
> > new records, or do they just re-upload the same file each time, and
> > you have to see if it contains updates, new records or both?
> > If new records are always in a new file, then you can keep track of
> > previous filenames so this will tell you whether you need to do an
> > INSERT or an UPDATE.
> >
> > You will have some advantage with using an SP for the update/add
> > record queries. But Either way your going to have to do the looping
> > ove rthe file with CF as the file will be on the web server so the
> > DB server will not be able to connect to it with a SP directly.
> >
> > Russ
> >
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam
> > Sent: 09 September 2006 16:36
> > To: 'Coldfusion Development'
> > Subject: [CF-Dev] Import of 10k records from a tab delimited txt
> > Importance: High
> >
> > >- see footer for list info -<
> > Dear All,
> >
> > I have a system which needs to take a number of records (upwards of
> > 10k to
> > 12k) of peoples contact numbers from a surgery DB. Each patient may
> > have a mobile and landline so the format of the file is as follows:
> >
> > Pat ID NHS Number Home Mobile Email
> > ###### ###### ####### 0 0
> >
> > OR
> >
> > Pat ID Number Type Number
> > ##### Mobile Phone ###########
> > ##### Telephone - Home ###########
> >
> > The numbers have been removed above to protect the innocent!!!
> >
> > There are a couple of different text formats as not all surgeries
> > use the same patient management system and they are all a pain in
> > the bum to get info out of.
> >
> > The issue is, the surgery can upload this file at any time using a
> > form on a secure website. This is being done using the Flash Forms
> > cf_flashUpload from ASFusion. Getting the file up to the site is not
> > a problem. The problem is when I need to import these records into
> > the DB. This file might be an initial insert in which case I need to
> > push all records into the DB, equally the file might be an updated
> > file from a previous upload in which case only the numbers that have
> > changed
> need to be updated.
> >
> > Question is, what is the most efficient way to do this. I am using
> > CFMX7 and MS SQL 2000. My Stored Proc knowledge is not brilliant but
> > would welcome any guidance you can provide.
> >
> > At the moment I simply step through the file record by record and
> > check if the patient info is already in the DB, if it is and the
> > data is different, update the record, if not in the DB, insert it.
> > As my knowledge of Store proc is not good the logic for this is
> > being handled by CF and not in a single Stored proc as might be more
efficient.
> >
> > Ideally, I need the system to receive the file, run the import and
> > then return the status of the import to the user, i.e. how many
> > records where added, updated etc, as this may take a few seconds I
> > would like to have a screen which polls the system to see if the
> > import has completed but this is not a must, just thinking aloud.
> >
> > Look forward to your input.
> >
> > With Regards,
> >
> > Lee
> >
> > _______________________________________________
> >
> > For details on ALL mailing lists and for joining or leaving lists,
> > go to http://list.cfdeveloper.co.uk/mailman/listinfo
> >
> > --
> > CFDeveloper Sponsors:-
> > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> > >- Lists hosted by www.Gradwell.com -<
> > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
> > >help -<
> >
> >
> > _______________________________________________
> >
> > For details on ALL mailing lists and for joining or leaving lists,
> > go to http://list.cfdeveloper.co.uk/mailman/listinfo
> >
> > --
> > CFDeveloper Sponsors:-
> > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> > >- Lists hosted by www.Gradwell.com -<
> > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
> > >help -<
> >
>
>
> --
> Simon Baynes
> www.simonbaynes.com
> _______________________________________________
>
> For details on ALL mailing lists and for joining or leaving lists, go
> to http://list.cfdeveloper.co.uk/mailman/listinfo
>
> --
> CFDeveloper Sponsors:-
> >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> >- Lists hosted by www.Gradwell.com -<
> >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
> >help -<
>
> _______________________________________________
>
> For details on ALL mailing lists and for joining or leaving lists, go
> to http://list.cfdeveloper.co.uk/mailman/listinfo
>
> --
> CFDeveloper Sponsors:-
> >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
> >- Lists hosted by www.Gradwell.com -<
> >- CFdeveloper is run by Russ Michaels, feel free to volunteer your
> >help -<
>
--
Simon Baynes
www.simonbaynes.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
>- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
>- Lists hosted by www.Gradwell.com -<
>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help
>-<
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
>- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
>- Lists hosted by www.Gradwell.com -<
>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help
>-<
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
>- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
>- Lists hosted by www.Gradwell.com -<
>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
