How to convert xml data to json?

[devalvelociter]

Hi everyone,
I have XML data as shown below:

<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000405010255</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000405010310</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000405009777</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000401109537</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000401113460</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000401116861</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">001805015288</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">628801537715</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000319000184</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000401193586</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000405111139</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000405501186</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000760004994</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000805015689</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">000813001320</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">001105024982</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">001801080623</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">001810042371</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">001825075268</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">004214109637</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">004214109638</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">010310000278</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">032501004151</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">044205008102</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">105701000275</AccNum>
<AccNum DT="alphabetnumericspecialspaces" IND="125" LSP="2" MNL="1" MXL="19" PL="" PRE="optional" RLI="3">630010075140</AccNum>

and i want to convert the above data in json format as shown below:

{
  "AccNum": [
    405010255,
    405010310,
    405009777,
    401109537,
    401113460,
    401116861,
    1805015288,
    628801537715,
    319000184,
    401193586,
    405111139,
    405501186,
    760004994,
    805015689,
    813001320,
    1105024982,
    1801080623,
    1810042371,
    1825075268,
    4214109637,
    4214109638,
    10310000278,
    32501004151,
    44205008102,
    105701000275,
    630010075140
  ]
}

how to convert this data in json format.

[#2 · Accepted solution]

I would suggest converting to json using XML to json policy. Once this is in json, you can use a javascript policy to extract data and put into a json array.

[karthikprabhu77]

Did u try XMLtoJSON policy ? There is a sample example you can refer. You can achieve above scenario with minimal effort by using XSLT policy and XMLtoJSON.

[devalvelociter]

I fetched the AccNum using Extract Variable and after that i used XMLtoJSON policy for convert the data but I'm getting Invalid source type. i checked the source type also but still getting same response.

[karthikprabhu77]

Could you please post the extract variable policy code here. I will examine and suggest the changes.

The above XML doesnt have a root element, could you please check that as well.

[#6 · Accepted solution]

I would suggest converting to json using XML to json policy. Once this is in json, you can use a javascript policy to extract data and put into a json array.

[dchiesa1 · Accepted solution]

"Invalid source type" could mean

• You are not providing a Message as Source in the XMLToJSON policy. "request" and "response" are both message types. If you have the XML just as text stored in a context variable, I think that won't work. You need to create a message to hold the XML.
• You ARE using a message object to hold the XMl, but the Content-Type header on the message is neither application/xml nor text/xml.

Try this for a working example:

https://community.apigee.com/answers/91041/view.html

[dknezic]

Doesn't appear to be valid XML, there should be a single root element containing this data. I wouldn't expect any out of the box policies to work if it's not valid XML.