How to merge many XML files into one?

[mrindia]

Hi: I got a small project to combine many XML files into one and convert the combined XML file in Excel using AppleScript. My XML files look like this:

<?xml version="1.0" encoding="UTF-8"?> thorsten.jock@medienpartner-int.com B3_IM09MBDUF 2 3 CEST 2013 7 15 11

and like this…

<?xml version="1.0" encoding="UTF-8"?> frank.falk@medienpartner-int.com P1_FR1330G006007_Kate_van der Vaart 2 1 CEST 2013 7 12 16

I get many XML files like this. And I want them to be combined and shown like this:

<?xml version="1.0" encoding="UTF-8"?> thorsten.jock@medienpartner-int.com B3_IM09MBDUF 2 3 CEST 2013 7 15 11 frank.falk@medienpartner-int.com P1_FR1330G006007_Kate_van der Vaart 2 1 CEST 2013 7 12 16

And finally the combined XML file converts in Excel sheet with column headings “Job ID”, “From”, “Job Name” and so on…

Or any other best way to get the same result…

Thanks

[StefanK]

Hi,

XML Suite of System Events can parse XML files but is not able to create new elements.
And creating, tagging, inserting the elements manually with string manipulation is a pain.

This script uses (and requires) XMLLib osax by Satimage.
It assumes that there is a “main file” with the appropriate root element and at least one child element.
You will be prompted to choose the main file and the files to be added.
The script assumes also that the files to be added have only one element without the job tag
The value of the id tag is set automatically by gathering and incrementing the number of elements

property rootElementTagName : "MetadataObject"

-- prompt to choose main file
set chosenFile to choose file of type {"xml"} with prompt "Choose Main File"
if chosenFile is false then return
set mainFilePath to chosenFile as text

-- open the main file and count elements
set mainRef to XMLOpen file mainFilePath
set mainRoot to XMLRoot mainRef
set numberOfElements to XMLCountElement mainRoot

-- prompt to choose one or multiple files to be added to the root element of the main file
set filesToAdd to choose file of type {"xml"} with prompt "Choose Files to be appended to Main File" with multiple selections allowed
if filesToAdd is not false then
	repeat with aFileToAdd in filesToAdd
		
		-- open new file
		set aFileToAddRef to XMLOpen aFileToAdd
		set aFileToAddRoot to XMLRoot aFileToAddRef
		set rootTag to XMLTagName aFileToAddRoot
		-- check compatibility of the root element tag
		if rootTag is rootElementTagName then
			set numberOfElements to numberOfElements + 1
			-- create new child with <job> tag
			set newJobChild to XMLNewChild "<job/>" at mainRoot
			-- add <id> attribute
			XMLSetAttribute newJobChild name "id" to numberOfElements
			set childCount to XMLCountElement aFileToAddRoot
			-- copy all tags into new created <job> child
			repeat with aChild from 1 to childCount
				XMLNewChild (XMLChild aFileToAddRoot index aChild) at newJobChild
			end repeat
			
		end if
		-- close file reference of new file
		XMLClose aFileToAddRef
	end repeat
	-- save main file (with overwrite)
	XMLSave mainRef in file mainFilePath
end if
-- close file reference of main file
XMLClose mainRef

The script does not include the Excel stuff

[McUsrII]

Hello.

You can use the xpath command below, to extract the values from the xml documents, you won’t get the job-Id out, so you’ll have recreate that element.

xpath test.xml '//job/descendant-or-self::*/text()' 2>/dev/null

Here is an example of the output, which is what I have dreamt up as output from Stefan’s script above.

[code] thorsten.jock@medienpartner-int.com
B3_IM09MBDUF
2
3
CEST
2013
7
15
11

frank.falk@medienpartner-int.com
P1_FR1330G006007_Kate_van der Vaart
2
1
CEST
2013
7
12
16[/code]