SSIS: Add schema to xml file

Question

I need to pull data from a database and create an xml file via ssis. So far, I've created a script that can successfully create an xml file (using XmlTextWriter), listing all necessary data, however, they also want the schema listed within the file as well and I'm not sure how to do it.

Here's my current code to create my xml file (using a Script Component in my data flow):

StreamWriter sw;
XmlTextWriter xWriter;
String rowName;
String collectionName;
private int[] columnNames;

public override void PreExecute()
{


    rowName = "Responses";
    collectionName = "NewDataSet";
    String fileName = Variables.FullFileName;        
    xWriter = new XmlTextWriter(fileName, null);
    xWriter.WriteStartDocument();
    xWriter.WriteStartElement(collectionName);
}

public override void PostExecute()
{        

    xWriter.WriteEndElement();
    xWriter.WriteEndDocument();
    xWriter.Close();
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Type rowType = Row.GetType();
    PropertyInfo columnValue;

    xWriter.WriteStartElement(rowName);
    foreach (IDTSInputColumn100 column in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
    {
        columnValue = rowType.GetProperty(column.Name);
        xWriter.WriteStartElement(column.Name);

        Object value = columnValue.GetValue(Row, null);
        if(value != null)
        {
            xWriter.WriteValue(value.ToString());

        }

        xWriter.WriteEndElement();
    }      
}

How can I add schema info to this? I've seen tutorials for "WriteXmlSchema", but this seems to only work with a dataset (which I'm not using).

I really appreciate any help you can provide.

The schema is used to validate the XML. So, you must either have an existing schema, or you need to author one. Your customer is probably assuming that you're validating the XML against the schema and they want to see what the validation criteria is by examining the schema. So, you may want to re-sync with your customers on project expectations. — David, Oct 18 '12 at 19:39

score 1 · Accepted Answer · answered Oct 18 '12 at 20:46

If you just need to reference an existing schema within the xml document, it's just an attribute in the root element. See this tutorial re XML schemas. In the example on that page, to write the reference to the schema, after xWriter.WriteStartElement(collectionName); add

xWriter.WriteAttributeString("xsi:schemalocation", "http://www.w3schools.com note.xsd")

If you need to create the xsd schema document there are various ways to do this. I suggest googling "create xsd from ...".

SSIS: Add schema to xml file

1 Answers1