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 two seperate tables into one

Hello!
I don`t know how to do some query. I have two tables which looks like
it:
First table:
MRPC 200504 200505 200506
C01 1 2 3
C02 2 3 4
C03 3 3 2
Second table:
MRPC 200504 200505 2000506
C01 20% 20% 50%
C02 10% 30% 70%
C03 30% 40% 15%
I would like to combine these two tables into one table, which would
look like it:
MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
C01 1 20% 2 20% 3 50%
C02 2 10% 3 30% 4 70%
C03 3 30% 3 40% 2 15%
The number of columns is changeable, because once a w there is extra
column added. Is it possible to link these two tables and create one
score table? As you can see the second table has the same columns as
first one and don`t have a string "PRC" in the name of column.
Thank you for your help
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***>> Is it possible to link these two tables and create one score table?
Yes it is possible, but unless you are working towards achieving some
performance benefits (for instance, by materializing data) for specific
queries, such an attempt is of little use. You can always derive the
resultset using a simple JOIN -- in many cases a view should be the
solution.
Keeping them separate, on the other hand, allows you to manipulate data in
each table separately without affecting the other.
It is not a show-stopper, just the matter of aliasing the column names.
Anith|||On Fri, 05 Aug 2005 09:05:05 -0700, Marcin Zmyslowski wrote:

>
>Hello!
>I don`t know how to do some query. I have two tables which looks like
>it:
>First table:
>MRPC 200504 200505 200506
>C01 1 2 3
>C02 2 3 4
>C03 3 3 2
>Second table:
>MRPC 200504 200505 2000506
>C01 20% 20% 50%
>C02 10% 30% 70%
>C03 30% 40% 15%
>I would like to combine these two tables into one table, which would
>look like it:
>MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
>C01 1 20% 2 20% 3 50%
>C02 2 10% 3 30% 4 70%
>C03 3 30% 3 40% 2 15%
>
>The number of columns is changeable, because once a w there is extra
>column added. Is it possible to link these two tables and create one
>score table? As you can see the second table has the same columns as
>first one and don`t have a string "PRC" in the name of column.
Hi Marcin,
Instead of adding columns to your tables for each w, why not add one
column to the table to hold the w, then just add rows?
The first table would look like this:
MRPC W Data
C01 200504 1
C01 200505 2
C01 200506 3
C02 200504 2
C02 200505 3
C02 200506 4
C03 200504 3
C03 200505 3
C03 200506 2
The second table would be similar. Depending on actual business
requirements, it might also be possible to combine these two tables:
MRPC W Data PRC
C01 200504 1 20%
C01 200505 2 20%
C01 200506 3 50%
C02 200504 2 10%
C02 200505 3 30%
C02 200506 4 70%
C03 200504 3 30%
C03 200505 3 40%
C03 200506 2 15%
(BTW, what datatype do you use for the percentages?)
Tables with a seperate column for each w/month/year/whatever tend to
bring lots of probles and no gain.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi!
I could have data in rows, but I can only do a crosstab query which let
me create one columns (wk), not two columns at the same time: "wk" and
"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
still don`t know how to combine these two tables into one. Could you
give me a code example' I cannot find it in archieve. I would be very
grateful for help.
Thanx, Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 08 Aug 2005 00:54:37 -0700, Marcin Zmyslowski wrote:

>Hi!
>I could have data in rows, but I can only do a crosstab query which let
>me create one columns (wk), not two columns at the same time: "wk" and
>"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
>still don`t know how to combine these two tables into one. Could you
>give me a code example' I cannot find it in archieve. I would be very
>grateful for help.
>Thanx, Marcin from Poland
Hi Marcin,
Doing a cross tab is actually better handled by the presentation tier.
But if there is no way that the client can handle this and you must do
it server side, use something like this untested code:
SELECT MRPC,
MAX(CASE WHEN W = '200504' THEN Data END) AS '200504',
MAX(CASE WHEN W = '200504' THEN PRC END) AS '200504 PRC',
MAX(CASE WHEN W = '200505' THEN Data END) AS '200505',
MAX(CASE WHEN W = '200505' THEN PRC END) AS '200505 PRC'
FROM YourTable
GROUP BY MRPC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Combining or Concating seperate fields for date?

I have two tables in a SQL db.
Each has 3 separate fields used to store a date info:

lastservicemonth tinyint1
lastserviceday tinyint1
lastserviceyear smallint2
(and these fields can be nulls)

I want to compare the date info in Table A vs. Table B and find the latest date between the two.

