0

In our environment we use MS SQL w/ stored procedures. In those procedures we return our results as XML and when access the data as we need to.

I'm introducing some charts into our tools which are 3rd party and require specific formats in order to operate and I am running into an issue.

In this screenshot, you are able to see what the structure should look like which I can get to work with the plugin just fine. The issue is with how SimpleXML handles single result sets.

enter image description here

As you can see in the image below, with one result item, it is no longer formatted as an array. The problem being is that the plugin expects to find the data in the format in the first example, but when there is only one value, it doesn't store it as an array.

enter image description here

As you can see from this image, the dataset for the escalationTypes is in the array format where the one below, submittedByDepartment is not. enter image description here

I am trying to find out if there is something I can do to fix the root of this problem, with SimpleXML. Is this a common issue found with SimpleXML with a workaround?

UPDATE

Here is a sample of the XML Object I am working with: http://pastebin.com/uPh0m3qX

SBB
  • 8,560
  • 30
  • 108
  • 223
  • This question would be easier to read (and easier to include details in an answer) if you included some example data as text, not just screenshots. – IMSoP Apr 22 '15 at 22:15
  • [link](http://stackoverflow.com/a/6167346/4815044) this answer might help. – Bob Nocraz Apr 22 '15 at 22:23
  • Thanks @BobNocraz - I added in the function but it didn't seem to alter the array at all – SBB Apr 22 '15 at 22:29
  • @BobNocraz The answers on that post mostly use conversion to JSON as a hack to get a PHP array. In this question, JSON is already the target, so going via JSON isn't going to help. – IMSoP Apr 22 '15 at 22:33
  • Read the comments on that answer, they seem to provide further insight. – Bob Nocraz Apr 22 '15 at 22:35
  • @IMSoP not the answer I linked, and one of the comments links to a viable solution that someone posted on php.net – Bob Nocraz Apr 22 '15 at 22:37
  • @BobNocraz Ah, yes, I scrolled too quickly and misunderstood the discussion. I'm not sure without trying, but suspect this wouldn't end up in quite the desired structure. – IMSoP Apr 22 '15 at 22:47
  • don't paste print_r only, more interesting the XML itself. You want to convert XML to JSON not print_r to JSON :) - And for detailed discussion on how to convert XML via SimpleXML to JSON I have put a three part series on my blog that starts with this article: [SimpleXML and JSON Encode in PHP – Part I](https://hakre.wordpress.com/2013/07/09/simplexml-and-json-encode-in-php-part-i/) – hakre Apr 23 '15 at 05:47

2 Answers2

1

I'm not 100% clear what your required structure is, but in general, it's not a good idea to jump straight from XML to JSON or vice versa. Both can represent data in various ways, and what you really want is to extract data from one and turn it into the other.

Note that this is what SimpleXML is designed to help you with - it never contains any arrays, but it gives you an API which helps you extract the data you need.

If I understand correctly, you want to build an array from each of the dataset elements, and put those into your JSON, so you'd want something like this:

foreach ( $xml->children() as $item_name => $item ) {
      foreach ( $item->dataset as $dataset ) {
             $json[$item_name]['dataset'] = (array)$dataset->attributes();
       }
}

Note that neither of those loops will behave differently if there is only one item to loop over. SimpleXML decides whether to behave like an array or an object based on how you use it, not based on what the XML looks like.

Note that while you can build a more general XML to array (or XML to JSON) function, getting it to always give the desired output from any input will probably take more time, and lead to harder-to-debug code, than writing specific code like the above.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • Thanks for the answer @IMSoP - All I am given to work with is the initial XML string returned from the query. With that `SimpleXML` I can do with it as I wish but nothing before its actually created. What happens is a user selects filter options in the UI. It then runs the query on those options and returns the results which are json_encoded directly from the XML and written to a file. That file is then read by the multiple charts on the page. – SBB Apr 22 '15 at 22:40
  • @SBB I'm not sure how your comment relates to my answer. The example approach I've given doesn't need to do anything before the SimpleXML object is created, it just uses the methods of that object to build an array structure of your choice, which you can pass to json_encode for use by the widgets. Let me know if something's unclear. – IMSoP Apr 22 '15 at 22:50
  • I guess I'm a little unclear of how to implement this. I changed `$xml` to `$data` as that is what the result of the query is stored in but I get nothing returned when I try and dump `$json`. I actually got it to print out the data but it seems like the inner for each loop might need some work - http://oi58.tinypic.com/34zf6yr.jpg – SBB Apr 22 '15 at 22:53
  • @SBB As I mentioned above, with only screenshots to go on, and no samples of the actual XML and JSON, it's hard to give examples that exactly match your case. You might want to have a look at the basic usage examples in the manual to understand what's going on: http://php.net/manual/en/simplexml.examples-basic.php And it's best to build your logic looking at the XML itself, as print_r with SimpleXML can be a bit misleading sometimes. – IMSoP Apr 22 '15 at 23:00
  • I added a sample of the data that is being returned to me (http://pastebin.com/uPh0m3qX). In the example, the `escalationTypes` works just fine because there is more than one result so it creates the array. However when there is only the one record as you can see in the others, its a direct child of the `dataset` instead of inside `0` – SBB Apr 22 '15 at 23:07
  • 1
    @SBB No, that's not the XML, that's the result of print_r. I can't use that to create example code. Also, and this is key to understanding SimpleXML, *there is no array*; the print_r has to show you that there are multiple nodes with the same name, so it lays it out like that, but if you use a loop like I've done in my example, it will work just as well with one child as with many. Again, have a look at the examples in the manual. – IMSoP Apr 22 '15 at 23:11
0

you can declare the object as an array by adding (array) before referencing the variable. (array)$wasobject

Max D
  • 815
  • 7
  • 10
  • I am generating this array from the results of the database query. All that happens is `$this->results = new SimpleXMLElement($xml);` so I dont have access to the structure it self to be able to add that in front of the ones I need. – SBB Apr 22 '15 at 22:15