asked
merge xml file
I have a folder of xml files that are all the same layout. I want to merge them into a single file. Any suggestion or widgets that can do that?
@dosberg How do you want the contents to look? Each of the xml files probably has the root node and a bunch of child nodes. Do you want the root node to be the same and the child nodes to be merged into a single file? If so then a small C# app could be the best option (has nothing to do with the SQL Server related questions though).
This has nothing to do with SQL Server, so the question should be asked on some other forum, but here is a small snippet which will do the trick. You can change the path to whatever it is in your case (create a console app to run the code): XmlDocument merged = new XmlDocument(); // name your root node as you see fit (named "root" in this snippet) merged.AppendChild(merged.CreateNode(XmlNodeType.Element, "root", null)); string[] files = Directory.GetFiles("C:\\Useless\\Temp\\xml"); string[] contents = new string[files.Length]; for (int i = 0; i < files.Length; i++) { using (XmlReader reader = XmlReader.Create(files[i])) { reader.MoveToContent(); contents[i] = reader.ReadInnerXml().Replace("\n", "\r\n"); reader.Close(); } // reader is disposed } merged.DocumentElement.InnerXml = string.Join("", contents); merged.Save("C:\\Useless\\Temp\\xml\\Merged.xml"); The above will require these directives: using System; using System.IO; using System.Xml; Oleg
If this is a repeating task, you can use SSIS to achieve this. depending on how many "outputs" in the xml file, this could take a couple of hours to setup but it would be worth it to have a scalable solution. If you want to do it with SSIS, create a FOR EACH Loop container and use the for each file enumerator. Then you can save the filename to a variable, then use that variable in a dataflow task to change the XML file connection. In the data flow task, save the data from the xml file to a table, then after the for each loop container, put in a another dataflow task that outputs the data from the table into a XML file, then you have a merged XML file.
Oleg, point well taken. The danger of posting as you rush out of the office. My goal is to merge and flatten the data so I can load it into sql. That is why I chose this forum. Thank you for the code snippet. Dan, thank you for the SSIS suggestion. David
Hi Dosberg, if you don't have to merge the files, then take out the last step; i.e. you don't need the last dataflow task to output the temp table data. hope it works out ok for you
@dosberg Hi David, Daniel's SSIS solution should work for you and scale well too, but depending on your xml shape, you can also do it with practically no setup by writing a small proc to do it with pure T-SQL in the form of insert into [the_table] select [the_fields] from [merged_xml_file]. If this sounds worthy of consideration, drop a line about your xml shape and I can write a sample proc.
