Solved

Delphi, ReportMan, MyBase XML Format Help

Posted on 2007-04-02
15
1,072 Views
Last Modified: 2013-11-23
I'm looking for information on the MyBase XML format.

I'm trying to use ReportMan (reportman.sourceforge.net) to read from am XML file that my software created. I cannot seem to find any information on what the XML schema should look like for compatible XML files. Since I won't be creating these files in Delphi I need a pretty descriptive spec on what needs to go where!

Thanks for any help!
0
Comment
Question by:mitchvincent
  • 10
  • 5
15 Comments
 
LVL 26

Accepted Solution

by:
EddieShipman earned 500 total points
ID: 18853045
Did you see the explaination of the ClientDataset XML here:
http://bdn1.borland.com/article/borcon/files/2106/paper/2106.html

I have written a component to convert any dataset descendant to XML and you can
modify how the XML is to be formatted. It also can build a DTD but I need to modify it
to build the METADATA.
0
 

Author Comment

by:mitchvincent
ID: 18853770
Where can I find information on your component?
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18853808
It is posted on Delphi pages here:
http://www.delphipages.com/result.cfm?ID=3641
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18853815
I have intended to modify it to use MSXML but haven't gotten around to it. Some of the
functionality listed may not be complete, too.
0
 

Author Comment

by:mitchvincent
ID: 18857566
Thanks Eddie!

The only thing I don't see how to do is inner-structures like this
(this is actual XML generated by my software now) :

<?xml version="1.0" encoding="ISO-8859-1"?>
<Invoice>
<Status>Pending</Status>
<Customer_Contact>Bob Smith</Customer_Contact>
<Customer_Phone1>111-222-3333</Customer_Phone1>
<Customer_Phone2></Customer_Phone2>
<Customer_Fax></Customer_Fax>
<Customer_Email>sales@...</Customer_Email>
<Line>
<Quantity>5</Quantity>
<Item>SKU1 - Demo Item1 - Description of demo item 1
</Item>
<Price>$10.00</Price>
<Line_Total>$50.00</Line_Total>
</Line>
<Line>
<Quantity>1.5</Quantity>
<Item>Labor - Computer repair labor</Item>
<Price>$35.00</Price>
<Line_Total>$52.50</Line_Total>
</Line>
<Payment>
<Payment_Type>Check</Payment_Type>
<Payment_Date>10-08-2004</Payment_Date>
<Payment_Amount>$1.00</Payment_Amount>
<Payment_Applied>$1.00</Payment_Applied>
<Check_Number></Check_Number>
<CC_Number>****************</CC_Number>
<Payment>
<Payment_Type>CC</Payment_Type>
<Payment_Date>10-12-2004</Payment_Date>
<Payment_Amount>$2.00</Payment_Amount>
<Payment_Applied>$2.00</Payment_Applied>
<Check_Number></Check_Number>
<CC_Number>****************</CC_Number>
<Notes></Notes>
</Payment>
</Invoice>

There can be an arbitrary number of "line" and "payment" sections..

Any idea how that would be handled in the MyBase format so that I
could use the "line" portion as a subreport?

I'm going to accept your question as the answer as you did answer my initial question but if you have any idea on the relational aspect, let me know!

Thanks!
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18857732
You are missing a closing   </Payment>   tag here:

<CC_Number>****************</CC_Number>
<Payment>

I'm not sure what you mean a "sub-report"
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18857774
What you are goign to need to do is to "transform" the XML generated by your program into
the format that myBase is expecting.
What I would suggest doing is to take a TClientDataset and create records that match the
data in your XML and then export it to the myBase format XML and then let's see the differences
and work on an XSLT transform to create the myBase from your XML.

I don't think the XMLTable will work for you because of the way that myBase's METADATA
is formatted. I may be able to modify it to do that but it will take some time.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mitchvincent
ID: 18857781
The closing payment tag is there,second line from the bottom.

Anyway - see how I have multiple <line> blocks in my XML? I'm not sure how to translate my XML to something that MyBase could work with.
0
 

Author Comment

by:mitchvincent
ID: 18857847
Hi Eddie -- the issue is that this software isn't written in Delphi - that's why I'm curious about "manually" creating the compatible XML.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18857962
[quote]The closing payment tag is there,second line from the bottom.[/quote]
YOU NEED TWO closing payment tags because you have TWO OPENING payment tags.

If this software is producing this XML, it is flawed. This is not well-formed XML and won't
pass any validation.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18857973
I'd like to know more about the data and what you expect it to be.
Is this some kind of record layout from some table? If so, what is the schema?
Do you need the payment info? How do you want the data/report designed?
0
 

Author Comment

by:mitchvincent
ID: 18858011
You're right about the second payment tag - I copy/pasted that and trimmed a few bits of sensitive information.

The XML itself is the table - I don't have a schema designed outside the XML I produce. Everything is string, though, and the report layout will be done with ReportMan (reportman.sourceforge.net). I can use ReportMan through OBDC directly to my data source but the lack of currency formatting functions in ReportMan (that can change the currency symbol through use of the GetCurrencyFormat Windows API function.

My hope is to use ReportMan's MyBase support and create XML files in the format needed so I can format dates and currency strings in my software verses having to do it in the report generator.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18859159
That doesn't look like any table data that I've seen exported to XML.

But, I would think that it would be something like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
 <Invoice>
  <Status>Pending</Status>
  <Customer_Contact>Bob Smith</Customer_Contact>
  <Customer_Phone1>111-222-3333</Customer_Phone1>
  <Customer_Phone2></Customer_Phone2>
  <Customer_Fax></Customer_Fax>
  <Customer_Email>sales@...</Customer_Email>
  <Line>
    <Quantity>5</Quantity>
    <Item>SKU1 - Demo Item1 - Description of demo item 1</Item>
    <Price>$10.00</Price>
    <Line_Total>$50.00</Line_Total>
  </Line>
  <Line>
    <Quantity>1.5</Quantity>
    <Item>Labor - Computer repair labor</Item>
    <Price>$35.00</Price>
    <Line_Total>$52.50</Line_Total>
  </Line>
  <Payment>
    <Payment_Type>Check</Payment_Type>
    <Payment_Date>10-08-2004</Payment_Date>
    <Payment_Amount>$1.00</Payment_Amount>
    <Payment_Applied>$1.00</Payment_Applied>
    <Check_Number></Check_Number>
    <CC_Number>****************</CC_Number>
  </Payment>  
  <Payment>
    <Payment_Type>CC</Payment_Type>
    <Payment_Date>10-12-2004</Payment_Date>
    <Payment_Amount>$2.00</Payment_Amount>
    <Payment_Applied>$2.00</Payment_Applied>
    <Check_Number></Check_Number>
    <CC_Number>****************</CC_Number>
    <Notes></Notes>
  </Payment>
 </Invoice>

And, this is how I'd break out the data:

Invoice Table:
Invoice_Key
Customer_Key
Status

Customer Table:
Customer_Key
Customer_Contact
Customer_Phone1
Customer_Phone2
Customer_Fax
Customer_Email

Line Table:
Line_Key
Customer_Key
Line_Qty
Line_Item
Line_Price
Line_Total

Payment Table:
Payment_Key
Customer_Key
Payment_Type
Payment_Date
Payment_Amount
Payment_Applied
Check_Number
CC_Number


And, to parse this info into a TClientDataset I'd do this:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, msxml2_tlb, DB, DBClient, StdCtrls, COMObj;

type
  TForm1 = class(TForm)
    Button1: TButton;
    cdsInvoice: TClientDataSet;
    cdsLine: TClientDataSet;
    cdsPayment: TClientDataSet;
    cdsCustomer: TClientDataSet;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    oXMLDoc:                   IXMLDOMDocument2;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  oXMLDoc  := CreateOleObject('MSXML2.DOMDocument.3.0') as IXMLDOMDocument2;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  oXMLDoc  := nil;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  oNodes: IXMLDOMNodeList;
  oNode:  IXMLDOMNode;
  i:      Integer;
