The “ORDER” type in the following XML document will not be shown in the available external columns dialog in SSIS 2012.
<?xml version=”1.0″ encoding=”utf-8″?>
<ORDER>
<ID>1</ID>
<ORDERLINE>
<ID>1</ID>
<PRODUCTNAME>Test1</PRODUCTNAME>
</ORDERLINE>
<ORDERLINE>
<ID>2</ID>
<PRODUCTNAME>Test2</PRODUCTNAME>
</ORDERLINE>
</ORDER>
Result
If you want the ORDER type to show up, add a root element to the xml document:
<?xml version=”1.0″ encoding=”utf-8″?>
<ROOT>
<ORDER>
<ID>1</ID>
<ORDERLINE>
<ID>1</ID>
<PRODUCTNAME>Test1</PRODUCTNAME>
</ORDERLINE>
<ORDERLINE>
<ID>2</ID>
<PRODUCTNAME>Test2</PRODUCTNAME>
</ORDERLINE>
</ORDER>
</ROOT>
Now you will see both ORDER and ORDERLINE show up:
To add a root node to an XML document in SSIS you can use the following script code:
public void Main() { try { string selectedXmlFile = Dts.Variables[@"User::SelectedXmlFile"].Value.ToString(); string AlteredXmlFile = (String.Format("{0}x", selectedXmlFile)); Dts.Variables[@"User::AlteredXmlFile"].Value = selectedXmlFile; XmlDocument oldDoc = new XmlDocument(); oldDoc.Load(selectedXmlFile); XmlDocument newDoc = new XmlDocument(); XmlElement root = newDoc.CreateElement("ROOT"); newDoc.InsertAfter(root, null); XmlNode content = newDoc.ImportNode(oldDoc.ChildNodes[2], true); root.AppendChild(content); newDoc.Save(AlteredXmlFile); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception ex) { Dts.Variables[@"User::LogMessage"].Value = ex.ToString(); Dts.TaskResult = (int)ScriptResults.Failure; } }
Hi Roel,
Do we need to provide xml content or xml filepath in user variable?
Thanks,
Amit