Solved

delphi complex xml export

Posted on 2004-09-23
14
1,023 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 

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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

751 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