How to translate entire SQL DB into XML?

Maybe this link is a start :
http://www.swissdelphicenter.ch/torry/showcode.php?id=772

I will also post my complete code of the littke project I made :

First content of file Unit1.dfm :
object Form1: TForm1
  Left = 487
  Top = 321
  Width = 403
  Height = 238
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  OnDestroy = FormDestroy
  PixelsPerInch = 96
  TextHeight = 13
  object lblInfo: TLabel
    Left = 8
    Top = 40
    Width = 28
    Height = 13
    Caption = 'lblInfo'
  end
  object btnDoExport: TButton
    Left = 8
    Top = 8
    Width = 75
    Height = 25
    Caption = 'btnDoExport'
    TabOrder = 0
    OnClick = btnDoExportClick
  end
  object ADOConnection1: TADOConnection
    ConnectionString =
      'Provider=SQLOLEDB.1;Password=ip;Persist Security Info=True;User ' +
      'ID=sa;Initial Catalog=Personeel;Data Source=A-715S005'
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'SQLOLEDB.1'
    Left = 16
    Top = 80
  end
  object qryGetAllUserTables: TADOQuery
    Connection = ADOConnection1
    CursorType = ctStatic
    Parameters = <>
    SQL.Strings = (
      'SELECT *'
      'FROM SysObjects'
      'WHERE Xtype = '#39'U'#39
      '  AND Status > 0'
      'ORDER BY Name')
    Left = 16
    Top = 112
  end
  object tblATable: TADOTable
    Connection = ADOConnection1
    Left = 48
    Top = 112
  end
end



Content of Unit1.pas :
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, ADODB, StdCtrls;

type
  TForm1 = class(TForm)
    btnDoExport: TButton;
    ADOConnection1: TADOConnection;
    qryGetAllUserTables: TADOQuery;
    tblATable: TADOTable;
    lblInfo: TLabel;
    procedure btnDoExportClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses
  FileCtrl,
  Unit_DS2XML;

{$R *.DFM}

procedure TForm1.btnDoExportClick(Sender: TObject);
var
  ExportFN: String;
begin
  Screen.Cursor := crSQLWait;
  With qryGetAllUserTables do
  begin
    Close;
    Open;
    While Not Eof do
    begin
      tblATable.Close;
      tblATable.TableName := FieldByName('Name').AsString;
      ExportFN := 'D:\XMLExport\' + tblATable.TableName + '.xml';
      ForceDirectories( ExtractFileDir( ExportFN ) );
      lblInfo.Caption := 'Exporting table ' + tblATable.TableName + ' to ' + ExportFN;
      lblInfo.Refresh;
      tblATable.Open;
      Unit_DS2XML.DatasetToXML( tblATable, ExportFN );
      tblATable.Close;
      Next;
    end;
    Close;
  end;
  Screen.Cursor := crDefault;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.Close;
  ADOConnection1.Open;
  qryGetAllUserTables.Open;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  qryGetAllUserTables.Close;
  ADOConnection1.Close;
end;

end.





I have pasted the code from the link http://www.swissdelphicenter.ch/torry/showcode.php?id=772 to a file called Unit_DS2XML.pas and saved it somewhere in a directory that is in my Delphi Library Path.

You can also directly download the DS2XML-File from : http://delphi.about.com/library/bluc/vcl/DS2XML.zip

Or, maybe the freeware-component (with source), found here can be interesting too : http://www.torry.net/db/access/db_html/xmldtset.zip

Hm, I tested out both solutions, bu I guess the last link I posted (with the freeware-component) is a better one.

The exported files are much bigger and when I doubleclick such an xml-file, it opens correctly in Intenet Explorer.

When I do the same with the DS2XML-Unit, I get smaller xml-file and errors in IE when opening them.

So I guess installing the freeware-component is in this case the best solution.
Just drop an TXMLDataSet on you Form and link its DataSet-property to a Table/Query-Component.
Then, in you code just call the Method SaveToFile( 'yourfilename.xml' ) of the TXMLDataSet-Component.

I think I have a final solution for your problem :

1. Download and install :  http://www.torry.net/db/access/db_html/xmldtset.zip

2. Create new application and drop following components on your Form :
      - 1 TADOConnection named 'ADOConnection1'
      - 1 TADOQuery named 'qryGetAllUserTables'
      - 1 TADOTable named 'tblATable'
      - 1 TXMLDataSet named 'XMLDataset1'
      - 1 TButton named 'btnDoExport'
      - 1 TLabel named 'lblInfo'

3. Set AdoConnection1.ConnectionInfo to link it to you SQL Server-DB.

4. Fill qryGetAllUserTables.SQL with : 'SELECT * FROM SysObjects WHERE Xtype = 'U' AND Status > 0 ORDER BY Name'.

5. Link 'qryGetAllUserTables' and 'tblATable' to your AdoConnection1.

6. Set XMLDataset1.DataSet to tblATable.

7. In your Button's OnClick event, place following code :
procedure TForm1.btnDoExportClick(Sender: TObject);
var
  ExportFN: String;
begin
  Try
    Screen.Cursor := crSQLWait;
    { Query will contain all usertables of the connected DB (SQL Server) }
    With qryGetAllUserTables do
    begin
      { Refresh the Query }
      Close;
      Open;
      { For each record in the query, do an XML-Export. }
      While Not Eof do
      begin
        { Link the next Table to the Table-Component by filling its TableName property }
        tblATable.TableName := FieldByName('Name').AsString;

        { Construct the Name of the ExportFile (xml-file). }
        ExportFN  := 'D:\XMLExport\' + tblATable.TableName + '.xml';

        { If the ExportDirectory does not exist, create it first. }
        ForceDirectories( ExtractFileDir( ExportFN ) );

        { Make the app interacting with the user. }
        lblInfo.Caption := 'Exporting table ' + tblATable.TableName + ' to ' + ExportFN;
        Self.Refresh; { Refresh the Form. }

        { Do the Export to XML. }
        Try
          { Do the export of the DataSet to the XML-File. }
          XMLDataset1.SaveToFile( ExportFN );
        except
          showmessage( 'Error with ' + tblATable.TableName );
        end; { try except }
        { Goto next record in the Query }
        Next;
      end; { While Not Eof do }
      Close; { Close the query. }
    end; { With qryGetAllUserTables do }
  finally
    Screen.Cursor := crDefault;
  end; { Try Finally }
end;


That's it.
When clicking on the button, a separate XML-File for every usertable of your SQL Server DB will be created.

Hehe, finally got where we want to be.
Best regards.

The Mayor.

Thank you Workshop Alex.

That is correct: I need to export the content of the entire DB which is information on clinical projects( subjects ) with test results etc. Each subject must be exported to a separate XML file.

Thank you wimmeyvaert! I do not have production mode experience beyond Paradox/XBase. That is why although your code exports system info I greatly appreciate your efforts, your detailed explanations, too.  

I've been provided with a XML schema...which (it seems to me) has to control how the XML files are to get populated with query results.  How to do that???

Thanks


Jordan

Im not sure if this is what you want.
Its a pretty simple example and will just copy every tables data in your database (except system tables) to xml documents.
It just uses:
1 TADOConnection
1 TADOTable
1 TDataSetProvider
1 TClientDataset

Set the TADOConnection to the database you want to backup the data from, and make sure to change the directory of where you want the files to go


unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DBClient, Provider, DB, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOTable1: TADOTable;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    Button1: TButton;
    ListBox1: TListBox;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
   slTableNames: TStringList;
   i: Integer;
begin
   slTableNames := TStringList.Create;
   try
      ADOConnection1.Connected := True;
      ADOConnection1.GetTableNames( slTableNames);
      ListBox1.Items.Assign( slTableNames);

      for i := 0 to slTableNames.Count - 1 do
      begin
         ClientDataSet1.Active := False;
         ADOTable1.Active := False;
         ADOTable1.TableName := Trim( slTableNames.Strings[ i]);
         ADOTable1.Active := True;
         ClientDataSet1.Active := True;
         ClientDataSet1.SaveToFile( 'c:\DBData\' + slTableNames.Strings[ i] + '.xml', dfXML);
      end;
   finally
      FreeAndNil( slTableNames);
      ADOConnection1.Connected := False;
   end;
end;

end.




object Form1: TForm1
  Left = 270
  Top = 160
  Width = 236
  Height = 411
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 120
    Top = 344
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object ListBox1: TListBox
    Left = 24
    Top = 8
    Width = 177
    Height = 329
    ItemHeight = 13
    TabOrder = 1
  end
  object ADOConnection1: TADOConnection
    Provider = 'C:\CGP_CONNECTION.UDL'
    Left = 72
    Top = 80
  end
  object ADOTable1: TADOTable
    Connection = ADOConnection1
    Left = 144
    Top = 88
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = ADOTable1
    Left = 72
    Top = 136
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 144
    Top = 152
  end
end

Hi Workshop Alex,
>>>you import the XML Schema into Delphi through the XML Data Binding wizard. (Which happens to be far from perfect, btw.)<<<<<

would you recommend C# in that case?

Also, would you recommend to use XmlDocument to create XML instances????

Jordan

Thank you, Workshop_Alex.

Thanks to wimmeyvaert although the solution was for a different case. I appreciate the answers which elivated my knowledge.

I've just allocated points. 450 to Workshop_Alex and 50 to  wimmeyvaert


Happy New Year to both of you and every one who answered.

Jordan