Parsing XML via JSON to SP

(0) Share

Report

Posted on by polo1604

160

Hi Community,

I searched a lot but didn't succeed to find how to convert my XML files to my Sharepoint List.

First I convert to JSON, then parse the JSON output. The structure allows direct allocation to my sharepoint columns for most of the items, but the "property/value" should come to each SP column with the same name as the "property/key". I hope I could explain my issue.

Thanks for your help,

Paul

I have the same question (0)

All responses (10)

Answers (0)

KvB1 1,596 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

Hi,

Are these supposed to be in the same list item where the key indicates the column for that item, or are they different list items where you want to fill that specific column?

For example,

Was this reply helpful? Yes No
polo1604 160 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

Hi,

thanks for your reply. The columns to fill are following:

type:
source:
time:
mileage:
heading:
speed:
LACY:
LPOST:
LLAT:

this means I need to unnest the property values, and put them in the right column based on the property/key value.

Was this reply helpful? Yes No
KvB1 1,596 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

What kind of a type is the property?

If the position of those values is fixed within "property" I think you should be able to just grab them with the select action, and then use the output of that to fill your rows

Was this reply helpful? Yes No
polo1604 160 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

Hi,
I never did that with select, could you please show me how this should look like?

thanks a lot for your help!

Was this reply helpful? Yes No
KvB1 1,596 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

Could you post the schema of the parse JSON?

Was this reply helpful? Yes No
polo1604 160 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

here we are:

{
  "type": "object",
  "items": {
    "type": "object",
    "properties": {
      "type": {
        "type": "string"
      },
      "source": {
        "type": "string"
      },
      "time": {
        "type": "string"
      },
      "mileage": {
        "type": "string"
      },
      "heading": {
        "type": "string"
      },
      "speed": {
        "type": "string"
      },
      "property": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "key": {
              "type": "string"
            },
            "value": {
              "type": "string"
            }
          },
          "required": [
            "key",
            "value"
          ]
        }
      },
      "coordinate": {
        "type": "object",
        "properties": {
          "latitude": {
            "type": "string"
          },
          "longitude": {
            "type": "string"
          }
        }
      }
    },
    "required": [
      "type",
      "source",
      "time",
      "mileage",
      "heading",
      "speed",
      "property"
    ]
  }
}

Was this reply helpful? Yes No
KvB1 1,596 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

I am not very good with JSON 😞

But I think that you could create a select action, and use the parse JSON as input. If the objects in the property array are expected to be in the same order, you would then be able to define the key (such as LACY) during the select action, and give it a value based on the number of the object in the array.

For example, for LACY you could use a value like: ('property')[0]?['value']

Was this reply helpful? Yes No
polo1604 160 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

The objects in the property array are not always the same nor they are in the same order.

That's why I'm looking for a way to read the value out of property/key (such as LACY) to define where the value from Property/value has to be stored in.

something like this:

LACY: if(property/key='LACY', property/value)

Was this reply helpful? Yes No
polo1604 160 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

Hello Community,

nobody has an idea? There should be a solution but I will not find it alone...!

Was this reply helpful? Yes No
polo1604 160 on at

Like (0)

Report

Re: Parsing XML via JSON to SP

Hello,

I'm still struggling with my XML. Could anyone show how I could iterate trough all "property" nodes? Maybe this could help to analyze tha data.

Thanks for your help
Paul

Was this reply helpful? Yes No