Importing Access DB: source

Showing posts with label source. Show all posts

Wednesday, March 28, 2012

Importing TEXT File in DTS

Hello Guys,
I Hava a Source text connection and I'd like to take just the first row ( the header, of course) of the file to one table. How can I get this??
Tis is quite Urgent.
Thanxs;Does it have to be DTS?

Why not DTS in to a single column table (varchar(8000)) and the parse out the data in to the final table?|||Because the text files can be larger than 200MB. :(
I need to take just the first Row of the text file to know some important informations.|||You sure you're talking about row size?

That's a long row....|||No. Im speaking about the File.

Look an example of the beggining of the file:
I want to get the first row of the file and put it into a column. Note, just the first row. You can see that the another lines are in a different layout and would make my table very big.

A221539 DPVAT - COD BAR 151BANCO NOSSA
G00000000000000 20040123200401298664000000093373362
G00000000000000 20040123200401298663000000093383362
G00000000000000 20040123200401298669000000051623362
G00000000000000 20040123200401298669000000093383362
G00000000000000 20040123200401298664000000093383362
G00000000000000 20040123200401298661000000093383362
G00000000000000 20040123200401298661000000055433362|||Have you looked at BULK INSERT in BOL?

You can specify first row and last row (ie 1 and 1)

Wednesday, March 21, 2012

importing from oracle to an adp database

hi,

this is my current set up:

sql server 2005 express edition,
sql server management studio express,
oracle tables (original source data currently linked through odbc)
adp database file (new db where i'm trying to import the above oracle tables)

i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.

what is the fastest option? some of these tables hold over 1m records.

i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.

many thanks
luzippu

Hi luzippu,

Since you're going to be doing this on a regular basis I would recomend using the following:

Linked Server to the Oracle database.|||

Mike,

thank you for your feedback, it was nice to see a reply after many views...

Since I've posted this query I've had chance to find out more about the different versions.

In addition I have now the option to use MS SQL Server 7.0, thus DTS and the import scheduler facility.

Do you think this is the best option? Or shall I go with your solution above?

If the latter, could you give me an idea on what a Stored Procedure would look like to copy let's say:
ora_table1 to sql_table1 where create_date >= "01/01/2000"

Thank you
luzippu

sql

Importing from Excel to Sql Server 2005

I have about 50 Excel files from which I have to import data with some transformations to Sql Server. My first approach was to use Excel Source component in a data flow to read the data . However, as it turned out column X in some files was being converted to a DT_NTEXT blob and in other files it was mapped to WSTR. The reason I guess is because column X contained string of varying sizes - some greater than 255 characters while others less than 255 ( max was 3000 ) . My package used a ForEach loop to iterate over all the Excel files in a directory and feed that to the data flow task. I played around with IMEX and TypeGuessRows setting but they didnt help me . In my second approach I used 2 Excel sources ; one set up for the blob type and the other for the string type . I joined them together using a precedence contraint. This worked but I then figured out that there were 2 other columns in my data that exhibited the same behavior. I couldnt continue with the mulitple Excel source approach cause I would then have 8 Excel source components. Finally, I played around with Execute SQL Task . I selected the columns X,Y and Z , initialized 3 variables of type Object , used a ForEach to enumerate over the dataset and feed that to a script component that converted the objects to Strings. This seems to work for all types of data in the mulitple columns.

My question - has anyone encountered such problem ? What was the solution ? Just thought I would share this with the rest of the community. I cant seem to recall what the exact error I was getting ..but it was something like "cant convert long data to string " or something . I also keep getting annoying error icons in my Excel Source components used in the foreach loop. Something to do with acquire connection failed even after I set DelayValidation to "true".

Thanks

Another approach would be to use a conditional split component to redirect the two sets of files to two different data conversion components. You can then use the conversion components to convert the column X in both sets of files to Strings.

Carla

|||

Hi:

You have hit the most troublesome issue with the Jet Excel engine. I have found no easy way around for this problem. I have worked around the issue by having a dummy row in the second row of all my files. This dummy row contains dummy text >255 chars for all columns that can have >255 chars. My typeguessRows registry key is set to 2 and my connection strings have IMEX=1. This forces all these fields to type DT_NText.

I tried casting datatype in my select query, but Jet does not seem to support it. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1758786&SiteID=1

Please let me know if you find a solution.

HTH

Kar

|||

The solution was to use the third option which is using Execute SQL Task and mapping the troublesome columns to variable of Object type . You would then use a script component to cast the columns to String type. That seems to work right now.

Let me know if you have any further questions.

Monday, March 19, 2012

Importing Excel saved as XML

Hi guys!

I'm having troubles trying to import an excel file saved as XML.

Tried XML Source, Excel Source, OLEDB Source with no success.

Could someone point me to the right direction?

Thanks in advance.

I cannot see how you would do it without a custom script component as a source which opens the file with Excel automation (Not ideal).

You could (maybe) create your own XSD to allow SSIS to understand the document and create proper column / rows for you but you might end up doing a lot for nothing.

Can you not get it as a normal XLS file?

Friday, March 9, 2012

Importing data from one source in two destination tables linked by a foreign key

Hi,

I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :

table MOTHER (MOTHER_ID, MOTHER_NAME)

table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)

After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.

My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".

The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...

Is there a solution to chain two insertions with a foreign key constraint in a data flow?

Thanks

Regards,

Arnaud Gervais.

Just create two data flows in your control flow. The first to load the mother table. The second to then load the child table.|||

I can do that but I have a unique source file and with this solution I have to do same transformations of my data 2 times...

I've found a better solution that seems to be often used: http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx

It consists on creating two data flow and chaining them on the control flow as following:

- first data flow : I transform data, insert them in the mother table and keep the data flow in a raw file using multicast component.

- second data flow : I read the raw file to get transformed data, and find the foreign key with a lookup component bound to mother table previously imported.

It seems to be the best and fastest solution to do that...

Thanks,

Arnaud.

|||

it sounds like you are talking about the same thing at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=918028&SiteID=1

Jamie Thomson has posted feedback asking for "Intrinsic Flow Priority" to be included in SSIS. This would mean you could achieve what you are trying to do in a single data flow task.

Please go to (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058) and vote for this as well as posting your use case.

Wednesday, March 7, 2012

Importing data from a flat file

I have a flat file data source - call it "order". Its a text file that looks something like this:

ORDERNAME| Example1

CUSTOMER|Acme Industries
COST|11611
ITEMS
B1|550S162-43(33)|35.708|1|636
T1|550S162-43(33)|20.967|1|636
T2|550S162-43(33)|20.967|1|636
W1|350S162-43(33)|1.330|2|501
W21|350S162-43(33)|1.330|1|911
W2|350S162-43(33)|3.044|2|501
W20|350S162-43(33)|3.044|1|911

I would like to write the metadata to a [order header] table and the ITEMS to a [order detail] table. Can someone direct me to a example of something similar?

There are at least two ways to do this, either by using a custom script transform or by reading the source file twice, one for header rows, once for details. I posted a quick comparision of the options and a sample of the second approach on my blog (Pardon the site - it's still under construction -http://agilebi.com/cs/blogs/jwelch/default.aspx). You didn't specify if the source file contains multiple orders, which does have some impact on the solution.

I'm sure there are other approaches for this as well. I'd be interested to hear about other techniques, as I've run into this problem a few times myself, and am not completely satisfied with the options I've explored.

|||

jwelch wrote:
There are at least two ways to do this, either by using a custom script transform [...]

You can use a conditional split instead of a custom script.

|||

I ran into a problem with the conditional split when my header and detail rows had a different number of columns. How'd you work around that with the conditional split?

|||

jwelch wrote:
I ran into a problem with the conditional split when my header and detail rows had a different number of columns. How'd you work around that with the conditional split?

Normally, I read in the full record as one big text string and work with it that way.|||Yeah, I touched on that in the blog post. Guess I just don't like string manipulation |||Since my original post -- I've tried writing a class that reads the file into a StreamReader Object and then moves the data into a DataSet. Although not complete, this approach may turn out to be the simplest. If successful, I'll post the code once its complete.|||

JohnBGood wrote:
Since my original post -- I've tried writing a class that reads the file into a StreamReader Object and then moves the data into a DataSet. Although not complete, this approach may turn out to be the simplest. If successful, I'll post the code once its complete.

The only problem with this approach is this:
Building a class (ie programming) isn't really simple to those who don't build code. It might be simple for you, but perhaps not for others -- just something to consider|||

I do understand your points. Writing code from scratch to do ETL instead of using an ETL tool such as SSIS does seem counterintuitive.

With that said, I did finally manage to get my C# ETL utility working correctly. To me its an easier approach simply because I'm more comfortable with C# File I/O than SSIS. My intial thought was to use SSIS, but after reading up a little on working with nested data, it seemed to me that an SSIS approach would require more time (at least for me) to implement.

As far as the raw data file goes... it comes from a 3rd party system - so we don't have alot of control over what they provide.

Thanks for taking the time to answer my questions. Very much appreciated. I'll post the code for my C# routine tomorrow AM.

|||

Here's the code in C#...

|||

public class BOM
{
public DataSet GetOrderInfo(string logFileName) //bring the datafile into a dataset.
{
DataTable tblOrderInfo = CreateOrderInfo(); //procedure that creates a datatable
DataTable tblConnectors = CreateConnectorInfo();
DataTable tblMembers = CreateMemberInfo();
DataSet bom = new DataSet();

if (File.Exists(logFileName))
{
FileStream fs = new FileStream(logFileName, FileMode.Open, FileAccess.Read, FileShare.None);
StreamReader sr = new StreamReader(fs);
string Line;
string[] items = new string[50];
int i = 0;

while ((Line = sr.ReadLine()) != "ITEMS")
{
items Idea = Line;
string[] columns = Line.Split(new char[1] { '|' });
DataRow newrow;
newrow = tblOrderInfo.NewRow();
int j = 0;
foreach (string Data in columns)
{
newrow[j] = Data;
j++;
}
tblOrderInfo.Rows.Add(newrow);
i++;
}

while ((Line = sr.ReadLine()) != null)
{
items Idea = Line;
string[] columns = Line.Split(new char[1] { '|' });
DataRow newrow;
newrow = tblMembers.NewRow();
int j = 0;
foreach (string Data in columns)
{
newrow[j] = Data;
j++;
}
tblMembers.Rows.Add(newrow);
i++;
}

bom.Tables.Add(tblOrderInfo);
bom.Tables.Add(tblItems);
fs.Close();

return bom;

}

else
{
return null;

}
}

Friday, February 24, 2012

Importing an XML file with hirarchial data

Hello,

I would like to import an XML file containing hierarchial data into a table in SQLserver. I guess that I am supposed to use the XML source editor and connect it to the xml file containing the following:

<?xml version="1.0"?>
<VariantFamilies>
<VariantFamily FamilyID="XXX">
<FamilyDescription Language="en-GB">Variant Family Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1XX">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2XX">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
<VariantFamily FamilyID="XYY">
<FamilyDescription Language="en-GB">Variant Family 2 Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning 2 p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1YY">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2YY">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
</VariantFamilies>

Following columns exist in the table: WK, VariantFamily, VariantFamilyDescription, VariantID, VariantDescription.
wk is automatic,
Variant family should contain XYY,
VariantFamilyDescription should contain Variant Family 2 Description in English,
VariantID should contain 2YY,
VariantDescription should contain Variant Description 2 in English

in the outputname I can only choose one of the attributes at a time (the system has created two new items for each ID to identify the "parent". Now how do I go about to put the actual information into the columns? NOT the system created IDs. I would like to have one row per smalles grain (VariantID) hence having redundant data in Variant family column.

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I seem to be in a similar conundrum. However, what I want to do is move data elements from my XML file into multiple SQL Server database tables. I've created the XML data flow source and multiple data flow destinations, each pointing to their own table.

As Wenyang Hu pointed out, the XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table. I've now got over 50 tables, but can only select one (from the XML Source Editor columns form). Subsequently, when I go to add a data flow path to my data flow destination, I can only select a single output, which doesn't provide me with all the data I need to load a single database table.

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

That's true one xmlsrc output can be hooked with only one downstream, hence if you want several xmlsrc outputs to be loaded to one single destination, then as said above you'll need other transformation(s) either in between XMLSrc and destination, or, in another dataflow to manipulate.

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

To Sort on various key columns then join on those key columns at MergeJoin to eventually merge these 3 xmlsrc outputs into one destination. Since you have 50 outputs from xmlsrc, you can also save them into 50 destionation tables first, then use another dataflow with those tables as sources, then use other SSIS transforms to process based on your specific data hierarchy.

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering if the 'Union All' transformation task might work. The help on that task indicates it can have multiple inputs. If I map multiple inputs from my XMLSrc outputs, then maybe this will work. Then I can have about 10 union all task to map to my 10 SQL tables. Thoughts? I'm hoping to understand the union all as the help doesn't indicate that it does anything unusual.
thanks.
|||

UnionAll unions all its input rows but a) the column metadata will be built up based only on the primary input(the first one hooked up with unionAll) b)it does not "join" on key columns which you may need to merge those hierarchical data from xml file into one destination without loosing your data logic. So unless your multiple xmlsrc outputs have identical column structure, unionall won't fit your need I think.

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

<Property>
<PropertyId>TheIdINeed</PropertyId>
....
....
<Buildings>
<Building id="123">
.....
......
<Rooms>
<Room id ="ABC">
.....
.....
<Beds>
<Bed id="cfg">
.....
.....
</Bed>
</Beds>
</Room>
</Rooms>
</Building>
</Buildings>
</Property>

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Importing an XML file with hirarchial data

Hello,

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,

Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering

if the 'Union All' transformation task might work.

The help on that task indicates it can have multiple inputs. If I

map multiple inputs from my XMLSrc outputs, then maybe this will

work. Then I can have about 10 union all task to map to my 10 SQL

tables. Thoughts? I'm hoping to understand the union

all as the help doesn't indicate that it does anything unusual.

thanks.|||

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Importing an XML file with hirarchial data

Hello,

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,

Thanks
Wenyang|||

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering

if the 'Union All' transformation task might work.

The help on that task indicates it can have multiple inputs. If I

map multiple inputs from my XMLSrc outputs, then maybe this will

work. Then I can have about 10 union all task to map to my 10 SQL

tables. Thoughts? I'm hoping to understand the union

all as the help doesn't indicate that it does anything unusual.

thanks.|||

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

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

<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>

Importing an XML file (hierarchy issue)

Hi,

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

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>

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

Code Snippet

<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>

Wednesday, March 28, 2012

Importing TEXT File in DTS

Wednesday, March 21, 2012

importing from oracle to an adp database

Importing from Excel to Sql Server 2005

Monday, March 19, 2012

Importing Excel saved as XML

Friday, March 9, 2012

Importing data from one source in two destination tables linked by a foreign key

Wednesday, March 7, 2012

Importing data from a flat file

Friday, February 24, 2012

Importing an XML file with hirarchial data

Importing an XML file with hirarchial data

Importing an XML file with hirarchial data

Importing an XML file (hierarchy issue)

Importing an XML file (hierarchy issue)

Importing Access DB

Blog Archive

About Me