November 14, 2012 at 1:01 am
Hi All,
I need to dump the XML query results into a file using the stored procedure below without using BCP.
Your help would be appreciated.
Here's the script I'm using:
ALTER PROCEDURE Test
AS
BEGIN
DECLARE @XML Nvarchar(max)
/* GRADUATES */
SET @XML = '<DataExtract>'
SET @XML = @XML + (SELECT Top 5
pe.candIdas [Id],
ISNULL(FirstName + ' '+ LastName, '')as [Name],
ISNULL(PreferedName, '')as [PreferredName],
ISNULL(pe.IDNumber, '')as [IdPassportNumber],
ISNULL(CONVERT(varchar(10), pe.dteDateOfBirth, 120), '')as [DateOfBirth],
ISNULL(pe.Mobile, '')as [CellNumber],
ISNULL(pe.EMail, '')as [Email],
ISNULL(pe.AltEmail, '')as [AltEmail],
(SELECT ISNULL(AltContactName, '')as [Name],
ISNULL(AltContactPhone, '')as [Phone]
FROM [CandPersonal]
WHERE candId = pe.CandId
FOR XML PATH, TYPE )as 'AlternateContactPerson',
''as [PreferredContactMethod], --??
Case When isWorkPermit = 1 Then 'True' Else 'False' Endas [RequiresWorkPermit],
ISNULL(rg.GenderName, '')as [Gender],
ISNULL(eo.EthnicName, '')as [Ethnicity],
ISNULL(n.NationalityName, '')as [Citizenship],
Case When ceo.hasDisability = 1 Then 'True' Else 'False' Endas [HasDisability],
ISNULL(ceo.DisabilityDetail, '')as [DisabilityDetail],
Case When pe.bitHasConviction = 1 Then 'True' Else 'False' Endas [HasCriminalConvictions],
ISNULL(pe.nvcConvictionDetails, '')as [CriminalConvictions],
ISNULL(pe.intPreferedAddress, '')as [PreferredAddress], --??
ISNULL(CONVERT(varchar(10), pr.RegisterDate, 120), '')as [RegistrationDate],
(SELECT
ISNULL(CurrentAddressStreet, '')as [Street],
ISNULL(CurrentAddressCity, '')as [City],
ISNULL(CurrentAddressProvince, '')as [Province],
ISNULL(CurrentAddressPostcode, '')as [Postcode],
ISNULL(CurrentAddressCountryId, '')as [Country],
ISNULL(CurrentAddressPhone, '')as [Phone]
FROM [CandPersonal] WHERE CandId = pe.CandId
FOR XML PATH, TYPE )as 'CurrentPostalAddress',
(SELECT
ISNULL(HomeAddressStreet, '')as [Street],
ISNULL(HomeAddressCity, '')as [City],
ISNULL(HomeAddressProvince, '')as [Province],
ISNULL(HomeAddressPostcode, '')as [Postcode],
ISNULL(HomeAddressCountryId, '')as [Country],
ISNULL(HomeAddressPhone, '')as [Phone]
FROM [CandPersonal]
WHERE CandId = pe.candId
FOR XML PATH, TYPE )as 'HomePostalAddress',
ISNULL(cpl.LocationName, '')as [PreferredOfficeLocation], --??
''as [PreferredJobType], --??
''as [HighestQualification], --??
(SELECT
ISNULL(vchSchoolName, '')as [SchoolName],
ISNULL(vchCity, '')as [City],
ISNULL(ea.EducationalAuthorityName, '') as [EducationAuthority],
ISNULL(intGrade12Status, '')as [Grade12Status] --??
FROM [CandSecondaryEducation] ce
Inner Join refEducationalAuthority ea on ce.intEducationalAuthority = ea.EducationalAuthorityId
WHERE ce.CandId = pe.CandId FOR XML PATH, TYPE )as 'SecondaryEducation',
(SELECT
ISNULL(nvcInstitution, '')as [TertiaryInstitution],
ISNULL(nvcQualification, '')as [TertiaryQualification],
ISNULL(nvcName, '')as [FieldOfStudy],
ISNULL(vchCompletionYear, '')as [CompletionYear]
FROM CandQualification cq
Left Join refFieldOfStudy fos on cq.intFieldOfStudy = fos.intFieldOfStudyID
WHERE cq.intCandidateID = pe.CandId FOR XML PATH, TYPE )as 'TertiaryEducation',
ISNULL(pe.intHomeLanguage, '')as [HomeLanguage],--??
(SELECT ISNULL(EmployerName, '')as [Employer],
ISNULL(NatureOfBusiness, '')as [NatureOfBusiness],
ISNULL(CONVERT(varchar(10), FromDate, 120), '')as [From],
ISNULL(CONVERT(varchar(10), ToDate, 120), '')as [To],
ISNULL(PositionHeld, '')as [Position],
ISNULL(DutiesSummary, '')as [Duties]
FROM CandEmployHistory ce
WHERE ce.CandId = pe.CandId
FOR XML PATH, TYPE )as 'WorkExperisnce'
FROM [dbo].[CandPersonal] pe
Inner Join CandProfile pr on pe.CandId = pr.Id
Left Join CandEqualOpps ceo on pe.CandId = ceo.CandId
Left Join refGender rg ON rg.GenderId = ceo.GenderId
Left Join refEthnicOrigin eo on ceo.EthnicOriginId = eo.EthnicOriginId
Left Join refNationality n on ceo.NationalityId = n.NationalityId
Left Join CandPrefLocation cpl on pe.CandId = cpl.CandId
WHERE pr.isGraduateApplicant = 1
--And pe.CandId IN ('475', '476', '477')
FOR XML PATH('Candidate'), ROOT('Graduates'))
/* EXPERIENCED HIRES */
SET @XML = @XML + (SELECT Top 5
pe.candIdas [Id],
ISNULL(FirstName + ' '+ LastName, '')as [Name],
ISNULL(PreferedName, '')as [PreferredName],
ISNULL(pe.IDNumber, '')as [IdPassportNumber],
ISNULL(CONVERT(varchar(10), pe.dteDateOfBirth, 120), '')as [DateOfBirth],
ISNULL(pe.Mobile, '')as [CellNumber],
ISNULL(pe.EMail, '')as [Email],
ISNULL(pe.AltEmail, '')as [AltEmail],
(SELECT ISNULL(AltContactName, '')as [Name],
ISNULL(AltContactPhone, '')as [Phone]
FROM [CandPersonal]
WHERE candId = pe.CandId
FOR XML PATH, TYPE )as 'AlternateContactPerson',
''as [PreferredContactMethod], --??
Case When isWorkPermit = 1 Then 'True' Else 'False' Endas [RequiresWorkPermit],
ISNULL(rg.GenderName, '')as [Gender],
ISNULL(eo.EthnicName, '')as [Ethnicity],
ISNULL(n.NationalityName, '')as [Citizenship],
Case When ceo.hasDisability = 1 Then 'True' Else 'False' Endas [HasDisability],
ISNULL(ceo.DisabilityDetail, '')as [DisabilityDetail],
Case When pe.bitHasConviction = 1 Then 'True' Else 'False' Endas [HasCriminalConvictions],
ISNULL(pe.nvcConvictionDetails, '')as [CriminalConvictions],
ISNULL(pe.intPreferedAddress, '')as [PreferredAddress], --??
ISNULL(CONVERT(varchar(10), pr.RegisterDate, 120), '')as [RegistrationDate],
(SELECT
ISNULL(CurrentAddressStreet, '')as [Street],
ISNULL(CurrentAddressCity, '')as [City],
ISNULL(CurrentAddressProvince, '')as [Province],
ISNULL(CurrentAddressPostcode, '')as [Postcode],
ISNULL(CurrentAddressCountryId, '')as [Country],
ISNULL(CurrentAddressPhone, '')as [Phone]
FROM [CandPersonal] WHERE CandId = pe.CandId
FOR XML PATH, TYPE )as 'CurrentPostalAddress',
(SELECT
ISNULL(HomeAddressStreet, '')as [Street],
ISNULL(HomeAddressCity, '')as [City],
ISNULL(HomeAddressProvince, '')as [Province],
ISNULL(HomeAddressPostcode, '')as [Postcode],
ISNULL(HomeAddressCountryId, '') as [Country],
ISNULL(HomeAddressPhone, '') as [Phone]
FROM [CandPersonal]
WHERE CandId = pe.candId
FOR XML PATH, TYPE )as 'HomePostalAddress',
ISNULL(cpl.LocationName, '')as [PreferredOfficeLocation], --??
''as [PreferredJobType], --??
''as [HighestQualification], --??
(SELECT
ISNULL(vchSchoolName, '')as [SchoolName],
ISNULL(vchCity, '')as [City],
ISNULL(ea.EducationalAuthorityName, '') as [EducationAuthority],
ISNULL(intGrade12Status, '')as [Grade12Status] --??
FROM [CandSecondaryEducation] ce
Inner Join refEducationalAuthority ea on ce.intEducationalAuthority = ea.EducationalAuthorityId
WHERE ce.CandId = pe.CandId
FOR XML PATH, TYPE )as 'SecondaryEducation',
(SELECT
ISNULL(nvcInstitution, '')as [TertiaryInstitution],
ISNULL(nvcQualification, '')as [TertiaryQualification],
ISNULL(nvcName, '')as [FieldOfStudy],
ISNULL(vchCompletionYear, '')as [CompletionYear]
FROM CandQualification cq
Left Join refFieldOfStudy fos on cq.intFieldOfStudy = fos.intFieldOfStudyID
WHERE cq.intCandidateID = pe.CandId
FOR XML PATH, TYPE )as 'TertiaryEducation',
ISNULL(pe.intHomeLanguage, '')as [HomeLanguage],--??
(SELECT ISNULL(EmployerName, '')as [Employer],
ISNULL(NatureOfBusiness, '')as [NatureOfBusiness],
ISNULL(CONVERT(varchar(10), FromDate, 120), '')as [From],
ISNULL(CONVERT(varchar(10), ToDate, 120), '')as [To],
ISNULL(PositionHeld, '')as [Position],
ISNULL(DutiesSummary, '') as [Duties]
FROM CandEmployHistory ce
WHERE ce.CandId = pe.CandId
FOR XML PATH, TYPE )as 'WorkExperisnce'
FROM [dbo].[CandPersonal] pe
Inner Join CandProfile pr on pe.CandId = pr.Id
Left Join CandEqualOpps ceo on pe.CandId = ceo.CandId
Left Join refGender rg ON rg.GenderId = ceo.GenderId
Left Join refEthnicOrigin eo on ceo.EthnicOriginId = eo.EthnicOriginId
Left Join refNationality n on ceo.NationalityId = n.NationalityId
Left Join CandPrefLocation cpl on pe.CandId = cpl.CandId
WHERE pr.isGraduateApplicant = 0
--And pe.CandId IN ('1459', '1756', '5103')
FOR XML PATH('Candidate'), ROOT('ExperiencedHires'))
SET @XML = @XML + '</DataExtract>'
SELECT CAST(@XML as XML)
END
Thanks
Teee
November 14, 2012 at 1:28 am
What's wrong with bcp? It's probably the most efficient method to create the XML file.
An alternative is to use SSIS and the export column component in the dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2012 at 1:47 am
I'd like to do it with the script as the stored proc wold be called from the application.
November 14, 2012 at 1:49 am
Then bcp would be ideal.
Or if you want to complicate things, you could use OLE Automation:
Create File from T-SQL Using OLE Automation[/url]
OLE Automation Stored Procedures
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2012 at 1:59 am
Please show me the BCP example as well.
Thanks
November 14, 2012 at 2:04 am
Here you go:
http://lmgtfy.com/?q=sql+server+export+to+xml+bcp
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2012 at 2:18 am
Thank you 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply