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

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 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 DELETE and JOIN statements

In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:

DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL

SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?

GerritYou could try with a NOT IN.

DELETE FROM GroupsMembers WHERE GroupID NOT IN (SELECT ID FROM Groups)
|||Thanks, that does seem to do the trick.

Gerrit

Combining 3 datasource controls with t-sql

Hello everyone,

I'm trying to get a count of 3 different types on the same field. For Example, let's use Gender as the field with these options: Male, Female, Not Given. What I'm wanting to do is retrieve a count for each type. What I have so far is: SELECT COUNT(Gender) WHERE Gender = 'Male' and I have to duplicate this in 3 different data controls. I would like, however, to have one datasource control with a statement along the lines of:

SELECT ( SELECT COUNT(Gender) FROM Users WHERE Gender='Male), SELECT COUNT(Gender) FROM Users WHERE Gender='Female', SELECT COUNT(Gender) WHERE Gender='NotGiven' )

From Users

Or something to that effect. Any suggestions?

Thank you greatly for your help,

Mark

SELECTSUM(CASEWHEN Gender='Male'THEN 1ELSE 0END)as MaleCount,

SUM(CASEWHEN Gender='Female'THEN 1ELSE 0END)as FemaleCount,

SUM(CASEWHEN Gender='NotGiven'Or GenderISNULLTHEN 1ELSE 0END)as NotGivenCount

FROM Users

|||

This ended up working for me: SELECT Count_1 = (SELECT COUNT(Gender) FROM User WHERE Gender='male'), Count_2 = (SELECT COUNT(Gender) FROM Users WHERE Gender='female') and then access Count_1 and Count_2

Thanks for your help.. I will mark yours as the answer because it looks like it would work too.

sqlsql

combine table data fields from two records into a record

Dear helper,

I have the question of T-SQL.

I have a table original is:

and want to use Sql to make it becomes:

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 2.24 kg Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Water-reducing Admixture

It is better to make it becomes a view for table joining.

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding, Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding, Water-reducing Admixture

Regards,

Man Pak Hong, Dave

try this..

SELECT a.Concrete_Grade
, a.Mix_Code
, a.RM_ID
, a.RM_Name
, a.RM_Value
, a.UnitType_ID
, a.RMTypeType_Name
, b.RMType_Name
FROM YourTable a INNER JOIN
YourTable b ON a.Concrete_Grade = b.Concrete_Grade
AND a.Mix_Code = b.Mix_Code
AND a.RM_ID = b.RM_ID
AND a.RM_Name = b.RM_Name
AND a.RM_Value = b.RM_Value
AND a.UnitType_ID = b.UnitType_ID|||

So your point is creating view which holds the values [RMTypeType_Name]='Water-reducing', lets say vw_MyData_WaterReducing. Later you want to join this view with outher tables on your query.

One way it is good if you use INDEXED VIEW. You have to create a index on this new view. It will increase the performance well.

But if you try to use with out index (only the filtered query), it may decrease the performance. You may unknowingly use Self join on your query...

To know better abotu indexed view visit here ... http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Columns & rowns interchage on string the data

Hi,

Is it possible to interchange column & row data from the t-sql on
storing into a file?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***Simon,

Thanks for your reply. I woule like the following output data

col1 col2 col3 ......

row1 r1c1 r1c2 c1c3 ...

row2 r2c1 r2c2 r2c3 ....

row3 r3c1 r3c2 r3c3 ......

..
..
..

to be output like

row1 row2 row3 ......

col1 r1c1 r2c1 r3c1

col2 r1c2 r2c2 r3c2

col3 r1c3 r2c3 r3c3
..
..
..

Thanks

*** Sent via Developersdex http://www.developersdex.com ***|||I'm not sure I understand your question, but I think you're asking if
you can export a crosstab report to a file? If so, a reporting tool is
probably the best general solution (MSSQL Reporting Services, Business
Objects etc), but you could create your own qrosstab queries:

http://www.aspfaq.com/show.asp?id=2462

To export the results to a file, you could use osql.exe, bcp.exe or
DTS.

Simon|||Here's an example:

CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, x INTEGER NOT NULL, y
INTEGER NOT NULL, z INTEGER NOT NULL) ;

INSERT INTO foo (foo_key, x, y, z)
SELECT 1,10,11,12 UNION ALL
SELECT 2,20,21,22 UNION ALL
SELECT 3,30,31,32 ;

SELECT foo_key, x, y, z
FROM foo ;

SELECT 'x' AS col,
MAX(CASE WHEN foo_key = 1 THEN x END) AS row1,
MAX(CASE WHEN foo_key = 2 THEN x END) AS row2,
MAX(CASE WHEN foo_key = 3 THEN x END) AS row3
FROM foo
UNION ALL
SELECT 'y',
MAX(CASE WHEN foo_key = 1 THEN y END),
MAX(CASE WHEN foo_key = 2 THEN y END),
MAX(CASE WHEN foo_key = 3 THEN y END)
FROM foo
UNION ALL
SELECT 'z',
MAX(CASE WHEN foo_key = 1 THEN z END),
MAX(CASE WHEN foo_key = 2 THEN z END),
MAX(CASE WHEN foo_key = 3 THEN z END)
FROM foo ;

