SQL 2005 need to generate XML per each new record

tommyo94
tommyo94 used Ask the Experts™
on
We are currently running SQL 2005.  We need to generate an xml file as each new record is entered into the database.  I'm not sure where this would be done and we need to name each XML file with the incident number which has its own field.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
I'm not sure where this would be done and we need to name each XML file with the incident number which has its own field.
This would be done at the application (presentation layer) and not in SQL Server itself.

Author

Commented:
So in the export from SQL server you cannot name the XML export with the value of one of the fields?
Top Expert 2012

Commented:
So in the export from SQL server you cannot name the XML export with the value of one of the fields?
What do you mean by export?  Do you mean SSIS?  If so, absolutely, SSIS is your application (presentation layer) and are at liberty to do whatever you can in that environment.

Author

Commented:
SSIS is what I am using.  I'm not sure what commands to issue to modify the naming of the XML output file .
Top Expert 2012
Commented:
You can use an expression for that.  Here is a good explanation as to how to do it:
http://www.mssqltips.com/sqlservertip/1084/dynamic-flat-file-connections-in-sql-server-integration-services/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial