Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1138
  • Last Modified:

delphi complex xml export

Hi!
Im trying to do a complex xml export with delphi (e-accounting). My problem is following:
The XML export contains data out of many tables (6 to be exact). Some data is written once per subtree and some data can be repeated many times (in this case headers and positions). I also have a schema that fits the xml.
My question is:
How can i export data from multiple tables so it fits the shema without adding nodes to xml  manualy or having to fetch the data manualy? Are there any components that would take datasources or tables as parameters and would produce desired xml with out much source? Can this be done? Or do i have to do my own step by step coding?

Answer should have some code or some hardcore hints. ;)
Thx.
0
datalabdd
Asked:
datalabdd
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Ivanov_GCommented:
you will have to build it manually using TXMLDocument, which exists in Delphi 6+
0
 
datalabddAuthor Commented:
There has to be a better way than this. Anybody else have any ideas?
0
 
Ivanov_GCommented:
Example with TXMLDocument:

<ROOT ATTRIBUTE="TEST_ATTR">
  <ELEMENT_INT>2</ELEMENT_INT>
  <ELEMENT_STR>Test Element</ELEMENT_STR>
</ROOT>

procedure TForm1.Button1Click(Sender: TObject);
var
  Root   : IXMLNode;
begin
  XMLDocument1.Active := True;
  Root := XMLDocument1.CreateElement('ROOT', '');
  Root.Attributes['ATTRIBUTE'] := 'TEST_ATTR';
  Root.ChildValues['ELEMENT_INT'] := '2';
  Root.ChildValues['ELEMENT_STR'] := 'Test Element';
  RichEdit1.Lines.Text := Root.XML;
end;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
datalabddAuthor Commented:
I know how to do this manualy.
But i need structured XML output.
For example:
<doc>
      <user>
                 <name> bruce willis </name>
                 <address>
                           <street>  21 jump street </street>
                           <town> washington       </town>
                           <zip>  90210                </zip>                  
                 </address>
                 <telephone>  234234234 </telephone>
                 <telephone> 32323233    </telephone>
      </user>
      <user>
       .
       .
       .
      </user>
</doc>

I get this data out of 3 different tables.
I want to know how i can create such XML output. The structure is very important. Ive read about using xslt files and schemas, but i cant figure out how and with what components this could be done. The main problem is that the structure is dynamic!! So user can change it how ever he wants. That is my problem.
Thx. ;)
0
 
Ivanov_GCommented:
"The main problem is that the structure is dynamic"

this is serious issue ...
0
 
datalabddAuthor Commented:
I know :'( Thats why Im asking.
I know how to create XML documents with fixed structure. But dynamic is a bit more complicated. :(
0
 
Wim ten BrinkCommented:
I would not even bother using any XML-based component for this. Basically, these tables have master-detail relationships so what you could do is just walk through each master-table, per record through each detail record, each sub-detail, each sub-sub-detail record, until all records have been written.

And writing it? Well, XML is just a plain text format. There's nothing wrong by just writing it to a textfile...

var AFile:TextFile;
begin
  AssignFile(AFile,'Output.xml');
  Rewrite(AFile);
  WriteLn(AFile, '<?xml version="1.0" encoding="UTF-8"?>');
  WriteLn(AFile, '<?xml-stylesheet type="text/xsl" href="Layout.xslt"?>');
  WriteLn(AFile, '<doc>');
  Table1.First;
  while not Table1.EOF do begin
    WriteLn(AFile, '<Master>');
    // Write the master record
    Table2.First;
    while not Table2.EOF do begin
      WriteLn(AFile, '<Detail>');
      // Write the detail record
      // Add more levels if required.
      WriteLn(AFile, '</Detail>');
     end;
    WriteLn(AFile, '</Master>');
  end;
  WriteLn(AFile, '</doc>');
  CloseFile(AFile);
end;

I know, I know... It would be neater to use the XML components to build this document. But for a quick output, this is just the fastest solution. The XML components are more useful for either importing data or modifying existing data. But to export to a new file, K.I.S.S...

Now, about using components to convert data to XML, there are some solutions for this. With the newest SQL Server, for example, it is possible to generate XML output instead of dataset output. Thus you could create a stored procedure that generates your XML file for you. It is also possible to use XSLT to transform XML data from one format to the other format. If you use ADO, then you can have your recordsets available as XML data. All you would have to do then is call a proper transformation method with a proper XSLT file. And if you're experienced with XSLT, it wouldn't take much time.

But while all these methods might seem cool and interesting, my method above is just the easiest.
0
 
EddieShipmanCommented:
Do you get ALL this data in one query at once?
0
 
datalabddAuthor Commented:
So if i understand u correctly i could transform xml file with no real structure in to a structured one with using XSLT?
Somehow doing it with SQL server (xml explicit mode) is not an option.
I dont have experience with XSLT. So how do i use XSLT with Delphi components?
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
datalabdd, EddieSHipman is pointing you on the right direction.
If you get datas in a Query (or AdoQuery), then you can call SaveToFile with pfXML parameter.
This saves the recordset in XML format so you'll have your xml file already created just in one step....

* Note that using SaveToFile the Saved File will stay open until the recordset is also open. See Delphi Help for more details

F68 ;-)
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
just a correction: -->Query (or AdoQuery), means Query (ADOQuery)...
That's an AdoDB effort
0
 
