Link to home
Start Free TrialLog in
Avatar of mitchvincent
mitchvincent

asked on

Delphi, ReportMan, MyBase XML Format Help

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!
ASKER CERTIFIED SOLUTION
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mitchvincent
mitchvincent

ASKER

Where can I find information on your component?
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
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.
Avatar of mitchvincent
mitchvincent

ASKER

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!
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
You are missing a closing   </Payment>   tag here:

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

I'm not sure what you mean a "sub-report"
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
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.
Avatar of mitchvincent
mitchvincent

ASKER

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.
Avatar of mitchvincent
mitchvincent

ASKER

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.
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
[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.
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
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?
Avatar of mitchvincent
mitchvincent

ASKER

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.
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
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>
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
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.
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image
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.