Convert XML string to infotable

[Spiff]

Hi! I used Resources["ContentLoaderFunctions"].PostText(params); to get a website containing XML data. The problem is that the url is not leading to a raw XML file but has a html frame around it. That's why I didn't use GetXML. I fetched the complete webpage and used some JavaScript to just have the XML string as result. It looks like:

<?xml version="1.0" encoding="utf-8"?> <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"> <!-- <dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd" > --> <metadata> <item name="item1" type="xs:string" length="20"/> <item name="item2" type="xs:string" length="18"/> </metadata> <data> <row> <value>123456</value> <value>7890</value> </row> <row> <value>abcd</value> <value>efgh</value> </row> </data> </dataset>

How can I convert this XML string to something that can be used in Thingworx? Do I need to convert it to a XML object first? Is it possible to fill an infotable directly?

 Thanks!
David

[CarlesColl · Accepted solution]

TW Server Side Javascript it's E4X you can work with the XML nodes out of the box in order to read and generate an infotable.

[Spiff]

Thanks Carles, with the hint to E4X I managed to convert the string to a XML object with

var xmldata = '<dataset><metadata> <item name="item1" type="xs:string" length="20"/> <item name="item2" type="xs:string" length="18"/> </metadata> <data> <row> <value>123456</value> <value>7890</value> </row> <row> <value>abcd</value> <value>efgh</value> </row> </data> </dataset>'
var xml = new XML(xmldata);

The processing leads to another problem though. As you can see a bit clearer here:

<metadata>
   <item name="item1" type="xs:string" length="20"/>
   <item name="item2" type="xs:string" length="18"/>
</metadata>
<data>
   <row>
      <value>123456</value>
      <value>7890</value>
   </row>
   <row>
      <value>abcd</value>
      <value>efgh</value>
   </row>
</data>

 the data in the <row> nodes always starts with <value>. The 1st <value> is the row entry of the first coloumn <metadata><item>. When I try to fill the infotable with "for each (var tag in xxml.row)" the <value> column will be filled out with several written out "<value>"-entries.

I didn't have another idea as just to prerocess the xml and replace all <value> with <value1> to <value15> in my case to have a seperation.

Is there any better way to do this directly? The XML structure seems to be a bit special here.

Thanks!
David