Importing an XML file (hierarchy issue)

Hi,

I am trying to import an xml file using xml source object.

The problem is that this object ignores the hierarchy/dependeces of the elements, each output of the object has data of every element isolated... and i have to merge them to update my destination table, beacuse i need to insert several fields of diferent elements in the same table (only one). SISS doesnt take into account hierarchy and insert the records separatedly and fill the fields of the other elements with null...

Thats my xls:

Code Snippet

<?xml version="1.0" ?>

- <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

- <xs:element name="responses">

- <xs:complexType>

- <xs:sequence>

- <xs:element minOccurs="0" name="response">

- <xs:complexType>

- <xs:sequence>

- <xs:element minOccurs="0" maxOccurs="unbounded" name="reserva">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="localizador" type="xs:string" />

<xs:element minOccurs="0" name="programa" type="xs:string" />

<xs:element minOccurs="0" name="fecha_creacion" type="xs:string" />

<xs:element minOccurs="0" name="fecha_modificacion" type="xs:string" />

- <xs:element minOccurs="0" name="oficina_responsable">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="empresa" type="xs:string" />

<xs:element minOccurs="0" name="nombre" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="plazas" type="xs:string" />

- <xs:element minOccurs="0" maxOccurs="unbounded" name="salida">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="codigo" type="xs:string" />

<xs:element minOccurs="0" name="dias" type="xs:string" />

- <xs:element minOccurs="0" name="origen">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="nombre_corto" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element minOccurs="0" name="destino">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="nombre_corto" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="fecha" type="xs:string" />

<xs:element minOccurs="0" name="estado" type="xs:string" />

<xs:element minOccurs="0" name="importe" type="xs:string" />

<xs:element minOccurs="0" name="comision" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_emision" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_pago" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_confirmacion" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element minOccurs="0" maxOccurs="unbounded" name="pasajero">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="dni" type="xs:string" />

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="telefono" type="xs:string" />

<xs:element minOccurs="0" name="num_documento" type="xs:string" />

<xs:element minOccurs="0" name="primera_emision" type="xs:string" />

<xs:element minOccurs="0" name="ultima_emision" type="xs:string" />

<xs:element minOccurs="0" name="pagado" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

The XML Source is not ignoring hierarchy. In fact, I just tried using your schema with the XML Source, and it produced seven outputs. It then added an "id" column to the outputs, in order to represent the hierarchy. For instance, it creates a "response_id" for the response "table", and reserva has a response_id to tie it to the response row, and a reserva_id to identify the reserva row. oficina_responsable has empresa and nombre columns as well as a reserva_id to indicate which reserva row it is a child of.

This can be a pain in the neck, but it does preserve the hierarchy.

Looking at your schema, it seems to me that you might want to use an XSL transform to flatten your XML For instance, you could have an origen_nombre column and a destino_nombre column within the salida "table". I believe you could reduce it to three elements, and therefore three outputs from the XML source. You would have reserva, salida and pasajero. All three outputs would have a "reseva_id" column:

Code Snippet

<responses>

<reserva>

<localizador>x</localizador>

<salida>

<origen_nombre>nombre</origen_nombre>

...

</salida>

<salida>

<origen_nombre>nombre</origen_nombre>

...

</salida>

<pasajero>

...

</pasajero>

<pasajero>

...

</pasajero>

</reserva>

<reserva>

...

</reserva>

<responses>