373251 ; 10307107
373062 ; 10304708
10304135 <-- here is the idpurchaseorder missing from the first column but it does have both products listed
What i expect would be the following result from the testdata (i can add the other needed data myself if needed) above:
373251;10307107
373062;10304708
373062;10304135
Does anyone know the correct way of getting the data requested? If it is a other way then XMLStarlet it is fine although that is the easiest so far for me.
some additional bits of Knowledge about XPath will lead you forward towards your solution. Especially try to get information about how to figure out "parent nodes" of your target nodes.
One possible algorithm is to not iterate over the different orders(data), but iterate over the different products(products) and get a parent node(idpurchaseorder) from there.
Regards,
stomp.
P. S.: @Mod or @SDohmen: please fix the typo in the xml above: <name>Productname/name>
P. P. S.: I did not know the solution before the question was asked, and - according to Neos request - I will not take the fun and chance to grow from the poster to figuring it out for him-/herself. (The solution is about the same in size as the current shown attempts).
P. P. P. S: +1 Point for SDohmen using a suitable tool for the task.
SDohmen, I don't generally thank people for posting questions.
However you've posted good example data and a clear and concise question with a reasonable attempt at solving it yourself. Also the tool your using is the right one for the job, so many XML parsing questions posted here want to use sed / grep / awk which will usually fail given a different XML layout.
Interesting to note that if you start from the parent level, which would have been my first inclination, you only get the first product under that parent:
Sorry for the late reply. It took a while and help from some friends who have more knowledge also about this subject. He told me the same thing and gave me a short example of what is possible. With that example i was able to product the line below:
I was not aware that you could "backtrack" the tree and get the main info added to the product info. It most likely isn't the cleanest solution but at least it works great.
Thank you for this. I always try my best to get as much as possible done myself. Only if i am really stuck i will post the question and even that is most likely days after i got stuck. I find it important to try and test myself although sometimes that is not possible. For each problem it is of course searching for the best solution and even though i dont fully understand xmlstarlet it gave me the best results for all the scripts i have used it on. I seen several attempts with sed etc but they where so confusing or gave me weird outputs (Its like opening a door with a hammer instead of the key ;)) that i wanted to use a program that is specifically designed for this and that was/is xmlstarlet.
I do have to add though that i cheated a bit as i got json data before the xml and i transformed it to the xml to actually get the proper data for it. Since i do lack the knowledge to get all json data (although i did try it with jq first), i decided to look for the transformation (Decoding JSON to a PHP Array and then converting it to XML. . GitHub) and then get all data with xmlstarlet. This last part was almost correct then except for the backtracking that i did not know.
nice to hear that you worked your way through and thanks for giving feedback.
Some comments:
That's why I pointed out XPath for you twice and presented you links for a good and short tutorial. Read the great manuals out there! Read it and you will be lesser dependant on others.
I would suggest to avoid that. With this step, the complexity of the task had risen for no good reason. I understand why you did it(just to get the task done - did that myself in the past), but I recommend to better invest the time to get the data out of the original format. That's most likely possible with jq - even if it needs the work to get familiar with jq. Otherwise you carry the increased complexity, increased error possibility, increased resource footprint with that task from now on.
An alternative to jq may be a small php script, that generates the csv
(...and sometimes a quick and dirty hack is just fine too)
I did find those links you gave me but they where still a bit confusing and i would say limited in what i needed. Too be honest i did not have that much time either as i spend already almost a week on this with xmlstarlet alone.
As you mention this was a cheat but transforming the json to xml but this was also because i am severly limited in time and knowledge of jq. I actually did try a couple days and then i gave up because of the time constraints. Seeing your code however makes it look quite easy and even less fault sensitive. When i have some time i will actually try it out and let you know here about the (hopefully) success of the jq way.
I just tested jq but something seems not to work. I edited your piece of code to work with the json string:
Your json is different from mine. As the error message states numbers and strings cannot be added. You have to convert the number to a string before concatenating both: