Separate XML and XSD files

Loading Data > Separate XML and XSD files

The normal XML standards have the data in the xml file and the table definitions in a separate xsd (xml schema definition) file which is only required when the table is being created or when the xml file is being validated for form. The xsd file name is found within the xml file in an xsi (xml schema instance) statement which can include a namespace definition;e.g.

<root xmlns="http://www.qad.com/wsl-schema"

xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"

xsi:schemaLocation="http://www.qad.com/load_table.xsd">

or no namespace;e.g.

<root xmlns="http://www.qad.com/wsl-schema"

xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="load_table.xsd">

The xsd file is an xml file and should be found in the same directory as the xml file that calls it. This xsd file will contain the column definitions for the load table which will be defined during the drag and drop.

The column definitions within the xsd file must be detailed enough to define a load table that the xml file can be loaded into. The data type mapping between the xsd file and the database have been implemented as below:

XSD	SQL Server	Oracle	IBM DB2
string with length	char()	char()	char()
string with maxlength	varchar()	varchar2()	varchar()
integer	integer	integer	integer
decimal with precision and scale	numeric(x,y)	number(x,y)	decimal(x,y)
dateTime (ISO8601)	datetime	date	timestamp
i2	integer	integer	integer
i4	integer	integer	integer
r4	float	number	float
r8	float	number	float
float	float	number	float

These are the ISO-ANSI SQL/XML standards and in the case of integers, datetime and floats the column can be defined with one line. i.e.

<xsd:element name="Policy_ID" type="xsd:integer"/>

<xsd:element name="Quote_Date" type="xsd:dateTime"/>

<xsd:element name="Quote_Price" type="xsd:r4"/>

In the case of strings and decimals the column requires a bit more detail to produce the correct data type. Strings can be fixed length with padded data by using the length attribute. The following will produce a char(1) column called Excess_Waiver:

<xsd:element name="Excess_Waiver">

<xsd:restriction base="xsd:string">

<xsd:length value="1"/>

</xsd:restriction>

</xsd:element>

Strings can be of variable length by using the maxLength attribute. The following produces a column of SQL Server varchar(8) or Oracle varchar2(8) called Password:

<xsd:element name="Password">

<xsd:restriction base="xsd:string">

<xsd:maxLength value="8"/>

</xsd:restriction>

</xsd:element>

Decimal numbers are defined with the precision and scale attributes. If the scale attribute is zero or missing then the column will be a whole number of size precision. The following produces a SQL Server column of numeric(6) or an Oracle column of number(6):

<xsd:element name="code" >

<xsd:restriction base="xsd:decimal">

<xsd:precision value="6"/>

<xsd:scale value="0"/>

</xsd:restriction>

</xsd:element>

The following produces a SQL Server column of numeric(8,2) or an Oracle column of number(8,2):

<xsd:element name="code" >

<xsd:restriction base="xsd:decimal">

<xsd:precision value="8"/>

<xsd:scale value="2"/>

</xsd:restriction>

</xsd:element>

An example file with most data types would be as follows:

<xsd:schema xmlns="http://www.qad.com/wsl-schema"

xmlns:xsd="http://www.qad.com/XMLSchema">

<xsd:element name="Col_name1" type="xsd:integer"/>

<xsd:element name="Col_name4" type="xsd:dateTime"/>

<xsd:element name="Col_name5">

<xsd:restriction base="xsd:string">

<xsd:maxLength value="100"/>

</xsd:restriction>

</xsd:element>

<xsd:element name="Col_name6">

<xsd:restriction base="xsd:string">

<xsd:length value="100"/>

</xsd:restriction>

</xsd:element>

<xsd:element name="Col_name7" type="xsd:float"/>

<xsd:element name="Col_name8" >

<xsd:restriction base="xsd:decimal">

<xsd:precision value="6"/>

<xsd:scale value="2"/>

</xsd:restriction>

</xsd:element>

</xsd:schema>

The column order will be the same as the xsd file.

The only rules concerning the xml file are that the data element tags are the column names and each row of data is a child of the root element. eg

<row>

<dim_customer_key>7</dim_customer_key>

<address>3700 PARNELL RISE</address>

<city>BEAVERTON</city>

<dss_source_system_key>1</dss_source_system_key>

<dss_update_time>2003-10-03T10:02:15.310</dss_update_time>

</row>

Any columns which are missing from the row will be NULL in the loaded row.

The dateTime format in the xml file is defined as ISO8601 which looks like this:

2003-10-03T10:02:15.310

QAD Data Warehouse Designer will load this string into Oracle as:

TO_DATE(‘20031003100215’,’YYYYMMDDHH24MI’)

or for SQL Server:

CONVERT(DATETIME,‘2003-10-03T10:02:15.310’,126)

or for DB2:

TIMESTAMP_FORMAT('20031003100215', 'YYYYMMDDHH24MISS')

The xsd file is only required to create the load table, if the load table is only being loaded then this file is ignored. To check that the xml and xsd files are well formed you can open them with any web browser. If the files display with no errors then they are valid xml files.