You may want to look at SQL 2005, aS I know it has more advanced XML
support, not that I have use dit myself.

Russ

-----Original Message-----
From: database-bounces@list...
[mailto:database-bounces@list...] On Behalf Of Lee Fortnam
Sent: 27 November 2006 15:31
To: 'database programming'
Subject: RE: [CF-Database] Inserting multiple records with XML and Cursor

Thanks Russ,

Problem is the XML doc can contain 10K items which was the subject of a
thread some time ago and this was see as the best way to get it into the DB.
Apparently once the openxml doc element has been used the XML is available
as a dataset so the looping through can be done in a number of ways but not
one that I have seen that makes sense.

Lee Fortnam

-----Original Message-----
From: database-bounces@list...
[mailto:database-bounces@list...] On Behalf Of Snake
Sent: 27 November 2006 15:27
To: 'database programming'
Subject: RE: [CF-Database] Inserting multiple records with XML and Cursor

I can't re4ally see how you could avoid cursors in this situation as your
having to loop over a document.
Perhaps you should try parsing the XML on CF first and them passing
attributes to your stored proc instea dof parsing the XML insid ethe stored
proc, and compare the performance to see which is better.

Russ

-----Original Message-----
From: database-bounces@list...
[mailto:database-bounces@list...] On Behalf Of Lee Fortnam
Sent: 27 November 2006 15:14
To: 'database programming'
Subject: [CF-Database] Inserting multiple records with XML and Cursor

Hi All,

Me again, have been reading lots of sites about how bad cursors are for
Stored Procedures and so wanted to try and update my current SP with a
revised version (also need to write a couple of new ones so seemed like a
good time to look into it).

I have a stored procedure which accepts an XML document passed as type text
(sample of xml file below). I need to loop through the xml file performing
some actions based on the variables from it. How can I achieve the same but
without the performance hit of cursors, tried looking at the usual sites
such as 4guysfromrolla etc but to no avail, any help would be much
appreciated.

The other versions I need to do do not include an XML file but literally
reading from 1 table (Table A - some default values) and then updating
another table (table b) which has received some updates, i.e. so for each
record in Table A, update any machine records based on a couple of variables
in Table B.

Regards

--prepare the XML Document by executing a system stored procedure exec
sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

-- Declare the cursor
DECLARE Appointments CURSOR FOR
SELECT
NHSNumber, expectedTXDateTime, messageDate, messageTime, toSee FROM
OPENXML(@xml_hndl, '/Appointments/row', 2)
WITH
(
NHSNumber nvarchar(50) 'NHSNumber',
ExpectedTXDateTime nvarchar(50)
'ExpectedTXDateTime',
MessageDate nvarchar(10) 'MessageDate',
MessageTime nvarchar(5) 'MessageTime',
ToSee nvarchar(200) 'ToSee'
)
OPEN Appointments

-- Get first element of data
FETCH Appointments INTO @vch_NHSNumber,
@vch_ExpectedTXDateTime,
@vch_MessageDate,
@vch_MessageTime,
@vch_ToSee

-- Do my actions

-- Get next record
FETCH Appointments INTO @vch_NHSNumber,
@vch_ExpectedTXDateTime,
@vch_MessageDate,
@vch_MessageTime,
@vch_ToSee

CLOSE Appointments

DEALLOCATE Appointments

-- Clear the XML document from memory
exec sp_xml_removedocument @xml_hndl
GO

Lee Fortnam

_______________________________________________

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
>-<


_______________________________________________

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 -<