--
David Portas
SQL Server MVP
--

column names, table name, user name

Hi;
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.

column as variable

I have a problem that I'm sure is very simple to answer for anyone that knows a bit of T-SQL. In a stored procedure, I simply want to concatenate a string variable containing a column name into a Select statement.

For example:
I want to execute the following statement but using a variable for the column name:

Select * from tblmet1araw where JulianDay = 1

JulianDay is an integer
This is how I have my code set up:

declare @.xxx as varchar(20)
set @.theday = 'JulianDay'

select * from tblmet1araw where @.theday = 1

I get the following error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'JulianDay' to a column of data type int.declare @.col varchar(10)
set @.col='id'
exec('select * from sysobjects where '+@.col+'=1')|||Thanks. That works.

Colour My World

In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
the QA color coding? When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white.
Thanks!>> In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code
Not sure if direct printing is possible or not. This might be silly but if
the code is small enough to fit the screen, you could use a screen print to
a word document and then print it.
Anith|||>> When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white. <<
Just for fun, you might want to research the effect of "colored code"
on maintaining it. There is a standard test for brain damage where you
show the subject a seires of flashcards with the names of colors in
colored ink (I.e. "RED" pritned in green ink) and ask them to call out
the either word or the color. This has nothing to do with being
color-blind.
Since it involves switching brain hemispheres and thus the physical
structure of the brain as a organ, the rate is fairly constant over a
person's lifetime. Unless they get some physical damage to the brain.
Strongly analytical ("left brain, right hand") people have an awful
time with "neon vomit programming tools" because they have to filter
out the colors to abstract the code from the text.
So, I have to ask, why would you ever want to do this? Get a copy of
SQL PROGRAMMING STYLE for some more info on how humans read code. I
did a bit of the work on this back in the 1980's for AIRMICS while I
was a Georgia Tech.|||Well, cut-n-paste it into MS Word - that should do the trick ;)|||Screen capture could work, but the code is many, many pages long.
Is this possible in SQL Server? If not , are there any 3rd party programs
that might work?
Thanks again...
"Anith Sen" wrote:

> Not sure if direct printing is possible or not. This might be silly but if
> the code is small enough to fit the screen, you could use a screen print t
o
> a word document and then print it.
> --
> Anith
>
>|||Nope, tried it. Still black-and-white in MS Word.
"Alexander Kuznetsov" wrote:

> Well, cut-n-paste it into MS Word - that should do the trick ;)
>|||Why would I want color-coded printouts? for the same reason they're
color-coded on the screen. Easier to separate what's code, what's keywords,
and what's comments.
As far as the brain damage research, although it sounds rather intriguing,
considering how many more years of my life I'm going to spend looking at M&M
colored typing, I may be better off not knowing...
"--CELKO--" wrote:

> including the keywords and comments come out in black and white. <<
> Just for fun, you might want to research the effect of "colored code"
> on maintaining it. There is a standard test for brain damage where you
> show the subject a seires of flashcards with the names of colors in
> colored ink (I.e. "RED" pritned in green ink) and ask them to call out
> the either word or the color. This has nothing to do with being
> color-blind.
> Since it involves switching brain hemispheres and thus the physical
> structure of the brain as a organ, the rate is fairly constant over a
> person's lifetime. Unless they get some physical damage to the brain.
>
> Strongly analytical ("left brain, right hand") people have an awful
> time with "neon vomit programming tools" because they have to filter
> out the colors to abstract the code from the text.
> So, I have to ask, why would you ever want to do this? Get a copy of
> SQL PROGRAMMING STYLE for some more info on how humans read code. I
> did a bit of the work on this back in the 1980's for AIRMICS while I
> was a Georgia Tech.
>|||On Fri, 10 Feb 2006 08:51:29 -0800, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
>the QA color coding? When I print, even to a color laser printer, everythin
g
>including the keywords and comments come out in black and white.
>Thanks!
I use Macromedia's Homesite. You can set the colors for each entity.
What you see on the screen is what prints. I also use Ultra Edit and
Slick Edit. Both of these allow you to print a selection where
Homesite will only print the entire file. I like the printout from
Homesite best.
--
BettyB -- www.flamingo-code.com
"I have noticed even people who claim everything is
predestined, and that we can do nothing to change it,
look before they cross the road." - Stephen Hawking|||I don't know if it is an option or not, but the 2005 replacement of QA (Mana
gement Studio) does keep
formatting (including colour) when you copy text. Personally, I hate that, b
ut it might be an upside
in these situations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:F6077A67-C933-47CA-A4C5-654D2E7881A8@.microsoft.com...
> Nope, tried it. Still black-and-white in MS Word.
> "Alexander Kuznetsov" wrote:
>|||> Personally, I hate that,
me too. Sometimes I need to paste into notepad - that removes colors
and fonts