How to export XML query results to a file using Sql Server 2008 R2 From Management Studio

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to export XML query results to a file...

How to export XML query results to a file using Sql Server 2008 R2 From Management Studio

Rate Topic

Display Mode

Topic Options

Author

Message

Teee

Posted Wednesday, November 14, 2012 1:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:23 AM
Points: 31, Visits: 121

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.candId as [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' End as [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' End as [HasDisability],
ISNULL(ceo.DisabilityDetail, '') as [DisabilityDetail],
Case When pe.bitHasConviction = 1 Then 'True' Else 'False' End as [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.candId as [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' End as [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' End as [HasDisability],
ISNULL(ceo.DisabilityDetail, '') as [DisabilityDetail],
Case When pe.bitHasConviction = 1 Then 'True' Else 'False' End as [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

Post #1384430

Koen Verbeeck

Posted Wednesday, November 14, 2012 1:28 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1384447

Teee

Posted Wednesday, November 14, 2012 1:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:23 AM
Points: 31, Visits: 121

I'd like to do it with the script as the stored proc wold be called from the application.

Post #1384458

Koen Verbeeck

Posted Wednesday, November 14, 2012 1:49 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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

OLE Automation Stored Procedures

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1384459

Teee

Posted Wednesday, November 14, 2012 1:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:23 AM
Points: 31, Visits: 121

Please show me the BCP example as well.

Thanks

Post #1384468

Koen Verbeeck

Posted Wednesday, November 14, 2012 2:04 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

Here you go:

http://lmgtfy.com/?q=sql+server+export+to+xml+bcp

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1384470

Teee

Posted Wednesday, November 14, 2012 2:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:23 AM
Points: 31, Visits: 121

Thank you

Post #1384477

« Prev Topic | Next Topic »

Permissions