Import complex XML

[fre]

Dear Community

I need to import XML-structured data.

This is how the code looks like:

<gebeurtenisDatablok
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:type="geb:GebeurtenisDatablok"
        xmlns:geb="urn:be:vlaanderen:omgeving:datablok:gebeurtenis:GEB_WIJZIGEN_CONTACT_001:001">
    <persoon xmlns:dossier="urn:be:vlaanderen:omgeving:dossier:v2" xsi:type="dossier:RechtsPersoon">
		<persoonId databron="KBO" externIdWaarde="0000000000" />
		<hoedanigheid>
			<naam>AANVRAGER</naam>
			<contact>
				<postadres gebruik="POA">
					<subadres></subadres>
					<straat>
						<naam>no street</naam>
					</straat>
					<huisnummer>100</huisnummer>
                    <busnummer></busnummer>
					<postcode>1000</postcode>
					<gemeente>
						<naam>Brussels</naam>
					</gemeente>
					<regio>Vlaams Gewest</regio>
					<land>Country</land>
				</postadres>
				<medium aard="TEL">000000000</medium>
				<medium aard="EMAIL">first.last@server.com</medium>
				<medium aard="FAX"></medium>
				<medium aard="GSM"></medium>
			</contact>
		</hoedanigheid>
		<maatschappelijkeNaam>name ZZZ</maatschappelijkeNaam>
		<commercieleNaam>name XXX</commercieleNaam>
		<rechtsvorm>VZW</rechtsvorm>
    </persoon>
</gebeurtenisDatablok>

So far, I've tried 2 things:

- import via libname xxx xmlv2

- using XML Mapper

In both cases, SAS / XML mapper reports that it can not handle the XML.

Does anyone know how to import this data into a regular SAS dataset or what else I could try?

Any help or advice is highly appreciated.

Kind regards

[RW9]

Well, one way, and I don't suggest it as the best, open the file in Excel, this will create an Excel table, then save as CSV.  Can't see why the mapper wouldn't handle it though, its a pretty simple table from what you show there.  

[fre]

Hi RW9,

Thank you for your reply.

Seems like Excel cannot handle this xml.

+ I guess XML mapper is limited when it comes to more complex XML.

I'm wondering if SAS can handle such format. 

I don't know any other alternatives at the moment.

If anyone knows any alternative, feel free to reply...

[RW9]

That XML you posted in your first post opened in Excel no problem for me.  

[fre]

Thank you for clearing that out. 

I was using the import data in Excel (that didn't work), but indeed it works fine when you just use 'open file'.

Excel does a pretty good job, it's a pitty that SAS cannot do this.

For my purposes, I have 300 such XML files, all with different schemes.

So manually open each file in excel + convert to csv is not an option.

I'll have to look further to find something that can import all those files automatically.

Maybe VBA or something like that.

[RW9]

Yes, just be careful as Excel likes to be "helpful"!  You could automate it by using VBA in Excel to load each file and save to CSV.  Seems a bit of a faff.  As mentioned XMLMapper should be able to deal with that as its not that complicated, maybe check with support as to why it says that, maybe you just need to stip something out.

When dealing with XML, you may be better off having a specific XLM tool the purpose.