07-11-2017 04:59 AM
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">firstname.lastname@example.org</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.
07-11-2017 05:44 AM
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.
07-11-2017 06:17 AM
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...
07-11-2017 08:25 AM
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.
07-11-2017 08:45 AM
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.