Combining XML Files using SSIS or T-SQL etc.

Loop?
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
> How can I combine all my xml files so it can be processed by SSIS in order
> that I may create a table containing the imported xml files?
>
What is Loop?
Could you please explain more in detail.
Thank you in advance for your assistance.
"Peter W. DeBetta" wrote:

> Loop?
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
>
>
|||SSIS now has a Control Flow Item called Foreach Loop Container. You use this
to loop through all the xml files in a specified directory. You will also
need to create a Data Flow Task in the Foreach Loop Container. The samples
that come with SQL Server 2005 have an example of using this container
control, and you can find more info in BOL.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5EAFCB63-AC58-4592-A7DF-786AF5CABBBF@.microsoft.com...[vbcol=seagreen]
> What is Loop?
> Could you please explain more in detail.
> Thank you in advance for your assistance.
> "Peter W. DeBetta" wrote:

Combining XML Files

I have to combine two seperate fixed-length flat files into 1 xml file, however, the catch is the 1st flat file (header) can/will be converted to an xml file but if there is another flat file *detail) found, then we must transform that to xml and combine into the header into one xml file.

So, the standalone header xml file would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 1 info....</field2>
</row>
</rows>
</header>
</tables>
<document>

and if a detail file was found, then the xml would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 2 info....</field2>
</row>
</rows>
</header>
<detail>
<rows>
<row>
<detfield1>details file 1 info....</detfield1>
<detfield2>details file 2 info....</detfield2>
</row>
</rows>
</detail>
</tables>
<document>

Seeing how you can't easily perform any if logic in a data-flow, what would be the best way to achieve this?

JAson

Are you saying there will be more than one details file? In your example, is detfield2 really supposed to contain a value from the second detail file?

|||

sorry, I meant that there would be more than 1 filed in the details file. So,

<detfield1>details file field_1 info....</detfield1>
<detfield2>details file field_2 info....</detfield2>

The tricky part is just to figure out how to manipulate an xml file (like removing just a few end tags, then inserting another xml portion, and replace the end tags that were removed) after it has been created and written to to a destination.

|||Given that there is no "XML Destination" adapter provided with SSIS, I think you're going to have to write one yourself if your goal is to end up with an XML document. If I were in your shoes, I think I'd write a custom, managed (not script) component. I'd give it two inputs: One for the header row, and the other for the detail rows. There's a good sample in BOL that you can use to get started.

Combining XML

Assume I have the following, in T-SQL:

declare @.xml1 xml

declare @.xml2 xml

set @.xml1 = '<first></first>'

set @.xml2 = '<second></second>'

I want to combine these, into the following, and return the results typed as XML:

<results>

<first></first>

<second></second>

</results>

I can't seem to find any way to do this - other than converting them to a string, and concatinating them and then typing the results as XML. Any ideas?

Thanks,

Scott

One way to do this is to use 'FOR XML PATH'

declare @.xml1 xml

declare @.xml2 xml

set @.xml1 = '<first></first>'

set @.xml2 = '<second></second>'

select @.xml1 , @.xml2
FOR XML PATH ('results'), type

The section 'Constructing XML Using FOR XML ' in BOL has more information about the different FOR XML capabilities.

Thanks

Babu

|||

Beautiful!!!

Thank you Babu!

Combining Values

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

And combine them into this:

John Doe | Address 1; Address 2; Address 3

Do I need a cursor for this?imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
>
And combine them into this:
>
John Doe | Address 1; Address 2; Address 3
>
>
Do I need a cursor for this?

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.|||I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

>

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

>

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Do I need a cursor for this?

>
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -
>
- Show quoted text -

|||If you cant do this in reports, refer this

http://sqljunkies.com/WebLog/amacha...px?Pending=true
Madhivanan

On Jun 30, 10:47 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Do I need a cursor for this?

>

Quote:

Originally Posted by

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

|||imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

You have three options:

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

Option 1) is the safest method.

For option 2) details refer to:
http://groups.google.pl/group/micro...cab9fecb969f34/
Third method:
http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info|||Here's where things get interesting:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

The results need to be

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

I have no idea how to pull this off.

On Jun 30, 6:39 am, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>
You have three options:
>
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Option 1) is the safest method.
>
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info

