August 8, 2007 at 7:51 am
Hi all, new here, sorry to be posting a question as my first post!
I am running a query that creates a temp table with xml tags and data as text strings. The whole results file should then create a valid XML file. The dataset I am working with is 70,000 people, the XML file ultimately ends up with 1.7million lines. The query works fine when I restrict the dataset to 1000 people but when I attempt any more, rows start to dissappear, and the end tags of the file are missing? Any thouhgts? The query is... (some text has been replaced with smilies!) Sorry, it is converting most of the post to HTML and has removed a lot of the data! How do I turn HTML off in my posts?
CREATE procedure [dbo].[sp_ContactPointExport_DOB_Batch] (
@PupilsExported Int Output,
@DOB_START varchar(10),
@DOB_END varchar(10)
)
As
Declare @sql Varchar(1000)
Begin
SET NOCOUNT ON
-- data pupils
--Declare Pupils Table(
Create table #Pupils(
p_PER_ID int,
p_FNameVarchar(35),
p_MiddleNamesVarchar(35),
p_SNameVarchar(35),
p_DOBDatetime,
p_SexVarchar(1),
p_DateDeceasedDateTime)
Declare @Today DateTime
Set @Today = getdate()
Insert #Pupils(p_PER_ID, p_FName, p_MiddleNames, p_SName, p_DOB, p_Sex, p_DateDeceased)
Select tblPerson.PER_ID, tblPerson.PER_FNAME, tblPerson.PER_MNAME, tblPerson.PER_SNAME, tblPerson.PER_DOB, tblPerson.PER_SEX, tblpupil.PUP_DECEASED_DATE
From tblPupil
Join tblPerson on tblPupil.PER_ID = tblPerson.PER_ID
Where (tblPerson.per_from_date <= getdate() and (tblPerson.per_to_date >= getdate() or tblPerson.per_to_date is null))
And dbo.fn_CalcAge(tblPerson.PER_DOB, @Today) < 18 --< 18 years old
And tblPERSON.PER_DOB BETWEEN CONVERT(datetime, @DOB_START, 103) AND CONVERT(datetime, @DOB_END, 103)
order by tblPERSON.PER_DOB desc
-- xml
CREATE TABLE #XML (
MyID Int identity(1,1),
ExportData Varchar(8000))
Declare @PER_ID Int
Declare @FName Varchar(35)
Declare @MName Varchar(35)
Declare @SName Varchar(35)
Declare @DOB DateTime
Declare @Sex Varchar(1)
Declare @DateDeceased DateTime
Declare @NewLine Varchar(2)
Declare @DateTime DateTime
Declare @CONTACT_ID Int
Declare @ContactFName Varchar(35)
Declare @ContactSName Varchar(35)
Declare @ContactEmail Varchar(255)
Declare @ContactHomeNo Varchar(255)
Declare @ContactWorkNo Varchar(255)
Declare @ContactMobileNo Varchar(255)
Declare @ContactFaxNo Varchar(255)
Declare @ContactType Varchar(255)
Declare @ContactStartDate DateTime
Declare @ContactEndDate DateTime
Declare @AcademicYear Int
Declare @ACYearStartDate Varchar(30)
Declare @ACYearEndDate Varchar(30)
Declare @ADR_NUMBER Varchar(100)
Declare @ADR_LOCALITY Varchar(100)
Declare @ADR_TOWN Varchar(100)
Declare @ADR_COUNTY Varchar(100)
Declare @ADR_POSTCODE Varchar(8)
Declare @PARENT_CURSOR_ROWS Int
Declare @SERVICE_CURSOR_ROWS Int
Set @NewLine = char(32)
Set @PupilsExported = 0
Set @DateTime = getdate()
--Get the current academic year.
Exec sp_GetACYearNonTermDates @ACYearStartDate output, @ACYearEndDate output
Set @AcademicYear = cast(year(@ACYearStartDate) as varchar(4))
--Start Cursor...
DECLARE PupilCursor CURSOR Local FORWARD_ONLY SCROLL_LOCKS KEYSET FOR
Select Distinct p_PER_ID, p_FName, p_MiddleNames, p_SName, p_DOB, p_Sex, p_DateDeceased
From #Pupils p
open PupilCursor
Fetch Next From PupilCursor Into @PER_ID, @FName, @MName, @SName, @DOB, @Sex, @DateDeceased
If @@fetch_status = 0
Begin
-- Required otherwise XML will be invalid.
Insert #XML(ExportData) Values ('' + @NewLine)
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails"')
Insert #XML(ExportData) Values ('xmlns:bs7666="http://www.govtalk.gov.uk/people/bs7666"')
Insert #XML(ExportData) Values ('xmlns:cdt="http://www.govtalk.gov.uk/people/ContactDetails"')
Insert #XML(ExportData) Values ('xmlns:ind="http://www.govtalk.gov.uk/Education/ISIndexCommon"')
Insert #XML(ExportData) Values ('xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"')
Insert #XML(ExportData) Values ('xsi:schemaLocation="http://www.govtalk.gov.uk/Education/ISIndexDataSourceMessage')
Insert #XML(ExportData) Values ('ISIndexDataSourceMessage-v1-1.xsd">')
-- Detail...
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('
While @@fetch_status = 0
Begin
if @@fetch_status = 0
Begin
Set @PupilsExported = cast (@PupilsExported + 1 as int)
Insert #XML(ExportData) Values ('
--Pupil stuff...
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
If coalesce(@MName,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
--Pupil Address Cursor
DECLARE ChildAddressCursor CURSOR Local SCROLL_LOCKS KEYSET FOR
SELECT [ADD].ADR_NUMBER, [ADD].ADR_LOCALITY, [ADD].ADR_TOWN, [ADD].ADR_COUNTY, [ADD].ADR_POSTCODE
FROM tblADDRESS [ADD] INNER JOIN
tblPUPIL_ADDRESS PAD ON [ADD].ADR_ID = PAD.tblADDRESS
WHERE PAD.tblPUPIL = @PER_ID
AND (PAD.PAD_TO_DATE IS NULL)
AND ([ADD].adr_type = 1)
Open ChildAddressCursor
Fetch Next From ChildAddressCursor Into @ADR_NUMBER, @ADR_LOCALITY, @ADR_TOWN, @ADR_COUNTY, @ADR_POSTCODE
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('
While @@fetch_status = 0
Begin
If (@@fetch_status = 0)
Begin
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
If coalesce(@ADR_LOCALITY,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ADR_TOWN,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ADR_COUNTY,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
End
Fetch Next From ChildAddressCursor Into @ADR_NUMBER, @ADR_LOCALITY, @ADR_TOWN, @ADR_COUNTY, @ADR_POSTCODE
End
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('')
Close ChildAddressCursor
Deallocate ChildAddressCursor
Insert #XML(ExportData)Values ('
If coalesce(@DateDeceased,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
DECLARE ContactCursor CURSOR Local SCROLL_LOCKS KEYSET FOR
Select CONTACT_ID, PUPCON_START, PUPCON_END, Contact_FName, Contact_SName, CTTP_CODE, Contact_Fax, Contact_Email, Contact_Tel_Day, Contact_Tel_Night, Contact_Mobile
From tblPupil_Contact pc
Join tblContact c on c.CONTACT_ID = pc.tblContact
Join tblLU_CONTACT_TYPE on c.tblLU_CONTACT_TYPE = tblLU_CONTACT_TYPE.CTTP_ID
Where pc.tblPupil = @PER_ID
And (pc.PUPCON_START = (Select max(PUPCON_START) From tblPupil_Contact Where tblContact = c.CONTACT_ID And tblPupil = @PER_ID)
And PUPCON_START <= getdate() And ((PUPCON_END >= getdate()) Or (PUPCON_END is null)))
And PUPCON_LEGALGUARD in (1,2)
Open ContactCursor
Fetch Next From ContactCursor Into @CONTACT_ID, @ContactStartDate, @ContactEndDate, @ContactFName, @ContactSName, @ContactType, @ContactFaxNo, @ContactEmail, @ContactHomeNo, @ContactWorkNo, @ContactMobileNo
Set @PARENT_CURSOR_ROWS = @@cursor_rows
--Parent contacts only...
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('
While @@fetch_status = 0
Begin
If (@@fetch_status = 0)
Begin
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('arentCarerName>')
--Parental Address Cursor
DECLARE ParentAddressCursor CURSOR Local SCROLL_LOCKS KEYSET FOR
SELECT tblADDRESS.ADR_NUMBER, tblADDRESS.ADR_LOCALITY, tblADDRESS.ADR_TOWN, tblADDRESS.ADR_COUNTY, tblADDRESS.ADR_POSTCODE
FROM tblPUPIL_CONTACT pc INNER JOIN
tblCONTACT c ON c.CONTACT_ID = pc.tblCONTACT INNER JOIN
tblLU_CONTACT_TYPE ON c.tblLU_CONTACT_TYPE = tblLU_CONTACT_TYPE.CTTP_ID AND
pc.PUPCON_START =
(SELECT MAX(PUPCON_START)
FROM tblPupil_Contact
WHERE tblContact = c.CONTACT_ID AND tblPupil = @PER_ID) AND pc.PUPCON_START <= GETDATE() AND
(pc.PUPCON_END >= GETDATE() OR
pc.PUPCON_END IS NULL) AND pc.PUPCON_LEGALGUARD = 1 AND c.tblLU_RELATION <> 1 INNER JOIN
tblCONTACT_ADDRESS ON c.CONTACT_ID = tblCONTACT_ADDRESS.tblCONTACT INNER JOIN
tblADDRESS ON tblCONTACT_ADDRESS.tblADDRESS = tblADDRESS.ADR_ID
WHERE pc.tblPupil = @PER_ID and pc.tblCONTACT = @CONTACT_ID
AND (tblCONTACT_ADDRESS.CAD_TO_DATE IS NULL)
Open ParentAddressCursor
Fetch Next From ParentAddressCursor Into @ADR_NUMBER, @ADR_LOCALITY, @ADR_TOWN, @ADR_COUNTY, @ADR_POSTCODE
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('
While @@fetch_status = 0
Begin
If (@@fetch_status = 0)
Begin
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
If coalesce(@ADR_LOCALITY,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ADR_TOWN,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ADR_POSTCODE,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('arentCarerAddress>')
End
Fetch Next From ParentAddressCursor Into @ADR_NUMBER, @ADR_LOCALITY, @ADR_TOWN, @ADR_COUNTY, @ADR_POSTCODE
End
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('arentCarerAddresses>')
Close ParentAddressCursor
Deallocate ParentAddressCursor
--end Parental Address Cursor
If
(len(coalesce(@ContactEmail,''))+
len(coalesce(@ContactHomeNo,''))+
len(coalesce(@ContactWorkNo,''))+
len(coalesce(@ContactMobileNo,''))) > 0
Begin
Insert #XML(ExportData)Values ('
If coalesce(@ContactEmail,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ContactHomeNo,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ContactWorkNo,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ContactMobileNo,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('arentCarerContactDetails>')
End
Insert #XML(ExportData) Values ('arentCarer>')
End
Fetch Next From ContactCursor Into @CONTACT_ID, @ContactStartDate, @ContactEndDate, @ContactFName, @ContactSName, @ContactType, @ContactFaxNo, @ContactEmail, @ContactHomeNo, @ContactWorkNo, @ContactMobileNo
End
If @PARENT_CURSOR_ROWS > 0 Insert #XML(ExportData) Values ('arentCarers>')
Close ContactCursor
Deallocate ContactCursor
DECLARE ServiceProvisionCursor CURSOR Local SCROLL_LOCKS KEYSET FOR
Select CONTACT_ID, PUPCON_START, PUPCON_END, Contact_FName, Contact_SName, CTTP_DESC, Contact_Fax, Contact_Email, Contact_Tel_Day, Contact_Tel_Night, Contact_Mobile
From tblPupil_Contact pc
Join tblContact c on c.CONTACT_ID = pc.tblContact
Join tblLU_CONTACT_TYPE on c.tblLU_CONTACT_TYPE = tblLU_CONTACT_TYPE.CTTP_ID
Where pc.tblPupil = @PER_ID
And (pc.PUPCON_START = (Select max(PUPCON_START) From tblPupil_Contact Where tblContact = c.CONTACT_ID And tblPupil = @PER_ID)
And PUPCON_START <= getdate() And ((PUPCON_END >= getdate()) Or (PUPCON_END is null)))
And PUPCON_LEGALGUARD = 0
And tblLU_RELATION = 1
Open ServiceProvisionCursor
--Service Provisions...
Fetch First From ServiceProvisionCursor Into @CONTACT_ID, @ContactStartDate, @ContactEndDate, @ContactFName, @ContactSName, @ContactType, @ContactFaxNo, @ContactEmail, @ContactHomeNo, @ContactWorkNo, @ContactMobileNo
Set @SERVICE_CURSOR_ROWS = @@cursor_rows
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('
While @@fetch_status = 0
Begin
If (@@fetch_status = 0)
Begin
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
If coalesce(@ContactEndDate,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('ractitionerName>')
Insert #XML(ExportData)Values ('
Insert #XML(ExportData)Values ('
--Service Provision Address Cursor
DECLARE ServiceAddressCursor CURSOR Local SCROLL_LOCKS KEYSET FOR
SELECT tblADDRESS.ADR_NUMBER, tblADDRESS.ADR_LOCALITY, tblADDRESS.ADR_TOWN, tblADDRESS.ADR_COUNTY, tblADDRESS.ADR_POSTCODE
FROM tblPUPIL_CONTACT pc INNER JOIN
tblCONTACT c ON c.CONTACT_ID = pc.tblCONTACT INNER JOIN
tblLU_CONTACT_TYPE ON c.tblLU_CONTACT_TYPE = tblLU_CONTACT_TYPE.CTTP_ID AND
pc.PUPCON_START =
(SELECT MAX(PUPCON_START)
FROM tblPupil_Contact
WHERE tblContact = c.CONTACT_ID AND tblPupil = @PER_ID) AND pc.PUPCON_START <= GETDATE() AND
(pc.PUPCON_END >= GETDATE() OR
pc.PUPCON_END IS NULL) AND pc.PUPCON_LEGALGUARD = 0 AND c.tblLU_RELATION = 1 INNER JOIN
tblCONTACT_ADDRESS ON c.CONTACT_ID = tblCONTACT_ADDRESS.tblCONTACT INNER JOIN
tblADDRESS ON tblCONTACT_ADDRESS.tblADDRESS = tblADDRESS.ADR_ID
WHERE pc.tblPupil = @PER_ID AND pc.tblCONTACT = @CONTACT_ID
AND (tblCONTACT_ADDRESS.CAD_TO_DATE IS NULL)
Open ServiceAddressCursor
Fetch Next From ServiceAddressCursor Into @ADR_NUMBER, @ADR_LOCALITY, @ADR_TOWN, @ADR_COUNTY, @ADR_POSTCODE
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('
While @@fetch_status = 0
Begin
If (@@fetch_status = 0)
Begin
Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
If coalesce(@ADR_LOCALITY,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ADR_TOWN,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ADR_POSTCODE,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData)Values ('
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
End
Fetch Next From ServiceAddressCursor Into @ADR_NUMBER, @ADR_LOCALITY, @ADR_TOWN, @ADR_COUNTY, @ADR_POSTCODE
End
If @@cursor_rows > 0 Insert #XML(ExportData) Values ('')
Close ServiceAddressCursor
Deallocate ServiceAddressCursor
--end Service Provision Address Cursor
If
(len(coalesce(@ContactEmail,''))+
len(coalesce(@ContactFaxNo,''))+
len(coalesce(@ContactWorkNo,''))+
len(coalesce(@ContactMobileNo,''))) > 0
Begin
Insert #XML(ExportData) Values ('
If coalesce(@ContactEmail,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ContactWorkNo,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ContactMobileNo,'')<>'' Insert #XML(ExportData) Values ('
If coalesce(@ContactFaxNo,'')<>'' Insert #XML(ExportData) Values ('
Insert #XML(ExportData) Values ('')
End
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
End
Fetch Next From ServiceProvisionCursor Into @CONTACT_ID, @ContactStartDate, @ContactEndDate, @ContactFName, @ContactSName, @ContactType, @ContactFaxNo, @ContactEmail, @ContactHomeNo, @ContactWorkNo, @ContactMobileNo
End
If @SERVICE_CURSOR_ROWS > 0 Insert #XML(ExportData) Values ('')
Close ServiceProvisionCursor
Deallocate ServiceProvisionCursor
Insert #XML(ExportData) Values ('ataSourceRecord>')
Fetch Next From PupilCursor Into @PER_ID, @FName, @MName, @SName, @DOB, @Sex, @DateDeceased
End
End
If @PupilsExported > 0
Begin
Insert #XML(ExportData) Values ('')
Insert #XML(ExportData) Values ('')
--Update record count.
Update #XML Set ExportData = replace(ExportData,'#val',@PupilsExported)
End
--Convert special characters.
Update #XML
Set ExportData = dbo.fn_ConvertSpecialCharacters(ExportData)
Close PupilCursor
Deallocate PupilCursor
End
Select ExportData From #XML
SET NOCOUNT OFF
End
/*Family Name Family name or surname N
Email Address Email address for the Parent/Carer N
Home Phone Number Home Contact Phone number for Parent/Carer N
Work Phone Number Work Contact Phone number for Parent/Carer N
Mobile Phone Number Mobile Contact Phone number for Parent/Carer N
Declare @Count Int
Exec sp_ContactPointExport @Count Output
Select @Count
*/
GO
August 8, 2007 at 3:03 pm
Nested cursors….awesome! How many hours does this take to run on your 70,000 rows? Since this is your first time here, I won’t be to hard on your, but cursors are the devil. Why don’t you let SQL Server generate the XML for you. It seems like you are going about this the hard way. Is there a reason why you are trying to generate the XML manually?
August 8, 2007 at 6:50 pm
Read about "FOR XML EXPLICIT" in BOL.
Probably everything you need is there.
_____________
Code for TallyGenerator
August 9, 2007 at 2:36 am
I have inherited this script, it was written by a third party supplier, thats why the xml is generated manually. The problem is it didn't do what I needed so I amended it. 70,000 rows takes 30 minutes. Not bad, I can live with that. I need this working this week as it the deadline is approaching so I don't think I have time to re-write using FOR XML
August 9, 2007 at 3:36 am
I take it I can't learn FOR XML EXPLICIT in 2 days and then write the above nested cursor in FOR XML format?!
August 9, 2007 at 4:59 am
Who told you you need cursors for "XML EXPLICIT"?
And it takes less than 2 days to learn.
_____________
Code for TallyGenerator
August 9, 2007 at 5:05 am
Sorry, what I meant was. can I learn FOR XML and re-create the above processing in 2 days. I realise cursors would no longer apply.
But, having said all of this, the above nested cursor script runs in 30 minutes. My only issue with it is that query analyzer seems to stop in strange places and not insert some of the data into the table variable. I was hoping somebody had come accross this before!
August 9, 2007 at 5:25 am
Ouch!
30 minutes!
You must be kidding.
I thought my terribly long script generating complex XCBL was too slow - it took up to 3 seconds...
_____________
Code for TallyGenerator
August 9, 2007 at 5:29 am
The thing is, I only need to run this script once, maybe twice and that's it! I'd rather fix what I have, run it then ditch it forever! Any ideas on why the table variable is missing rows?
August 9, 2007 at 8:33 am
All, I have resolved this issue. I changed the triggers to read only fast forward and all seems to be fine. thanks for the responses!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply