XML File load

XML file loads are only supported from a Windows connection. There are multiple formats for data exchange when using XML. See the sections below for details on how an XML file is handled.
 
To load an XML file located in a Windows directory, proceed as follows:

1. Create a connection to the Windows system.
2. Browse to the connection and locate the XML file.
3. Make Load tables the middle pane drop target by double-clicking on the Load Table object group in the left pane.
4. Drag the XML file into the middle pane.

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. For example:

<row>
<dim_customer_key>7</dim_customer_key>
<code>228</code>
<name>JOHN AND JOES TOYS</name>
<address>3700 PARNELL RISE</address>
<city>BEAVERTON</city>
<state>OR</state>
<dss_source_system_key>1</dss_source_system_key>
<dss_update_time>2003-10-03T10:02:15.310</dss_update_time>
</row>

Supported XML Formats

WhereScape RED supports two types of xml file construct. 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. An alternate standard is used by Microsoft. This second standard is an in line definition which produces one file which contains a Schema element in the data stream where the column names and their approximate data types are defined.

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; for example:

 <root xmlns="http://www.wherescape.com/wsl-schema"
xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xsi:schemaLocation="http://www.wherescape.com/load_table.xsd">
or no namespace;e.g.
<root xmlns="http://www.wherescape.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:

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.wherescape.com/wsl-schema"
      xmlns:xsd="http://www.wherescape.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.
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

• WhereScape RED 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')

• Or for Teradata:
  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.

In line schema definition

The other supported xml construct allows the use of in line schema definitions as produced by the Microsoft FOR XML AUTO, ELEMENTS, XMLDATA query. This will produce one file which contains a Schema element in which the column names and their approximate data types are defined. Because the supplied data types are not concise enough to define the table columns correctly, this method will produce load tables of either data type text for SQL Server or varchar2(4000) for Oracle. The column names are taken from the <element type="col_name"/> strings within the Schema element. The data elements will be the same as above with the column names making up the start and end tags and the rows being the children of the root element. The file that is produced by the FOR XML query above needs to be changed slightly to comply with the xml standard. Remove everything before the Schema element and then give the file a starting root element and a closing root element.eg <root> and </root>
The xml files can optionally start with an xml pre process statement. For example:
<?xml version="1.0"?>
They may also contain xml comments. For example:
<!— comments    -->