I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates.
But ths far I have been unsuccessful.

Any help would be greatly appreciated!To get the latest row, use:SELECT TOP 1 *
FROM [Table A]
ORDER BY lastserviceyear, lastservicemonth, lastserviceday-PatP|||Thanks Pat - I can can see how my question was unclear. Hope this clarifies.

Say Table A and Table B both contain the same person records for when they last came in the hospital. But the tables contain different dates.

Example:
Table A - ID #123, John Doe , 11-1-2007
Table B - ID#123, John Doe, 12-3-2007

I want to update the date fields in Table A with the data in Table B, but only if the date in Table B is more recent than the date in Table A.

So, I need to compare the dates for each person in Table A to the same person in Table and determine which visit date is more recent.

Hope this makes more sense.|||Why are two tables storing such similar information?

If you give us the real problem, it might also be easier to decipher than "table A and table B..."|||While I understand the desire to "simplify" a problem for posting purposes, the process usually infuriates me... All too often critical pieces of information get "simplified" out of the example that gets posted!

Can you post at least the DDL for the tables (in other words the CREATE TABLE statements needed to recreate them), and whatever attempt you've made so far to do what you want? This would help us a lot in determining what you need.

-PatP|||I appreciate your feedback. I can see I need to clarify.
Table A and B are in different dbs connected to different apps.
The apps communicate with each other imperfectly, so the dates get out of synch.

I imported the id# and the day, month and year column from System B, Table B to system A.
Now I want to update Table A with that data.

Here's a select statement where I attempt to identify discrepancies in the two data sets. If I can correct this , I can do an update statement. The statement below adds the 3 date fields and arrives a a number rather than a date. Do the fields need to be converted from smallint and tinyint ?

SELECT
(p.lastserviceYEAR +'-'+ p.lastserviceMONTH +'-'+ p.lastserviceday) as pdate,
(e.lastserviceYEAR +'-'+ e.lastserviceMONTH +'-'+ e.lastserviceday) as edate
From patient p Inner Join empi e On (p.ID = e.ID)
where edate > pdate|||I'd use something like:Cast(1000 * lastserviceyear + 100 * lastservicemonth + lastserviceday AS INT)to get integer values that you can safely compare and sort... They aren't pretty to print, but they work well for comparisons and sorting.

-PatP|||I was going to suggest the use of DateAdd(), but I think that will be far more efficient.|||last time it was me dropping a zero, pat, this time it's you ;)

Cast(10000 * ...|||Excellent. That will do the trick.
Thanks to all for your help and patience!

Combine tables from previous db to new db (uhh...?)

I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.

My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?

Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6

New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?you can use a cursor...and loop through each record...get the values into variables and insert into the 2 tables...

your products_attributes table can be somthing like

ID,Attr1, Attr2, Attr3, Attr4, Attr5, Attr6.... you can have the product name too if you need to..

HTH|||could you post a link or some example code for doing this? Thanks!|||sorry i misread your post...did u mean move data from one table in a DB to two tables in ANOTHER db ? or is this all within the same db ? if you are moving from one DB to another,then it wud be easier to move the entire table into the new db first and then separate the data into two tables inside the new db...

HTH|||from one source db to a different target db, then split into two tables at the target db.|||there are prbly many ways to do this...you can do the export/import wizard to transfer the table to the target db and then use tht table to split it..thts when you can use cursors to go through each row and insert the values into diff tables...

HTH|||Any links for cursor samples you're referring to? I haven't used them before.

Combine multiple sql calls into 1

I have an old app that I'm trying to recode and improve performance.

From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.

Here is a quick example of the script

Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE

Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE

Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE

INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'

These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-ScottCheck out the INSERT ... EXECUTE(' ') syntax in BOL.

Combine 2 Dataset

Is that possible to do that'
Coz I use BSM to export a scorecard to RS and It becomes 2 seperate dataset.
I just wanna know if it's possible to combine those 2 dataset easily?/
thanksCheck out subreports. That is really the only way to combine (or seem to
combine) datasets. You can put a subreport is the detail line of a report if
you need to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hei" <Hei@.discussions.microsoft.com> wrote in message
news:A994E1E3-C7CA-4AB2-AD95-BA5CAE193AEE@.microsoft.com...
> Is that possible to do that'
> Coz I use BSM to export a scorecard to RS and It becomes 2 seperate
> dataset.
> I just wanna know if it's possible to combine those 2 dataset easily?/
> thanks
>