toRelational {xml2relational}

Converting an XML document into a relational data model

Description

Imports an XML document and converts it into a set of dataframes each of which represents one table in the data model.

Usage

toRelational(
  file,
  prefix.primary = "ID_",
  prefix.foreign = "FKID_",
  keys.unique = TRUE,
  keys.dim = 6
)

Arguments

file

The XML document to be processed.

prefix.primary

A prefix for the tables' primary keys (unique numeric identifier for a data record/row in the table) . Default is "ID_". The primary key field name will consist of the prefix and the table name.

prefix.foreign

A prefix for the tables' foreign keys (). Default is "FKID_". The rest of the foreign key field name will consist of the prefix and the table name.

keys.unique

Defines if the primary keys must be unique across all tables of the data model or only within the table of which it is the primary key. Default is TRUE (unique across all tables).

keys.dim

Size of the 'key space' reserved for primary keys. Argument is a power of ten. Default is 6 which means the namespace for primary keys extends from 1 to 1 million.

Details

toRelational() converts the hierarchical XML structure into a flat tabular structure with one dataframe for each table in the data model. toRelational() determines automatically which XML elements need to be stored in a separate table. The relationship between the nested objects in the XML data is recreated in the dataframes with combinations of foreign and primary keys. The foreign keys refer to the primary keys that toRelational() creates automatically when adding XML elements to a table.

Column	Type	Description	Example
Style	character	Name of the SQL flavor.	"MySQL"
NormalField	character	Template string for a normal, nullable field.	"%FIELDNAME% %DATATYPE%"
NormalFieldNotNull	character	Template string for non-nullable field.	"%FIELDNAME% %DATATYPE% NOT NULL"
PrimaryKey	character	Template string for the definition of a primary key.	"PRIMARY KEY (%FIELDNAME%)"
ForeignKey	character	Template string for the definition of a foreign key.	"FOREIGN KEY (%FIELDNAME%) REFERENCES %REFTABLE%(%REFPRIMARYKEY%)"
PrimaryKeyDefSeparate	logical	Indicates if primary key needs additional definition like a any other field.	TRUE
ForeignKeyDefSeparate	logical	Indicates if foreign key needs additional definition like a any other field.	TRUE
Int		character	Name of integer data type. "INT"
Int.MaxSize	numeric	Size limit of integer data type.	4294967295
BigInt	character	Name of data type for integers larger than the size limit of the normal integer data type.	"BIGINT"
Decimal	character	Name of data type for floating point numbers.	"DECIMAL"
VarChar	character	Name of data type for variable-size character fields.	"VARCHAR"
VarChar.MaxSize	numeric	Size limit of variable-size character data type.	65535
Text	character	Name of data type for string data larger than the size limit of the variable-size character data type.	"TEXT"
Date	character	Name of data type date data.	"DATE"
Time	character	Name of data type time data	"TIME"
Date	character	Name of data type for combined date and time data.	"TIMESTAMP"

In the template strings you can use the following placeholders, as you also see from the MySQL example in the table:

1. %FIELDNAME%: Name of the field to be defined.

2. %DATATYPE%: Datatype of the field to be defined.

3. %REFTABLE%: Table referenced by a foreign key.

4. %REFPRIMARYKEY%: Name of the primary key field of the table referenced by a foreign key.

When you use your own defintion of an SQL flavor, then sql.style must be a one-row dataframe providing the fields described in the table above.

You can use the datatype.func argument to provide your own function to determine how the data type of a field is derived from the values in that field. In this case, the values of the columns Int, Int.MaxSize, VarChar, VarChar.MaxSize, Decimal and Text in the sql.style dataframe are ignored. They are used by the built-in mechanism to determine data types. Providing your own function allows you to determine data types in a more differentiated way, if you like. The function that is provided needs to take a vectors of values as its argument and needs to provide the SQL data type of these values as a one-element character vector.

Value

A list of standard R dataframes, one for each table of the data model. The tables are named for the elements in the XML document.

See Also

Other xml2relational: getCreateSQL(), getInsertSQL(), savetofiles()

Examples

# Find path to custmers.xml example file in package directory
path <- system.file("", "customers.xml", package = "xml2relational")
db <- toRelational(path)