begin
  oXMLDoc.load('c:\inv.xml');
  // Insert the custoemr record
  cdsCustomer.Open;
  cdsCustomer.Insert;
  cdsCustomer.FieldByName('Customer_Key').AsInteger := 1;
  cdsCustomer.FieldByName('Customer_Contact').AsString := oXMLDoc.childNodes.Item[1].childNodes.Item[1].Text;
  cdsCustomer.FieldByName('Customer_Phone1').AsString  := oXMLDoc.childNodes.Item[1].childNodes.Item[2].Text;
  cdsCustomer.FieldByName('Customer_Phone2').AsString  := oXMLDoc.childNodes.Item[1].childNodes.Item[3].Text;
  cdsCustomer.FieldByName('Customer_Fax').AsString     := oXMLDoc.childNodes.Item[1].childNodes.Item[4].Text;
  cdsCustomer.FieldByName('Customer_Email').AsString   := oXMLDoc.childNodes.Item[1].childNodes.Item[5].Text;
  cdsCustomer.Post;
  cdsCustomer.Close;;

  // Now, gather the Line records
  oNodes := oXMLDoc.selectNodes('//Line');
  cdsLine.Open;
  for i := 0 to oNodes.length -1 do
  begin
    oNode := oNodes.Item[i];
    cdsLine.Insert;
    cdsLine.FieldByName('Line_Key').AsInteger     := i + 1;
    cdsLine.FieldByName('Customer_Key').AsInteger := 1;
    cdsLine.FieldByName('Quantity').AsString      := oNode.childNodes[0].Text;
    cdsLine.FieldByName('Item').AsString          := oNode.childNodes[1].Text;
    cdsLine.FieldByName('Price').AsString         := oNode.childNodes[2].Text;
    cdsLine.FieldByName('Line_Total').AsString    := oNode.childNodes[3].Text;
    cdsLine.Post;
  end;
  cdsLine.Close;

  // Now, gather the Payment records
  oNodes := oXMLDoc.selectNodes('//Payment');
  cdsPayment.Open;
  for i := 0 to oNodes.length -1 do
  begin
    oNode := oNodes.Item[i];
    cdsPayment.Insert;
    cdsPayment.FieldByName('Payment_Key').AsInteger    := i + 1;
    cdsPayment.FieldByName('Customer_Key').AsInteger   := 1;
    cdsPayment.FieldByName('Payment_Type').AsString    := oNode.childNodes[0].Text;
    cdsPayment.FieldByName('Payment_Date').AsString    := oNode.childNodes[1].Text;
    cdsPayment.FieldByName('Payment_Amount').AsString  := oNode.childNodes[2].Text;
    cdsPayment.FieldByName('Payment_Applied').AsString := oNode.childNodes[3].Text;
    cdsPayment.FieldByName('Check_Number').AsString    := oNode.childNodes[0].Text;
    cdsPayment.FieldByName('CC_Number').AsString       := oNode.childNodes[0].Text;
    cdsPayment.FieldByName('Notes').AsString           := oNode.childNodes[0].Text;
    cdsPayment.Post;
  end;
  cdsPayment.Close;

  // And finally the Invoice record
  cdsInvoice.Open;
  cdsInvoice.Insert;
  cdsInvoice.FieldByName('Invoice_Key').AsInteger  := 1;
  cdsInvoice.FieldByName('Customer_Key').AsInteger := 1;
  cdsInvoice.FieldByName('Status').AsString        := oXMLDoc.childNodes.Item[1].childNodes.Item[0].Text;
  cdsInvoice.Post;
  cdsInvoice.Close;
end;

end.

Now, you are going to have to setup the master-detail for your report but I have all the data
"transformed" into CDS XML files for you... Here are the resulting CDS XML files after the transform:

Invoice.XML:
<?xml version="1.0" standalone="yes"?>  
  <DATAPACKET Version="2.0">
    <METADATA>
      <FIELDS>
        <FIELD attrname="Invoice_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Customer_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Status" fieldtype="string" WIDTH="7"/>
      </FIELDS>
      <PARAMS CHANGE_LOG="1 0 4" PRIMARY_KEY="1"/>
    </METADATA>
    <ROWDATA>
      <ROW RowState="4" Invoice_Key="1" Customer_Key="1" Status="Pending"/>
    </ROWDATA>
  </DATAPACKET>
 
Customer.XML:
<?xml version="1.0" standalone="yes"?>  
  <DATAPACKET Version="2.0">
    <METADATA>
      <FIELDS>
        <FIELD attrname="Customer_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Customer_Contact" fieldtype="string" WIDTH="9"/>
        <FIELD attrname="Customer_Phone1" fieldtype="string" WIDTH="12"/>
        <FIELD attrname="Customer_Phone2" fieldtype="string" WIDTH="32"/>
        <FIELD attrname="Customer_Fax" fieldtype="string" WIDTH="32"/>
        <FIELD attrname="Customer_Email" fieldtype="string" WIDTH="9"/>
      </FIELDS>
      <PARAMS CHANGE_LOG="1 0 4" PRIMARY_KEY="1"/>
    </METADATA>
    <ROWDATA>
      <ROW RowState="4" Customer_Key="1" Customer_Contact="Bob Smith"
           Customer_Phone1="111-222-3333" Customer_Phone2="" Customer_Fax=""
           Customer_Email="sales@..."/>
    </ROWDATA>
  </DATAPACKET>

