Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding a Header Row to a SSIS package exporting out as XML Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2014 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:04 AM
Points: 25, Visits: 49
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
Post #1590689
Posted Wednesday, July 9, 2014 11:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 10,381, Visits: 13,435
What do you mean by a header row? By definition XML doesn't have rows, it has elements and attributes.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1590868
Posted Wednesday, July 9, 2014 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:04 AM
Points: 25, Visits: 49
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
Post #1590896
Posted Friday, July 25, 2014 7:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 6:32 AM
Points: 87, Visits: 380
Please post your solution!

Thanks...


Post #1596443
Posted Saturday, July 26, 2014 4:31 AM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:04 AM
Points: 25, Visits: 49
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.
Post #1596497
Posted Sunday, July 27, 2014 1:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 6:32 AM
Points: 87, Visits: 380
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!

Post #1596643
Posted Monday, July 28, 2014 6:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:04 AM
Points: 25, Visits: 49
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
Post #1596800
Posted Monday, July 28, 2014 6:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:04 AM
Points: 25, Visits: 49
Forgot to mention. I'm running mine in an SSIS package so just created a Flat File destination to send the file.
Post #1596801
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse