Xml Records - how to fix record list

Xml Records - how to fix record list

I'm trying to use XmlRecords to process the attached XML file and I'm having a problem. Can someone please tell me how to fix the record structure?

I tried below but get an errror: The current node (Objs) does not match the record definition (ActiveSync)
Hi Gerry,

The XML you have here wasn't exported from OutSystems. As so the structure doesn't match to what you have here. I'd suggest two options:
  1. Use the XML extension and implement the logic in Service Studio to map this XML with the structure you defined (I really recommend this one as it is probably easier to implement)
  2. Use XmlRcords to export a record of the structure you defined and transform your source XML (manually or thru XSLT) to that definition.
Hope this helps you.

I think that my problem is that elements have strange prefixes, like:
  <<S N="Identity">namprd05.prod.outlook.com/Microsoft Exchange Hosted Organizations/cvcmail.onmicrosoft.com/JDOLAN/ExchangeActiveSyncDevices/iPad§ApplDMPHGJMWDVGJ</S>
  <<DT N="FirstSyncTime">2013-05-28T18:43:52.6368735Z</DT>
  <<DT N="LastSyncAttemptTime">2013-06-05T17:08:10.3654066Z</DT>
  <<DT N="LastSuccessSync">2013-06-05T17:08:10.5214076Z</DT>
  <<S N="DeviceType">iPad</S>
  <<S N="DeviceModel">iPad3C3</S>
  <<S N="DeviceFriendlyName">Black iPad</S>
  <<S N="DeviceOS">iOS 6.0.1 10A523</S>
  <<S N="Status">DeviceOk</S>
  <<S N="DeviceActiveSyncVersion">14.1</S>
  <<I32 N="NumberOfFoldersSynced">5</I32>

Does anyone know how to deal with that?
I now have what I think is better formatted XML, but I still cannot get this to work.

Could someone please look at the example oml and tell me how to fix it? Thanks!!!
Hi Gerry,

You need to do several things here. The problem is that the XmlRecords maps the elements into the structure via the name of the element, not looking at the value of a particular attribute. So What I would suggest:

1. Remove the xmlns from the xml, by using something like 
xml = Replace(xml, "xmlns=""http://schemas.microsoft.com/powershell/2004/04""", "")
which will transform the line
<Objs Version="" xmlns="http://schemas.microsoft.com/powershell/2004/04">
<Objs Version="">

2. Apply a xslt to the xml (using the XSL_Transform action in the Xml extension) to transform the "N" attributes into its own element. I think a xsl similar to the following should do it:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" encoding="UTF-8"   indent="yes" />
<xsl:template match="@* | node()">
            <xsl:apply-templates select="@* | node()" />
 <xsl:template match="*[@N]">
   <xsl:element name="{@N}"><xsl:apply-templates/></xsl:element>

which will make your xml look something like:
         <Identity xmlns="">namprd05.prod.outlook.com/Microsoft Exchange Hosted Organizations/cvcmail.onmicrosoft.com/JDOLAN/ExchangeActiveSyncDevices/iPad??ApplDMPHGJMWDVGJ</Identity>
         <FirstSyncTime xmlns="">2013-05-28T18:43:52.6368735Z</FirstSyncTime>
         <LastSyncAttemptTime xmlns="">2013-06-05T17:08:10.3654066Z</LastSyncAttemptTime>
         <LastSuccessSync xmlns="">2013-06-05T17:08:10.5214076Z</LastSuccessSync>
         <DeviceType xmlns="">iPad</DeviceType>
         <DeviceModel xmlns="">iPad3C3</DeviceModel>

3. Now create in Service studio a set of hierarchical structures that EXACTLY resembles the structure of the XML

Objs (RecordList)
  - Obj (Record)
     -RefId (Integer)
     - MS (Record)
         - Identity (Text)
        - FirstSyncTime (DateTime)
        - LastSyncAttemptTime (DateTime)
        - LastSuccessSync (DateTime)
        - DeviceType (Text)
        - DeviceModel (Text)
        - DeviceFriendlyName (Text)

That should do it. I can make you an example... but only in version 8

Thanks Gonçalo. The XSLT is not working. When I apply it, it the XML does not change. Could you please help me get that corrected? Thanks. (I don't have v8 yet)
I created the XSLT considering your first xml...
You just need to tweak it a little bit. Check the sections in Bold (changed @N to @Name):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
 <xsl:template match="@* | node()">
            <xsl:apply-templates select="@* | node()" />
 <xsl:template match="*[@Name]">
   <xsl:element name="{@Name}"><xsl:apply-templates/></xsl:element>

After that the xslt works.
Then you have another problem. The datetime values are not in the standard XML format, so the XmlToRecordlist issues an error. I changed the FirstSyncTime, FirstSyncTime and LastSuccessSync to be of type Text instead of datetime and you already get the results.
If you can get the xml to have the standard xml datetime format (e.g.: 2002-05-30T09:00:00) it should work without changing the structure.

Thanks so much for the help. Everything is working now.