Line.XML
<?xml version="1.0" standalone="yes"?>  
  <DATAPACKET Version="2.0">
    <METADATA>
      <FIELDS>
        <FIELD attrname="Line_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Customer_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Quantity" fieldtype="string" WIDTH="3"/>
        <FIELD attrname="Quantity" fieldtype="string" WIDTH="3"/>
        <FIELD attrname="Item" fieldtype="string" WIDTH="46"/>
        <FIELD attrname="Price" fieldtype="string" WIDTH="6"/>
        <FIELD attrname="Line_Total" fieldtype="string" WIDTH="6"/>
      </FIELDS>
      <PARAMS CHANGE_LOG="1 0 4 2 0 4" PRIMARY_KEY="1"/>
    </METADATA>
    <ROWDATA>
      <ROW RowState="4" Line_Key="1" Customer_Key="1" Quantity="5" Item="SKU1 - Demo Item1 - Description of demo item 1"
           Price="$10.00" Line_Total="$50.00"/>
      <ROW RowState="4" Line_Key="2" Customer_Key="1" Quantity="1.5" Item="Labor - Computer repair labor"
           Price="$35.00" Line_Total="$52.50"/>
    </ROWDATA>
  </DATAPACKET>
 
Payment.XML
<?xml version="1.0" standalone="yes"?>
  <DATAPACKET Version="2.0">
    <METADATA>
      <FIELDS>
        <FIELD attrname="Payment_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Customer_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Payment_Type" fieldtype="string" WIDTH="5"/>
        <FIELD attrname="Payment_Date" fieldtype="string" WIDTH="10"/>
        <FIELD attrname="Payment_Amount" fieldtype="string" WIDTH="5"/>
        <FIELD attrname="Payment_Applied" fieldtype="string" WIDTH="5"/>
        <FIELD attrname="Check_Number" fieldtype="string" WIDTH="32"/>
        <FIELD attrname="CC_Number" fieldtype="string" WIDTH="16"/>
        <FIELD attrname="Notes" fieldtype="string" WIDTH="32"/>
      </FIELDS>
      <PARAMS CHANGE_LOG="1 0 4 2 0 4" PRIMARY_KEY="1"/>
    </METADATA>
    <ROWDATA>
      <ROW RowState="4" Payment_Key="1" Customer_Key="1" Payment_Type="Check" Payment_Date="10-08-2004"
           Payment_Amount="$1.00" Payment_Applied="$1.00" Check_Number="Check" CC_Number="Check" Notes="Check"/>
      <ROW RowState="4" Payment_Key="2" Customer_Key="1" Payment_Type="CC" Payment_Date="10-12-2004"
           Payment_Amount="$2.00" Payment_Applied="$2.00" Check_Number="CC" CC_Number="CC" Notes="CC"/>
    </ROWDATA>
  </DATAPACKET>
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18859172
OH BTW, I did not set field types for all your date, currency, etc fields.
You can modify the XML files to change the field types.
0
 
LVL 26

Expert Comment

by:EddieShipman
ID: 18859193
Oh, crud, I forgot that the Payment record needs an Invoice_Key instead of the
Customer_Key field.
Here is the resulting changes:

<?xml version="1.0" standalone="yes"?>
  <DATAPACKET Version="2.0">
    <METADATA>
      <FIELDS>
        <FIELD attrname="Payment_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Invoice_Key" fieldtype="i4" required="true"/>
        <FIELD attrname="Payment_Type" fieldtype="string" WIDTH="5"/>
        <FIELD attrname="Payment_Date" fieldtype="string" WIDTH="10"/>
        <FIELD attrname="Payment_Amount" fieldtype="string" WIDTH="5"/>
        <FIELD attrname="Payment_Applied" fieldtype="string" WIDTH="5"/>
        <FIELD attrname="Check_Number" fieldtype="string" WIDTH="32"/>
        <FIELD attrname="CC_Number" fieldtype="string" WIDTH="16"/>
        <FIELD attrname="Notes" fieldtype="string" WIDTH="32"/>
      </FIELDS>
      <PARAMS CHANGE_LOG="1 0 4 2 0 4" PRIMARY_KEY="1"/>
    </METADATA>
    <ROWDATA>
      <ROW RowState="4" Payment_Key="1" Invoice_Key="1" Payment_Type="Check" Payment_Date="10-08-2004"
           Payment_Amount="$1.00" Payment_Applied="$1.00" Check_Number="Check" CC_Number="Check" Notes="Check"/>
      <ROW RowState="4" Payment_Key="2" Invoice_Key="1" Payment_Type="CC" Payment_Date="10-12-2004"
           Payment_Amount="$2.00" Payment_Applied="$2.00" Check_Number="CC" CC_Number="CC" Notes="CC"/>
    </ROWDATA>
  </DATAPACKET>

The invoice points back to the customer so the Customer_Key field is not needed.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Nessus Scan 1 64
Merge 2 XML Files together 5 39
Multiple image collision 13 44
can't find the executable in Simulator 1 51
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now