fpXMLXSDExport: what changed
Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.
| Old | New | Line | |
|---|---|---|---|
| 1 | - | ||
| 2 | - | ||
| 3 | - | ||
| 4 | - | ||
| 5 | - | ||
| 6 | - | ||
| 7 | - | ||
| 8 | - | ||
| 9 | - | ||
| 10 | - | ||
| 11 | - | ||
| 12 | - | ||
| 13 | - | ||
| 14 | - | ||
| 15 | - | ||
| 16 | 1 | ## Overview | |
| 17 | 2 | ||
| 18 | 3 | ### FPC | |
| 19 | 4 | ||
| 20 | - | ||
| 5 | + | The fpXMLXSDExport module is part of [[fpDBExport]] in the FreePascal FCL database modules. | |
| 21 | 6 | ||
| 22 | - | ||
| 7 | + | This export module provides export to various forms of [[XML]], selectable by the ExportFormat setting. Note that not exporting indexes appears as a limitation in most formats below, but indexes probably will probably need to be changed anyway in an new environment/database. | |
| 23 | 8 | ||
| 24 | - | ||
| 9 | + | There is also a [[fpsimplexmlexport]] module which can export a single format XML. | |
| 25 | 10 | ||
| 26 | 11 | ### Lazarus | |
| 27 | 12 | ||
| 28 | - | ||
| 13 | + | The component is located on the *Data Export* tab. | |
| 29 | 14 | ||
| 30 | - | ||
| 15 | + |  | |
| 31 | 16 | ||
| 32 | 17 | ## Export formats | |
| 33 | 18 | ||
| 34 | - | ||
| 19 | + | This component supports various XML formats. | |
| 35 | 20 | ||
| 36 | 21 | ### AccessCompatible | |
| 37 | 22 | ||
| 38 | - | ||
| 23 | + | This format generates Microsoft Access XP/2002-2010 (and presumably SQL Server) compatible XML. | |
| 39 | 24 | ||
| 40 | - | ||
| 25 | + | You can specify to include or exclude inline XSD data definition with the CreateXSD setting. Recommended to leave it to true so Access can build up the table structure. If you have an existing compatible table, you wouldn't need the XSD. | |
| 41 | 26 | ||
| 42 | - | ||
| 27 | + | Index definitions are exported in this format. | |
| 43 | 28 | ||
| 44 | - | ||
| 29 | + | The Access format as emitted by Access XP at least uses the decimal separator of the computer the export is running on. E.g. a US locale will emit 42.42, while a Dutch locale will emit 42,42. If you want to override the decimal separator to a value regardless of locale (recommended if exchanging data across multiple locales), please set the DecimalSeparator (e.g. to '.'). | |
| 45 | 30 | ||
| 46 | - | ||
| 31 | + | Limitations: | |
| 47 | 32 | ||
| 48 | - | ||
| 49 | - | ||
| 50 | - | ||
| 51 | - | ||
| 33 | + | - Import of BLOB/binary type data in Access is problematic as Access has no real binary datatype, only an OLE datatype. Access stores OLE metadata, after that the binary object. When exporting to XML, Access stores the entire packet as base64 data. We cannot replicate this functionality, so we just base64 encode the BLOB, and lose the OLE metadata. If you want to, in Access, you can probably programmatically read the data as blob data and copy it to a "real" OLE field. See e.g. <http://support.microsoft.com/?kbid=103257> for hints on how to save the contents of the export BLOB to a file. | |
| 34 | + | - Date/time field data outside of Access limits are discarded (a NULL is inserted). | |
| 35 | + | - GUIDs are exported as text as Access has no equivalent field type. | |
| 36 | + | - Large numeric values may overflow Access import capabilities. Patches welcome. | |
| 52 | 37 | ||
| 53 | 38 | ### ADONETCompatible | |
| 54 | 39 | ||
| 55 | - | ||
| 40 | + | This format generates XML compatible with the .Net framework (specifically the ADO.NET data access libraries). It should work for versions 2 to 4; version 1/1.1 has not been tested. | |
| 56 | 41 | ||
| 57 | - | ||
| 42 | + | In this format you can also specify XSD or no XSD using the CreateXSD setting. This output format is fairly generic and could be usable for import in other applications, as well. | |
| 58 | 43 | ||
| 59 | - | ||
| 44 | + | Limitations: | |
| 60 | 45 | ||
| 61 | - | ||
| 46 | + | - Index definitions are not exported in this format. | |
| 62 | 47 | ||
| 63 | 48 | ### ExcelCompatible | |
| 64 | 49 | ||
| 65 | - | ||
| 50 | + | This format generates Microsoft Excel XP/2002-2010 compatible XML. | |
| 66 | 51 | ||
| 67 | - | ||
| 52 | + | This format recreates the data as Excel worksheet data. It does not include formatting and formulas, just plain data. | |
| 68 | 53 | ||
| 69 | - | ||
| 54 | + | The CreateXSD setting has no effect in this format - the Excel XML format doesn't seem to support compatible metadata. | |
| 70 | 55 | ||
| 71 | - | ||
| 56 | + | Limitations: | |
| 72 | 57 | ||
| 73 | - | ||
| 74 | - | ||
| 75 | - | ||
| 76 | - | ||
| 58 | + | - Index definitions are not exported in this format. | |
| 59 | + | - Binary data is not supported in the Excel XML format. | |
| 60 | + | - The Boolean (true/false) data format is not supported in this Excel XML format; instead 0/1 integers are exported. | |
| 61 | + | - Line endings within text cells/fields (generated in Excel by pressing Alt-Enter) are not supported as we'd need to output | |
| 62 | + | in the XML which is not possible using current FCL XML libraries. | |
| 77 | 63 | ||
| 78 | - | ||
| 64 | + | The [[FPSpreadsheet]] code could be a good alternative to this format: it offers native format output and probably multiline support. | |
| 79 | 65 | ||
| 80 | 66 | ### DelphiClientDataset | |
| 81 | 67 | ||
| 82 | - | ||
| 68 | + | This format generates Delphi ClientDataset compatible XML. | |
| 83 | 69 | ||
| 84 | - | ||
| 70 | + | This format allows Delphi applications to use the ClientDataset.LoadFromFile method to read the data. | |
| 85 | 71 | ||
| 86 | - | ||
| 72 | + | It has been written based on TurboDelphi (Delphi 2006) behaviour and exports UTF-8 encoded XML. | |
| 87 | 73 | ||
| 88 | - | ||
| 74 | + | The CreateXSD setting has no effect in this format - Clientdataset metadata is always exported. | |
| 89 | 75 | ||
| 90 | - | ||
| 76 | + | Limitations: | |
| 91 | 77 | ||
| 92 | - | ||
| 93 | - | ||
| 94 | - | ||
| 78 | + | - Index definitions are not exported in this format. | |
| 79 | + | - Variants are exported as binary data; patches for conversion are welcome. | |
| 80 | + | - Apparently, ClientDatasets have insert new data and update data modes; as of now, only the insert new data mode is supported. | |
| 95 | 81 | ||
| 96 | 82 | ## Other settings | |
| 97 | 83 | ||
| 98 | - | ||
| 84 | + | As the XML formats used above defines how date/time formats, Boolean formats etc are used, these general export settings have no effect: | |
| 99 | 85 | ||
| 100 | - | ||
| 101 | - | ||
| 102 | - | ||
| 103 | - | ||
| 104 | - | ||
| 105 | - | ||
| 106 | - | ||
| 107 | - | ||
| 108 | - | ||
| 86 | + | - BooleanFalse | |
| 87 | + | - BooleanTrue | |
| 88 | + | - DateFormat | |
| 89 | + | - DateTimeFormat | |
| 90 | + | - DecimalSeparator (only has effects in Access compatible export mode, no effect in other modes) | |
| 91 | + | - CurrencyDigits | |
| 92 | + | - CurrencySymbol | |
| 93 | + | - IntegerFormat | |
| 94 | + | - TimeFormat | |
| 109 | 95 | ||
| 110 | 96 | ## Lazarus and examples | |
| 111 | 97 | ||
| 112 | - | ||
| 98 | + | For an example, please see the xmlxsdexporttest.lpr test program in packages\fcl-db\tests. | |
| 113 | 99 | ||
| 114 | - | ||
| 100 | + | An additional example can be found in your FPC installatoin in packages\fcl-db\examples\dbftool.lpr. | |
| 115 | 101 | ||
| 116 | - | ||
| 102 | + | In Lazarus, you can use the lazdbexport package (the **TFPDataExporter** and **TStandardExportFormats** components in particular) to use this method, or the **TXMLXSDExport** component. | |
| 117 | 103 | ||
| 118 | 104 | ## License | |
| 119 | 105 | ||
| 120 | - | ||
| 106 | + | The fpXMLXSDExport module is freeware, licensed under the MIT license: all use free, but no liability accepted. It is also licensed under the license used for the FreePascal Free Component Library (FCL), so take your pick. | |
| 121 | 107 | ||
| 122 | 108 | ## See also | |
| 123 | 109 | ||
| 124 | - | ||
| 125 | - | ||
| 110 | + | - [[fpDBExport]] | |
| 111 | + | - [[fpdbfexport]] Similar module that exports to DBF/(Visual) FoxPro formats. | |
| 126 | 112 | ||
| 127 | 113 | --- | |
| 128 | - | ||
| 114 | + | *From the Free Pascal wiki, page "fpXMLXSDExport", as it stood on 2017-06-30 after a change by E-ric, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).* |