On Mon, Feb 14, 2011 at 6:27 PM, Mark Mandel <[email protected]> wrote:
> > Either that, or start looking at something like StaX - > http://stax.codehaus.org/Home > > Pulling that large a file in memory is going to suck a lot, no matter > what you do. > > Mark > > yeah, I don't know, still seems better equipped for SQL server if you can do it there. <cfstoredproc procedure="spBIG_XML_Insert" datasource="#request.DataSource#" returncode="Yes"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="mydoc" value="#r.SXMLRESPONSE#"> </cfstoredproc> And then the trimmed down Stored Procedure.... ALTER PROCEDURE [dbo].[spBIG_XML_Insert] @mydoc xml, AS declare @hdoc int -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @mydoc, '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:xsd="http://www.w3.org/2001/XMLSchema"; xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"; xmlns:c="urn:Scores/Services/v2/ScoresUpdate" />' -- Now do the insert INSERT INTO tblScoreUpdates SELECT MemberID = x.MemberID, ReportDate = x.ReportDate, Score = x.Score, DateAdded = GetDate() FROM OPENXML (@hdoc, 'soap:Envelope/soap:Body/c:LatestScoresResponse/c:parameters/c:Scores',2) WITH ( MemberID varchar(10) '@MemberID', ReportDate varchar(25) '@ReportDate', Score int '@Score') as x WHERE x.MemberID NOT IN (SELECT t.MemberID FROM tblScoreUpdates t With (NoLock) WHERE t.MemberID = x.MemberID) ; --Do another query here if you want, --remove the document from memory EXEC sp_xml_removedocument @hDoc And if you needed something from a level up... just dot notation back to it. LogID = int '../@LogID which would grab the logID from Parameters... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342218 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
