Generating XML Schema and XML Data files from a TDataSet

[Pierre le Riche]

Hi,

Is there an easy way to generate both an XML schema file as well as a XML
file containing all the data from an open TTable/TQuery?

What I basically want to do is create an XML schema file containing a list
of all the field names, and the corresponding XML data file containing the
field values for all the records from the open TDataset descendant.

Is there an easy way to do this, or will I have to write the code to
generate the .xml files myself?

Thanks a lot!

Pierre le Riche

[Joe Gilkey]

You can try using a TClientDataSet, and save it to an XML file. Start by
placing a TDataSetProvider and a TClientDataSet on your data module. Set
the DataSet property of the TDataSetProvider to your data set, and the
Provider property of the client data set to the data set provider. Open the
Table or Query, then open the Client Data Set. Once this is done, save the
ClientDataSet to a file with an XML extension. This will put the data, and
schema, into one XML file. If you know the layout of the XML file you would
like to create, you can use the XML Mapper tool, and transform the
TClientDataSet XML file into you own format.

Joe

"Pierre le Riche" <pler...@hotmail.com> wrote in message
news:3c330129_1@dnews...

[Pierre le Riche]

Thanks Joe,

The hitch with that solution is that when you start using TClientDataSet,
you have to lug midas.dll with you. Since the program in question is an
ISAPI DLL that will be running on an ISP's server, it is a complication we
would rather avoid. I have now decided to start coding on my own
TDatatSet -> XML unit, since it appears there is no easy solution around
(and XML is straight-forward enough)...

Thanks for the suggestion though - I was hoping that I was simply missing a
"TDataSet2XML" component on the Delphi component palette! No such luck, I
guess.

Best Regards and a Happy New Year,
Pierre

"Joe Gilkey" <jgi...@hotmail.com> wrote in message
news:3c331d4b$1_1@dnews...

[Jeff Rafter]

> The hitch with that solution is that when you start using TClientDataSet,
> you have to lug midas.dll with you. Since the program in question is an
> ISAPI DLL that will be running on an ISP's server, it is a complication we
> would rather avoid.

<shameless_plug style="leading question">What ISP are you
using?</shameless_plug>

> I have now decided to start coding on my own
> TDatatSet -> XML unit, since it appears there is no easy solution around
> (and XML is straight-forward enough)...

Two things you might want to look at:

http://www.niffler.com/delphi/

This is an XMLDataset component that works with OpenXML. Also ADO has the
ability to save a record to an XML file as well as provide a corresponding
Schema (I am not sure if it is XDR or XML Schema, off hand)

> Thanks for the suggestion though - I was hoping that I was simply missing
a
> "TDataSet2XML" component on the Delphi component palette! No such luck, I
> guess.

In D6 (Enterprise) there is the Data Mapper-- I think that might do what you
want though I have only toyed with it.

HTH,

--
Jeff Rafter
Defined Systems
http://www.defined.net
XML Development and Developer Web Hosting

[Michael Pham]

> The hitch with that solution is that when you start using TClientDataSet,
> you have to lug midas.dll with you. Since the program in question is an

You should be able to compile the MIDAS unit into your DLL by including it
in the USES clause. That way, you don't have to redistribute the MIDAS.DLL
file.

Mike Pham

"Pierre le Riche" <pler...@hotmail.com> wrote in message

news:3c33379e_2@dnews...

[Pierre le Riche]

Hi Jeff,

Thanks for the tips. I'm busy downloading the OpenXML Dataset components
now, and I'm also going to give ADO a go.

Unfortunately our company is situated in South Africa, so your "shameless
plug" was probably in vain! ;-)

Regards,
Pierre

[Jeff Rafter]

Pierre,

> Thanks for the tips. I'm busy downloading the OpenXML Dataset
> components now, and I'm also going to give ADO a go.

Sounds good-- if you find that you do write it on your own post here again.
Something like that would be very useful IMHO if it isn't already there (and
it should be made for Delphi 3/4+). Also, there is a lot of work that has
been done similar to this for many other languages-- it might be that there
is already a standard schema for SQL/ODBC databases-- that cut your job in
half. I will take a look around.

> Unfortunately our company is situated in South Africa, so your "shameless
> plug" was probably in vain! ;-)

Not so-- we have hosted companies from all over the world... ;-)

All the best,

[Vincent Parrett(VSoft)]

"Jeff Rafter" <.jeffr...@defined.net> wrote in message
news:3c344d09$1_1@dnews...

> > Unfortunately our company is situated in South Africa, so your
"shameless
> > plug" was probably in vain! ;-)
>
> Not so-- we have hosted companies from all over the world... ;-)

I'll bear witness to that, I'm in Australia, my partner is in Russia and we
host our website at defined in the US. Good service, reasonable price,
haven't experienced any problems so far<g>.

Regards

Vincent Parrett
AtoZed Software
Email vincent [ at ] atozedsoftware dot com
-------------------------------------------------------------------
Automate your build process with FinalBuilder
IntraWeb - True RAD development for the Web
http://www.atozedsoftware.com

[Jeff Rafter]

> Also, there is a lot of work that has
> been done similar to this for many other languages-- it might be that
there
> is already a standard schema for SQL/ODBC databases-- that cut your job in
> half. I will take a look around.

I checked around a little-- XML Spy generates a schema for any ODBC/SQL
database. I am guessing you want to embed this in your app-- but you may be
able to work off of the generated schema as a "target" if you write it. Spy
has a free 30 day evaluation:

http://www.xmlspy.com

I also found sql2dtd.py a python script that generates a DTD:

http://gnosis.cx/download/sql2dtd.py
http://www-106.ibm.com/developerworks/xml/library/x-matters9.html

Finally, for everything XML the Cover Pages:

http://xml.coverpages.org/xmlAndDatabases.html

HTH,

[Pierre le Riche]

Thanks Jeff,

I've completed work on my TDataSet to XML/XSD converter. It works pretty
well, but I'm not 100% sure that the data formatting in the XML file is the
way it should be (the format of timestamp fields for example). Since I'm
still looking for a really good .XSL visual designer, I haven't been able to
test it yet. I don't want to edit the XSL by hand, because I won't always be
around to do it, and these things have the tendency to require maintenance
and/or updates.

Anyone have opinions on which XSL tool is the best?

Thanks again...

Pierre

The unit follows below. The schema and XML layout is not quite standard, but
it's the way I like it, and it'll also work well with .XSL stylesheets. It
populates a TXMLDocument component's .XML property. Just call .savetofile
and you have it on disk.

------------------------------------------------------
unit DATA2XML;

interface

uses DB, SysUtils, XMLDoc, XMLIntf;

{Creates a W3C XML Schema (XSD) for the given dataset}
procedure DataSet2XSD(DataSet: TDataSet; XMLDocument: TXMLDocument);
{Converts the dataset records to XML}
procedure DataSet2XML(DataSet: TDataSet; XMLDocument: TXMLDocument);
{Translates a field data type to the corresponding W3C XML Schema data type}
function GetXMLDataType(FieldType: TFieldType): string;
{Translates a field value to the corresponding XSL format.}
function TranslateData(Field: TField): string;

implementation

{Translates a field data type to the corresponding W3C XML Schema data type}
function GetXMLDataType(FieldType: TFieldType): string;
begin
case FieldType of
ftSmallint: result := 'xs:short';
ftInteger: result := 'xs:int';
ftWord: result := 'xs:unsignedInt';
ftBoolean: result := 'xs:boolean';
ftFloat: result := 'xs:double';
ftCurrency: result := 'xs:double';
ftDate: result := 'xs:date';
ftTime: result := 'xs:time';
ftDateTime: result := 'xs:dateTime';
ftAutoInc: result := 'xs:int';
ftLargeint: result := 'xs:decimal';
ftTimeStamp: result := 'xs:dateTime';
else
result := 'xs:string';
end;
end;

{Translates a field value to the corresponding XLS format.}
function TranslateData(Field: TField): string;
var
y,m,d,h,n,s,ms: word;
begin
case Field.DataType of

{Floating point fields}
ftFloat:
begin
if (Field as TFloatField).currency then
result := formatfloat('0.##', Field.Value)
else
result := floattostr(Field.Value);
end;

{Timestamps}
ftDateTime, ftTimeStamp:
begin
{Decode the datetime}
DecodeDate(Field.Value, y, m, d);
DecodeTime(Field.Value, h, n, s, ms);
{Format the result}
result := inttostr(y) + '-'
+ copy(inttostr(m+100), 2, 2) + '-'
+ copy(inttostr(d+100), 2, 2) + 'T'
+ copy(inttostr(h+100), 2, 2) + ':'
+ copy(inttostr(n+100), 2, 2) + ':'
+ copy(inttostr(s+100), 2, 2) + '.'
+ copy(inttostr(ms+1000), 2, 3);
end;

else
result := Field.DisplayText;
end;
end;

