How to generate an XML file from SSIS (based on an XSD)? RRS feed

  • Question

  • I have an XSD that describes an XML format. I also have a sample XML file as well from the XSD. I am trying to understand SSIS's capability to generate the XML file in an ETL process. I would need to create the XML file based on the XSD that I have. It is safe to assume that all the data elements are in the DB that I am incorporating in the ETL and it is also safe to assume that the data in the XML may be from multiple different tables (so massaging is probably necessary). 

    Would this be a job for C#/VB or can I do this through an SSIS process/task? Please help me understand my options. Thanks.

    Friday, April 1, 2011 5:08 PM


All replies

    • Marked as answer by Jerry Nee Sunday, April 10, 2011 2:27 AM
    Friday, April 1, 2011 6:27 PM
  • I am trying to export an XML file in SSIS based on an XSD. So, I don't need the XSD, I already have that.

    I understand that one option is creating a table structure similar to the XSD and using SQL to create the XML, then write that to a file. I am just curious if there are built-in tools to do this in SSIS 2008.

    Friday, April 1, 2011 7:43 PM
  • I don't believe RamJaddu's post of April 1, 2011 answers the question.  What josh6847 needs is the XML not the XSD.

    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    Sunday, April 10, 2011 12:16 PM
  • Dear All,

    Indeed the post by RamJaddu did not answer the question. I am having the same issue: I have been provided an .xsd file which contains a data schema, and I have all of the necessary data in a .mdf file in order to create the required .xml file. Having queried SQL Server and received my data in "myDataSet" I can use C# to create the .xml ignoring the schema using

    string strSchemaFile = @"F:\Documents\UserCost\VictorianDataCollection\VCDCSchema_v2.xsd";
    string strXmlFile = "testXmlExport.xml";
    myDataSet.WriteXml(strXmlFile, XmlWriteMode.IgnoreSchema);

    but how do I export the .xml file so that is conforms with the .xsd format?

    If any of you have worked this out the answer would be most appreciated.

    All the best,








    "Everything should be made as simple as possible, but not simpler" - Einstein
    Tuesday, June 14, 2011 10:43 AM
  • Jerry Nee and RamJaddu this did not answer the question. It in fact gave the process for brining in an XML file not generating one from within SSIS (as was asked). Please mark this as unanswered.
    • Proposed as answer by dfsghn Friday, June 24, 2016 6:21 PM
    Tuesday, March 18, 2014 1:56 PM