datalabdd
asked on
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.
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.
Ivanov_G
you will have to build it manually using TXMLDocument, which exists in Delphi 6+
ASKER
There has to be a better way than this. Anybody else have any ideas?
Example with TXMLDocument:
<ROOT ATTRIBUTE="TEST_ATTR">
<ELEMENT_INT>2</ELEMENT_IN
<ELEMENT_STR>Test Element</ELEMENT_STR>
</ROOT>
procedure TForm1.Button1Click(Sender
var
Root : IXMLNode;
begin
XMLDocument1.Active := True;
Root := XMLDocument1.CreateElement
Root.Attributes['ATTRIBUTE
Root.ChildValues['ELEMENT_
Root.ChildValues['ELEMENT_
RichEdit1.Lines.Text := Root.XML;
end;
<ROOT ATTRIBUTE="TEST_ATTR">
<ELEMENT_INT>2</ELEMENT_IN
<ELEMENT_STR>Test Element</ELEMENT_STR>
</ROOT>
procedure TForm1.Button1Click(Sender
var
Root : IXMLNode;
begin
XMLDocument1.Active := True;
Root := XMLDocument1.CreateElement
Root.Attributes['ATTRIBUTE
Root.ChildValues['ELEMENT_
Root.ChildValues['ELEMENT_
RichEdit1.Lines.Text := Root.XML;
end;
ASKER
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. ;)
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. ;)
"The main problem is that the structure is dynamic"
this is serious issue ...
this is serious issue ...
ASKER
I know :'( Thats why Im asking.
I know how to create XML documents with fixed structure. But dynamic is a bit more complicated. :(
I know how to create XML documents with fixed structure. But dynamic is a bit more complicated. :(
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.x
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.
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.x
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.
Do you get ALL this data in one query at once?
ASKER
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?
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?
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 ;-)
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 ;-)
just a correction: -->Query (or AdoQuery), means Query (ADOQuery)...
That's an AdoDB effort
That's an AdoDB effort
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.