{Converts a dataset to a XML Schema (XSD)}
procedure DataSet2XSD(DataSet: TDataSet; XMLDocument: TXMLDocument);
var
i: integer;
begin
{Reset the XML document's options}
XMLDocument.Active := false;
XMLDocument.FileName := '';
XMLDocument.Options := XMLDocument.Options
+ [doNodeAutoCreate, doNodeAutoIndent, doAttrNull, doAutoPrefix,
doNamespaceDecl]
- [doAutoSave];
XMLDocument.XML.Clear;
XMLDocument.Active := true;
{Create the main element}
with XMLDocument.AddChild('xs:schema', 'http://www.w3.org/2001/XMLSchema')
do
begin
{Add the query response}
with AddChild('element') do
begin
Attributes['name'] := 'QueryResponse';
with AddChild('complexType') do
begin
with AddChild('choice') do
begin
{Add the error response}
with AddChild('element') do
begin
Attributes['name'] := 'Error';
with AddChild('complexType') do
begin
with AddChild('attribute') do
begin
Attributes['name'] := 'Message';
Attributes['type'] := 'xs:string';
Attributes['use'] := 'required';
end;
end;
end;
{Add the dataset response}
with AddChild('element') do
begin
Attributes['name'] := 'DataSet';
with AddChild('complexType') do
begin
{Add the records}
with AddChild('sequence') do
begin
with AddChild('element') do
begin
Attributes['name'] := 'Record';
Attributes['minOccurs'] := '0';
Attributes['maxOccurs'] := 'unbounded';
with AddChild('complexType') do
begin
{Add the field definitions}
for i := 0 to Dataset.Fields.Count - 1 do
begin
with AddChild('attribute') do
begin
Attributes['name'] := Dataset.Fields[i].FieldName;
Attributes['type'] :=
GetXMLDataType(Dataset.Fields[i].DataType);
end;
end;
end;
end;
end;
{Add the record count attribute}
with AddChild('attribute') do
begin
Attributes['name'] := 'RecordCount';
Attributes['type'] := 'xs:integer';
Attributes['use'] := 'required';
end;
end;
end;
end;
end;
end;
end;
end;

{Converts the records from a dataset into XML}
procedure DataSet2XML(DataSet: TDataSet; XMLDocument: TXMLDocument);
var
i: integer;
begin
{Reset the XML document's options}
XMLDocument.Active := false;
XMLDocument.FileName := '';
XMLDocument.Options := XMLDocument.Options
+ [doNodeAutoCreate, doNodeAutoIndent, doAttrNull, doAutoPrefix,
doNamespaceDecl]
- [doAutoSave];
XMLDocument.XML.Clear;
XMLDocument.Active := true;
{Create the main element}
with XMLDocument.AddChild('QueryResponse') do
begin
{Is the dataset valid and open}
if (DataSet <> nil) and (DataSet.Active) then
begin
{Start at the first record}
DataSet.DisableControls;
DataSet.First;
{Add the dataset}
with AddChild('DataSet') do
begin
{Add the recordcount attribute}
Attributes['RecordCount'] := DataSet.RecordCount;
{Add the records}
while not DataSet.EOF do
begin
with AddChild('Record') do
begin
for i := 0 to DataSet.Fields.Count - 1 do
begin
if not DataSet.Fields[i].IsNull then
begin
Attributes[DataSet.Fields[i].FieldName] :=
TranslateData(DataSet.Fields[i]);
end;
end;
end;
{Next record}
DataSet.Next;
end;
end;
{Enable controls again}
DataSet.EnableControls;
end
else
begin
with AddChild('Error') do
begin
Attributes['Message'] := 'The DataSet was unspecified or could not
be opened.';
end;
end;
end;
end;

end.

[Andy Law]

Checkout <xmlcomposer> www.whitehill.com it's a wyswig xsl tool.

"Pierre le Riche" <pler...@hotmail.com> wrote in message

news:3c347fc1_1@dnews...

[Jeff Rafter]

> still looking for a really good .XSL visual designer, I haven't been able
to
> test it yet. I don't want to edit the XSL by hand, because I won't always
be
> around to do it, and these things have the tendency to require maintenance
> and/or updates.
>
> Anyone have opinions on which XSL tool is the best?

Another good one is Xselerator by Marrowsoft:

http://www.marrowsoft.com

-Jeff

[Mandar]

Hi
I am using this way to convert the data to xml.At client side i am just setting it as domdocument.xml.

I am not verymuch through with xml but this seems to wok fine.

function TRemoteDataAccess.GetDataSet(
const SqlString: WideString): WideString;
var
temp:IXMLDOMDocument2;
begin
temp:=CoDOMDocument30.Create;
TADODataSet.SaveToFile('Data.xml',pfXML);
temp.load('Data.xml');
GetDataSet:=temp.xml;
end;

But i am having problem in setting the xml to dataset. :)
Mandar

[Jeff Rafter]

Mandar,

> But i am having problem in setting the xml to dataset. :)

The problem is probably that the recordset status for the rows is set to
unmodified (rather than new). The ADO persisting only allows you to update
changed records from XML. There was a good thread on this in
borland.public.delphi.database.ado [1] that might help. If you are just
trying to save and restore (e.g. you are moving files from one database to
another) you might be able to load the XML into a TADOQuery and then
manually copy all of the records across to your dataset.

[1]
http://groups.google.com/groups?hl=en&threadm=3ab7852a_1%40dnews&rnum=3&prev
=/groups%3Fq%3DXML%2BLoadFromFile%26hl%3Den%26meta%3Dgroup%253Dborland.publi
c.delphi.database.ado

Good Luck,