question

dosberg avatar image
dosberg 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?
xml
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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).
1 Like 1 ·
@Oleg - like, that's the answer...
0 Likes 0 ·
@Matt Whitfield Should I still add the C# snippet?
0 Likes 0 ·
Oleg avatar image
Oleg answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg, you never fail to deliver.
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dosberg avatar image
dosberg answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
@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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.