Adding a Header Row to a SSIS package exporting out as XML

Question

Adding a Header Row to a SSIS package exporting out as XML

WayneHess

Say Hey Kid

Points: 683
More actions
July 9, 2014 at 5:59 am

#100189

I've been asked to create an SSIS package to export data out as and XML file.
I've got the xml file down but now the customer is wanting a header row added to the first line of the xml file.
I've tried adding it in the Header row for the flat file destination but getting an error that it cannot parse the Header file.
Any suggestions would be appreciated.
Thanks in Advance
Wayne

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What do you mean by a header row? By definition XML doesn't have rows, it has elements and attributes.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

WayneHess Say Hey Kid Points: 683 More actions · Answer 2

I had created the SSIS package to pull just the student data.

What the vendor was wanting was the XML information <?xml version="1.0" encoding="utf-8"?> added to the first row of the extract.

I finally found the place to add it and how to add it after several searches.

Thanks

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 3

inevercheckthis2002

SSCommitted

Points: 1819

July 25, 2014 at 7:07 pm

#1732566

Please post your solution!

Thanks...

WayneHess Say Hey Kid Points: 683 More actions · Answer 4

In the heading area when setting up the destination file I had to add in <?xml version="1.0" encoding="utf-8"?>

That gave me what the vendor was needing in the file.

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 5

If you can possibly describe how you get the file to output, I would greatly appreciate it.

I have a query where I can get the XML that I need, but When I run the query with 'Results to text' or 'Results to file' selected, the text or file contains a line starting with the characters "XML..." and a second line containing dashes '--------------...'

I just need the XML in a file.

Thanks for any direction that you may provide!

WayneHess Say Hey Kid Points: 683 More actions · Answer 6

Here is how I setup my SQL Script to pull the data into an XML File.

SELECT(select s.StudentNumber As DisplayID, s.PersonID AS ExternalAccountID, sc.number AS SchoolID,s.FirstName, s.LastName, s.Gender,

CONVERT(varchar,s.birthdate,101) AS Birthdate, s.Grade AS GradeLevel,

CASE WHEN ts.PhysicalDate IS NOT NULL THEN CONVERT(varchar,ts.physicaldate,101)ELSE '' END As LastPhysicalDate,

MAX(CASE WHEN pe.eligibility ='F' THEN '3'WHEN pe.eligibility = 'R' THEN '2'ELSE '1'END) As EconomicIndicator

FROM student s

JOIN SchoolYear sy ON sy.endYear = s.endYear

LEFT JOIN TempScreening ts ON ts.personid = s.personID

JOIN calendar ca ON ca.calendarID = s.calendarID

JOIN School sc ON sc.schoolID = ca.schoolid

LEFT JOIN POSEligibility pe on pe.personid = s.personid

where studentnumber IS NOT NULL and sy.active = 1 and s.endDate IS NULL or s.endDate >=GETDATE()

AND ca.schoolID NOT IN( 12,13,32,16,34,14,15,25)

GROUP BY s.studentNumber, s.personID, sc.number,s.firstName, s.lastName,s.gender, s.birthdate, s.grade,ts.physicaldate

FOR XML PATH('Student'), root('Students')

)as Data

Hope this helps

WayneHess Say Hey Kid Points: 683 More actions · Answer 7

Forgot to mention. I'm running mine in an SSIS package so just created a Flat File destination to send the file.