Solved

delphi complex xml export

Posted on 2004-09-23
14
990 Views
Last Modified: 2013-11-23
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
Comment
Question by:datalabdd
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12132023
you will have to build it manually using TXMLDocument, which exists in Delphi 6+
0
 

Author Comment

by:datalabdd
ID: 12132041
There has to be a better way than this. Anybody else have any ideas?
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12132106
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:datalabdd
ID: 12132155
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12132180
"The main problem is that the structure is dynamic"

this is serious issue ...
0
 

Author Comment

by:datalabdd
ID: 12132204
I know :'( Thats why Im asking.
I know how to create XML documents with fixed structure. But dynamic is a bit more complicated. :(
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12133219
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12135355
Do you get ALL this data in one query at once?
0
 

Author Comment

by:datalabdd
ID: 12140269
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
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 12140714
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
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 12140735
just a correction: -->Query (or AdoQuery), means Query (ADOQuery)...
That's an AdoDB effort
0
 
LVL 17

Accepted Solution

by:
Wim ten Brink earned 500 total points
ID: 12142046
@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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12142618
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
 
LVL 26

Expert Comment

by:EddieShipman
ID: 12147909
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
The viewer will learn how to dynamically set the form action using jQuery.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

776 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