|||On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2
>
The results need to be
>
John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2
>
I have no idea how to pull this off.
>
On Jun 30, 6:39 am, "Marcin A. Guzowski"
>
>
>
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

>

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info- Hide quoted text -

>
- Show quoted text -

You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.|||Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?

On Jul 2, 3:28 am, Stephen2 <Step...@.mailinator.comwrote:

Quote:

Originally Posted by

On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
>
>
>
>
>
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:

>

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

>

Quote:

Originally Posted by

The results need to be

>

Quote:

Originally Posted by

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

>

Quote:

Originally Posted by

I have no idea how to pull this off.

>

Quote:

Originally Posted by

On Jun 30, 6:39 am, "Marcin A. Guzowski"

>

Quote:

Originally Posted by

<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info-Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.- Hide quoted text -
>
- Show quoted text -

sqlsql

combining two tables with a full-text search

I had a table that was terribly in need of normalisation that I have now
split into two tables. The table contains three "similar" fields that I
were previously indexed using a full-text query. I now need to do the query
twice as subqueries, UNION the results, then order by the calculated rank.
Problem is - the rank appears to be independent between the two tables, to
the results are coming up with one query always being higher than the other.
Is there any way to pre-select or cap the rank value, or another way to
search these two tables so they're more "combined"?
Thanks in advance,
Duncan
Probably not as the rank is generated on a per table basis.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <dunc@.ntpcl.f9.co.uk> wrote in message
news:Ouh0hapaFHA.2496@.TK2MSFTNGP14.phx.gbl...
> I had a table that was terribly in need of normalisation that I have now
> split into two tables. The table contains three "similar" fields that I
> were previously indexed using a full-text query. I now need to do the
query
> twice as subqueries, UNION the results, then order by the calculated rank.
> Problem is - the rank appears to be independent between the two tables, to
> the results are coming up with one query always being higher than the
other.
> Is there any way to pre-select or cap the rank value, or another way to
> search these two tables so they're more "combined"?
> Thanks in advance,
> Duncan
>
|||One solution, that wouldn't be the best but would work, would be to
create a third table containing all the fields you wish to index with
the primary key associated with it, then index that table instead.

Combining Two Tables Via T-SQL

Hello,

I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.

Thank you for your help!

UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:

Code Snippet

USE Northwind
GO

SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees

CompanyName
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Bill Smith
...

|||

Donnie:

The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:

The number of columns the same The datatypes of corresponding columns be the same|||

Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".

declare @.t1 table(c1 int, c2 int)

declare @.t2 table(c3 int, c4 int)

insertinto @.t1 values(1, 2)

insertinto @.t2 values(3, 4)

select c1, c2 from @.t1

union all

select c3, c4 from @.t2

AMB

|||

Are you certain that it is a UNION that you need to perform, and not a JOIN?

A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1a Column2a Column3a Column1b Column2b Column3b

--

1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1

2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2

3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3

SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b

A UNION will allow you to horizontally merge the data from both tables, like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1 Column2 Column3

-

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3

FROM Table1 t1

UNION ALL

SELECT t2.Column1b,
t2.Column2b,
t2.Column3b

FROM Table2 t2

Chris|||

Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?

Thanks for your help!

Table 1 - Sample Data Column1a Column2a Column3a 1 T1C2R1 T1C3R1 2 T1C2R2 T1C3R2 3 T1C2R3 T1C3R3 5 T1C2R5 T1C3R5 Table 2 - Sample Data Column1b Column2b Column3b 4 T2C2R4 T2C3R4 2 T2C2R2 T2C3R2 3 T2C2R3 T2C3R3 6 T2C2R6 T2C3R6 Ouptput: Column1a Column2a Column3a Column1b Column2b Column3b 1 T1C2R1 T1C3R1 NULL NULL NULL 2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2 3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3 NULL NULL NULL 4 T2C2R4 T2C3R4 5 T1C2R5 T1C3R5 NULL NULL NULL NULL NULL NULL 6 T2C2R6 T2C3R6

|||

Yes, a full join should work for you.

SELECT a.Column1a, a.Column2a, a.Column3a,

b.Column1b, b.Column2b, b.Column3b

FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)

There should not be any duplicates in the result set.

Combining two tables to make a third

Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:

>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns from e
ach table instead of using [*],
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mngong@.gmail.com> wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com
..
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
>
> Your help is appreciated have been on this
>