Wim ten BrinkCommented:
@Ferruccio68, the problem here is that the output must be saved to a specific XML format, possibly for processing through another application. If I read correctly, there's already a schema set for it. It is unlikely that ADO would generate an XML file matching this schema...

@datalabdd, yes. You can transform from one XML format to another XML format through XSLT. I've used this personally for my own CV (In dutch at http://www.workshop-alex.org/CV/) to have all data in a single XML file, then using several XSLT files to extract parts of the data and return it as XHTML (an XML substandard) that you see on that site. Sorry that it's in Dutch, though. ;-)

XSLT is just a plain scripting language that uses XML tags for formatting. http://www.xml.com/ is a good site to learn more about XML while at http://xmlspy.com/ you can buy an expensive tool to help you build those XSLT files. It's quite complex if you don't have a good tool to build these transformation files. http://www.xml.com/pub/a/2000/08/holman/index.html has specific information about XSLT.

About using those transformations inside Delphi, I had to import the MSXML type library and use this instead of the XML components that Delphi offers. Still not a big problem since XML is just a plain string format. Unfortunately I don't have my Delphi 7 sources available anymore.
If I'm correct, you need two variables and do something like this:

function ConvertXML: Widestring;
var
  xmlDoc: IXMLDOMDocument2;
  xsltDoc: IXMLDOMDocument2;
begin
  xmlDoc:= CoDOMDocument.Create;
  xmlDoc.Load('Your.XML');
  xsltDoc:= CoDOMDocument.Create;
  xsltDoc.Load('Your.XSLT');
  Result := xmlDoc.transformNode(xsltDoc);
end;

This is untested code, though... Not sure if it works, but it should be something like this.

The biggest problem is building that XSLT file anyway. The advantage is that the XSLT is just plain text so it's easy to modify it's output, though. No recompilation required. It's just that learning XSLT was the steepest learning curve I ever had. But with the right tools, I diod manage to grasp a little understanding about it.
There are other tools, though, that can help you. Best of all, there are some very special mapping tools that you can use to build a map of how an XML file must be converted to another XML format. But the good mappers are quite expensive...

Still, if I need to generate XML output, I just prefer to write it out as a textfile. Especially if the original data is NOT in XML format. Like in your situation, I'd still prefer to walk through all tables, doing writeln's to a textfile. (And I use SetTextBuf to speed up writing to textfiles!)
0
 
EddieShipmanCommented:
I wrote a XMLDataset control several years ago that took any TDataset and saved the output to XML.
You can have the source and modufy it to your content. I do not do schemas, so you will have to open the
XML and add the schema yourself. It oly does basic DTDs.
You can get it if you wmail me at mr_delphi_developer at yahoo dot com.
0
 
EddieShipmanCommented:
Forgot that I uploaded it here: http://www.delphipages.com/result.cfm?ID=3641
No need to contact me via email, just download it and go from there. I f you need
help with it or it doesn't work for you, let me know.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now