CommandReporter - How to specify the output format of reports

[iceman]

I have several reports set to output as XML (not XML with schema)

Running these by hand works as expected and an extenal program is able to parse the xml and process the xml data.

However, when running the same report via command reporter, as a scheduled job, with a line like

commandreporter.exe" /USER:"fred" /PASSWORD:"*****" /REPORT:"MyReport" /OUTPUT:"\MyFolder\Students.xml"

results in the xml being output with its schema.

 

This has several undesired effects.

• The xml contain loads of unexpected lines at the top;
  
• The output with schema contains extra fields I didn't ask for eg 'primary_id' as well as 'StudID'
  
• The field names in the xml with schema created by command reporter are different to those created when running the report by hand (and the hand run one contains different fieldnames to the sims fieldnames I asked for , but I have dealt with that issue!)
  

The last one, having different fieldnames, is very important as this obviously breaks the extenal program when it trys to process the data and doesn't get the fieldnames it expects.

 

Does anyone know how I can force commandreporter to output xml without a schema, the same as running the report manually does?

 

For info below is the output when run by hand (ie what I want) and below that is the output when run via CR, note the field names.

 

 
   99046
   007777
   X899123456789
   Bloggs
   Fred
   Bloggs
   Fred
   WPH
   M
   Woolf
   77
   2
   0
   80
   0
   Year 7
   F
   F
   F
   96.25
   Curriculum Year 7
 

 

 
   
     
       
         
           
             
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
               
             
           
         
       
     
   
 
 
   99046
   99046
   007777
   X899123456789
   Bloggs
   Fred
   Bloggs
   Fred
   WPH
   M
   Woolf
   77
   2
   0
   80
   0
   Year 7
   F
   F
   F
   96.25
   Curriculum Year 7
 

[LosOjos]

I'm afraid the only workaround for this I found was to produce the report both ways myself and account for that in code, i.e. match either element name as produced manually or by CommandReporter.

 

As for the schema, that really shouldn't be a problem - just ignore it if you don't need it. I don't know how you're parsing your XML, but any decent parser will separate schema from data.

 

If you absolutely must have an identical, schema-less format every time, perform an XSL transform on CommandReporter's output to ensure you get the expected format before it's passed to your program.

[iceman]

I'm reading the xml with a Delphi program.

The essential bit of that process is below (the a,b,c,d, is just to show the principle, the actual code is a bit more complex, dealing with generating sql from the data it gets from the xml)

 

How do I "perform an XSL transform on CommandReporter's output" and what does that do?

 

procedure TFrmImportSIMSdata.ImportStudents;
var
 xml:IXMLDocument;
 node:IXMLNode;
 NodesList : IXMLNodeList;
 i: Integer;
 a,b,c,d: string;
begin
XMLDocument1.FileName := DownloadFolderRoot + 'students.xml';
XMLDocument1.Active := true;
NodesList := XMLDocument1.DocumentElement.ChildNodes;
for i := 0  to NodesList.Count - 1 do
    begin
    node := NodesList.Get(i);
    a := Node.childnodes['ID'].text;
    b := Node.childnodes['AdmissionNumber'].text) ;
    c := Node.childnodes['UPN'].text);
    d := Node.childnodes['LegalSurname'].text);
             ...
             etc
             ...
   end;

[LosOjos]

XSL is a scripting/templating language with the specific purpose of processing XML documents i.e. taking XML as input and producing output in another format.

 

This is useful to you as you can take CR input and output XML formatted how you'd expect it.

 

NOTE: if your Delphi is just parsing XML to produce a SQL query, it'll likely be much quicker to bypass that step and instead write an XSLT to convert your XML to CSV and let SQL import the CSV

 

Looking at your snippets above, you could use XSL like this to ensure you always get the desired format:

 

 

You'll want to 'fortify' that XSL a bit to check nodes exist (SIMS doesn't include nodes with no data in its output) but that should give you the idea.

 

Depending on your OS, you'll want a way of "applying" that transform. As you'll be using Windows on your CR machine, you could use MS's mssxl tool: https://www.microsoft.com/en-gb/download/details.aspx?id=21714

Edited November 10, 2015 by LosOjos
fix formatting of code block

[iceman]

Thanks LosOjos,

Yes I am importing into a database using sql but into one that doesn't have a straightforward way of importing csv. So I simply generate the required INSERT sql in my program and then execute it. I guess if the XSLT is used to convert XML to csv one might as well just output the sims report as csv in the first place.

 

However, since researching this a bit I think I've decided to leave the reports in XML with schema (even changing the definition so that running by hand gives the same output as CR.) I managed to find good info on using the XML data Bindings wizard in Delphi that seems to take care of a lot of the complexity.

See Using the XML Data Binding Wizard - RAD Studio

Then reading in the required fields is as simple as one line and I was able to replace the code in my earlier posting with the very simple...

 

  var
      SIMSData : IXMLSuperStarReportType;  
      i : integer;
      s : string
 begin
 SIMSData := LoadSuperStarReport ();   //open xml as a IXMLSuperStarReportType
 for i := 0 to SIMSData.Record_.Count do
    begin
    s := SIMSData.record_[i].;
   //do something with s
    end;
 end;

 

The Binding wizard even takes care of mapping actual fieldnames in the xml file to an alias which is what you 'see' when you read the file. That saved me changing all the code that uses the filenames present in the non schema XML.

 

In case anyone else wants to do this, below are the comments that I put in my delphi program explaining to myself what to do.

 

//////////////////////////////////////////////////////////////////
///  The xml file import uses units created by the XML Bindings wizard
///  created by chosing File | New | Other | XML | XML Data Binding
///  once for each of the xml files we want to import and selecting that file in the first screen
///  (The units made by the XML binding wizard are the same as the XML filename so I 
///  renamed the Units to XML_Binding_ before saving so I could recognise them
///  as different from other unints called 'students' or 'Contacts')
///
///  Then to process the xml file use the following
///
/// ........................................................
///  uses  ;
///
///  var
///       SIMSData : IXMLSuperStarReportType;  //in  unit XMLBinding_students;
///       i : integer;
///       s : string
///  begin
///  SIMSData := LoadSuperStarReport (');   //open xml as a IXMLSuperStarReportType
///  for i := 0 to SIMSData.Record_.Count do
///     begin
///     s := SIMSData.record_[i].;
///     end;
/// .........................................................
///
///  The data comes out as the type defined during completing the data binding wizard
///  and can be any data type known to Delphi
//////////////////////////////////////////////////////////////////////////////////////

 

The odd behaviour of SIMS and CR still remains but this makes a simple job of getting round the issue.

Edited November 10, 2015 by iceman

[LosOjos]

Glad you've found a way that works for you

 

Just want to clarify this point:

 

I guess if the XSLT is used to convert XML to csv one might as well just output the sims report as csv in the first place.

 

XSLT isn't just an XML to CSV converter, it's a quick way of parsing XML to create any output. The example I posted above for instance takes CR's version of the XML and converts it in to the "manual run" version you posted above.

 

I mentioned CSV because in SQL Server for instance, CSV imports are extremely quick. I reduced the update time of one of my systems drastically (we're talking from a couple of hours to a couple of minutes) when I discovered XSLT, as it meant rather than writing a horrible SQL XML parser (very slow!), I could use XSLT to convert the XML to CSV first, then let SQL import the CSV (very fast!).

 

It was something of a fringe case though - I was dealing with SIMS photos, which SIMS won't export in anything but XML format

 

TL;DR - if you do a lot of XML manipulation, you'd be well served to learn XSL[T]