|
|
Rank: Newbie
Joined: 11/18/2016
Posts: 3
Location: UK
|
Hi,
I have recently been tasked with taking a rather large XML file containing thousands of lines of data, and splitting relevant data based on <Location> element.
Below I have attached a sample XML file simply containing two companies both of which have three accounts each.
As an example, I have been tasked with exporting data that is relevant to the same country, for example UK.
<Details>
<AccountDetails>
<CompanyInformation>
<Location>UK</Location>
<Name>Building Limited</Name>
<Address>Builders Lane Road, UK</Address>
</CompanyInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>11111</AccountNumber>
<Name>John Smith</Name>
<Address>Green Street, UK</Address>
</AccountInformation>
<AccountInformation>
<Location>USA</Location>
<AccountNumber>22222</AccountNumber>
<Name>George Graham</Name>
<Address>New York, USA</Address>
</AccountInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>33333</AccountNumber>
<Name>Andrea Jones</Name>
<Address>London, UK</Address>
</AccountInformation>
</AccountDetails
</AccountDetails>
<CompanyInformation>
<Location>AUS</Location>
<Name>Sydney Contractors</Name>
<Address>Pitt Street, Sydney, Australia</Address>
</CompanyInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>999999</AccountNumber>
<Name>Joe Bloggs</Name>
<Address>Luton, UK</Address>
</AccountInformation>
<AccountInformation>
<Location>USA</Location>
<AccountNumber>456789</AccountNumber>
<Name>Joey Andrews</Name>
<Address>Las Vegas, USA</Address>
</AccountInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>785452</AccountNumber>
<Name>Fred Vincent</Name>
<Address>Princess Street, UK</Address>
</AccountInformation>
</AccountDetails>
</Details>
The only caveat is that should an account (<AccountInformation>) exist for the UK, and the company (<CompanyInformation>) is of a different location, the <CompanyInformation> must be included regardless of its location. So, if I was to generate it should look something like this:-
<Details>
<AccountDetails>
<CompanyInformation>
<Location>UK</Location>
<Name>Building Limited</Name>
<Address>Builders Lane Road, UK</Address>
</CompanyInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>11111</AccountNumber>
<Name>John Smith</Name>
<Address>Green Street, UK</Address>
</AccountInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>33333</AccountNumber>
<Name>Andrea Jones</Name>
<Address>London, UK</Address>
</AccountInformation>
</AccountDetails
</AccountDetails>
<CompanyInformation>
<Location>AUS</Location>
<Name>Sydney Contractors</Name>
<Address>Pitt Street, Sydney, Australia</Address>
</CompanyInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>999999</AccountNumber>
<Name>Joe Bloggs</Name>
<Address>Luton, UK</Address>
</AccountInformation>
<AccountInformation>
<Location>UK</Location>
<AccountNumber>785452</AccountNumber>
<Name>Fred Vincent</Name>
<Address>Princess Street, UK</Address>
</AccountInformation>
</AccountDetails>
</Details>
So, my question is....what is the easiest utility to perform such a task? I am currently using XMLSpy 2016, is this enough? I am about to embark on scripting/macros to see if this is the correct way, or are other Altova tools such as RaptorXML better for this type of thing?
Any help will be greatly appreciated.....
many thanks
|
|
Rank: Newbie
Joined: 10/28/2002
Posts: 1,283
Location: AT
|
Hi,
.
use Mapforce and in your mapping insert a filter, see attached example
File Attachment(s):
 test.zip (3kb) downloaded 912 time(s).
|
|
Rank: Newbie
Joined: 11/18/2016
Posts: 3
Location: UK
|
island wrote:Hi,
.
use Mapforce and in your mapping insert a filter, see attached example
Thank you!!
I have downloaded MapForce and slowly beginning to map out what I need.
One problem I don't seem to rectify is how to target a particular element more than one, without having to duplicate it.
For example, If I throw in to the mix an additional Location child field, further down the chain, so I add an additional equal a = b, link that to another filter, but how can I connect that so I only have one company data sat above all the accounts?
It seems that once I add more complexity (child tags) to my XML, it seems much more difficult to only have one company data, and I cant quite figure out what I need?
I have tried using the Logical-or, so each of my location elements, if one of them returns true then I display company data, but that doesn't seem to work :(
|
|
Rank: Newbie
Joined: 11/18/2016
Posts: 3
Location: UK
|
Hi,
Maybe this will help visualise my problem....
What I am trying to do here is.... if Location fields under 12 or 19 are "IOM" then reporting the Company Information under number 3 just the once.
If I do the following, nothing displays under "Output"..... I know I can duplicate number 3, but that means the Company Information under number 3 appears multiple times (and as a result fails my schema validation).
What exactly am I missing....something very simple I hope (only starting looking at MapForce yesterday!)
Any help will be greatly appreciated, thanks
iTz
iTz attached the following image(s):
|
|
Rank: Newbie
Joined: 10/28/2002
Posts: 1,283
Location: AT
|
It looks like you may have to use variables as you are dealing with elements from different contexts. I would suggest attaching your mapping file (.mfd), the source xsd and source xml, and target xsd so that it is clear.
|
|
|
guest |
