Creating XML Document from SQL Server

Would anyone please help me , how to create XML document
for a specific record from the SQL Server database.
Is there any builtin tool available. If so, how to access
it.
Would I need to create a program to generate XML
document. If so, could you suggest the development
package/code examples...
Appreciate the info.
Would something along the line of
select * from T for xml auto
or
select * from T for xml raw
help?
Best regards
Michael
"Sat" <anonymous@.discussions.microsoft.com> wrote in message
news:442c01c42b8b$f0669640$a001280a@.phx.gbl...
> Would anyone please help me , how to create XML document
> for a specific record from the SQL Server database.
> Is there any builtin tool available. If so, how to access
> it.
> Would I need to create a program to generate XML
> document. If so, could you suggest the development
> package/code examples...
> Appreciate the info.

Creating View

I have a database I am working with that has three tables in it.
Two of the tables will be filled with information once a record is
submitted. The other one requires a response and will only get populated with
information when another transaction is fired.
(i.e. Record Submission sends an e-mail with an html link. When the link is
clicked by a user it loads an ASPX page that will fire a stored procedure
that creates a record in the third table that identifies the submission has
been viewed by someone on staff)
The problem I am running into with the view is that it will only retun
records that have been 'viewed' by someone. How can I do the view where it
will return all records regardless if a record exist in the child table?
The Table is joined with a PK and FK relationship.
If no PK from the master table exist as a FK in the child table I do not get
the reults.
I want to be able to see all records regardless if they have been viewed are
not. Can this be done?
IronSights,
You are asking about an OUTER JOIN such as:
CREATE MyView
AS
SELECT A.*, B.*, C.* -- Specify column names in real life
FROM Table1 A
JOIN Table2 B
ON A.ID = B.ID
LEFT OUTER JOIN Table3 C
ON A.ID = C.ID
Of course, the C.* columns will return NULL since no data exists, but the
row will show up in your view.
RLF
"IronSights" <IronSights@.discussions.microsoft.com> wrote in message
news:CDAFBEE7-2D51-4121-9CEA-9D70A873815D@.microsoft.com...
>I have a database I am working with that has three tables in it.
> Two of the tables will be filled with information once a record is
> submitted. The other one requires a response and will only get populated
> with
> information when another transaction is fired.
> (i.e. Record Submission sends an e-mail with an html link. When the link
> is
> clicked by a user it loads an ASPX page that will fire a stored procedure
> that creates a record in the third table that identifies the submission
> has
> been viewed by someone on staff)
> The problem I am running into with the view is that it will only retun
> records that have been 'viewed' by someone. How can I do the view where it
> will return all records regardless if a record exist in the child table?
> The Table is joined with a PK and FK relationship.
> If no PK from the master table exist as a FK in the child table I do not
> get
> the reults.
> I want to be able to see all records regardless if they have been viewed
> are
> not. Can this be done?
>
sql

Creating Tables with Triggers

I need an Insert trigger to create a set of tables to my database when i add
new record in the top level master table (horizontal partitioning
denormalization)..
Is this possible using trigger or procedure or even function' and how'
any help appreciated.I'd doing such things by using stored procedure rather within a trigger
Identify that row has beed added and call SP to create a table
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need an Insert trigger to create a set of tables to my database when i
>add new record in the top level master table (horizontal partitioning
>denormalization)..
> Is this possible using trigger or procedure or even function' and how'
> any help appreciated.
>|||As I said in my other post:
1) Don't multi-post.
2) This is a bad design.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need an Insert trigger to create a set of tables to my database when i
>add new record in the top level master table (horizontal partitioning
>denormalization)..
> Is this possible using trigger or procedure or even function' and how'
> any help appreciated.
>|||1) I said sorry for posting in 2 groups..
2) there was no reply to my first thread and i'm in need to help
3) Why u think it's a bad design' How gcould i design something better'
hope u could help
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uT635Kc7FHA.1000@.tk2msftngp13.phx.gbl...
> As I said in my other post:
> 1) Don't multi-post.
> 2) This is a bad design.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||How could i write a procedure to create table "Table_[ID]" Where id is a
paramter..
Also i must use trigger anyway to call this proccedure right'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS5H77b7FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I'd doing such things by using stored procedure rather within a trigger
> Identify that row has beed added and call SP to create a table
>
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||You'll have to be patient. Participation in these groups is voluntary. If
you want immediate help, then open a ticket with PSS.
As for the design, you will have a proliferation of tables, which will be
difficult to control. Why is it that you need all of these tables? Why
can't you have a single table with a column that differentiate? How about
providing a clear business spec - not a program spec?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eOhRFGd7FHA.956@.TK2MSFTNGP10.phx.gbl...
> 1) I said sorry for posting in 2 groups..
> 2) there was no reply to my first thread and i'm in need to help
> 3) Why u think it's a bad design' How gcould i design something better'
> hope u could help
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uT635Kc7FHA.1000@.tk2msftngp13.phx.gbl...
>|||Thanx for inform me about PSS ticket and sorry for multi-post.
I need to have many tables coz one table will hv more than 6,000,000 record
with 2 text fields one of them is type of "text". which mean searching is so
slow.. so partitioning tables will make each table contain 100,000 to
600,000 record which give reliable search preformance specially with joins
and relations..
The business is database of counties laws and rules, and logically one will
search laws by country, so i partition my tables depend on the the country.
What do u think' do you have better idea'
P.S.. My text is in Arabic so Full Text indexing do not help..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejgvjdd7FHA.2716@.TK2MSFTNGP11.phx.gbl...
> You'll have to be patient. Participation in these groups is voluntary.
> If you want immediate help, then open a ticket with PSS.
> As for the design, you will have a proliferation of tables, which will be
> difficult to control. Why is it that you need all of these tables? Why
> can't you have a single table with a column that differentiate? How about
> providing a clear business spec - not a program spec?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:eOhRFGd7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||SQL Server can handle tables of billions of rows. Typically, you'd place
the text in a separate table, with a FK to the parent. You can place a
Country and County FK column on the parent and index those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23gk6Brd7FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Thanx for inform me about PSS ticket and sorry for multi-post.
> I need to have many tables coz one table will hv more than 6,000,000
> record with 2 text fields one of them is type of "text". which mean
> searching is so slow.. so partitioning tables will make each table contain
> 100,000 to 600,000 record which give reliable search preformance specially
> with joins and relations..
> The business is database of counties laws and rules, and logically one
> will search laws by country, so i partition my tables depend on the the
> country.
> What do u think' do you have better idea'
> P.S.. My text is in Arabic so Full Text indexing do not help..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejgvjdd7FHA.2716@.TK2MSFTNGP11.phx.gbl...
>|||this drive to 2 big tables the parent one, and text table one.. this is the
current database structure which need 4 -5 min to search in the text..
this is time isn't acceptable so i will partitioning it to tables which cut
search time to 15 - 30 second (i tested it)
this is big improvment in time.
what do u think'
also could you plz tell me how could i use triggers to create these tables'
thanx
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiwD66d7FHA.2012@.TK2MSFTNGP14.phx.gbl...
> SQL Server can handle tables of billions of rows. Typically, you'd place
> the text in a separate table, with a FK to the parent. You can place a
> Country and County FK column on the parent and index those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:%23gk6Brd7FHA.2432@.TK2MSFTNGP10.phx.gbl...
>|||> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:%23gWgQfe7FHA.1416@.TK2MSFTNGP09.phx.gbl...

> this drive to 2 big tables the parent one, and text table one.. this is
> the current database structure which need 4 -5 min to search in the text..
> this is time isn't acceptable so i will partitioning it to tables which
> cut search time to 15 - 30 second (i tested it)
Correct indexing will serve you much better than partitioning the table.
Create an index on whatever attribute you would otherwise partitrion the
table by and search on that as well as your other criteria. If "search in
the text" means a free text search then also take a look at the "Full-text
indexing" topics in Books Online. If you look at your earlier thread on this
topic I also made some other suggestions.
David Portas
SQL Server MVP
--

Creating Queries

Is is possible to create a query to do something like
this:
For each "Record" from {SELECT field1 FROM table1}
SELECT * FROM table2
WHERE field2='Record'
next "Record"You can use a cursor to do so, but a regular query joining table1 to
table2 using field1 = field2 is the same thing.
What are you trying to accomplish?
Simon Worth
JLong wrote:
> Is is possible to create a query to do something like
> this:
> For each "Record" from {SELECT field1 FROM table1}
> SELECT * FROM table2
> WHERE field2='Record'
> next "Record"|||I think you mean something like this:
SELECT *
FROM Table2
WHERE EXISTS
(SELECT *
FROM Table1
WHERE Table1.field1 = Table2.field2)
Note that when discussing RDBMSs it's often considered more correct to
use the terms "Row" and "Column" in preference to "Record" and "Field".
Some people quite comfortably use these words interchangeably, while
others attach important conceptual differences to them and insist that
"records" and "fields" have no place in a relational database. If you
don't know or care about the difference then I suggest you make the
effort to use the least controversial terms (Rows and Columns) when
posting, if only in the interests of a quiet life ;-)
David Portas
SQL Server MVP
--|||Thank you David for your comments, I really do care about
RDBMS's terminology. Thank you for your suggestion I
will try it tomorrow morning at work and will let you
know how it turned out. As for Simon's question, I have a
query that return a calculations based on a variable
(row/record), I am running the query on the SQL query
analyzer. I was trying to see if I could pass the record
as a query, so I didn't have to change the value by hand.

>--Original Message--
>I think you mean something like this:
>SELECT *
> FROM Table2
> WHERE EXISTS
> (SELECT *
> FROM Table1
> WHERE Table1.field1 = Table2.field2)
>Note that when discussing RDBMSs it's often considered
more correct to
>use the terms "Row" and "Column" in preference
to "Record" and "Field".
>Some people quite comfortably use these words
interchangeably, while
>others attach important conceptual differences to them
and insist that
>"records" and "fields" have no place in a relational
database. If you
>don't know or care about the difference then I suggest
you make the
>effort to use the least controversial terms (Rows and
Columns) when
>posting, if only in the interests of a quiet life ;-)
>--
>David Portas
>SQL Server MVP
>--
>.
>|||Did my suggestion work? If not and you need more help, please read the
following article which explains the best way to desribe your problem
here.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I'm still having problems. I guess that I need to declare
a variable to hold the rows from the first select query
and then pass it to the second select query to return what
I want. In visual basic, it would be a loop like this
For each row in table1
select * from (select column1 from table2 where
coloum2='row') as table
next row
I don't know how to do it or if it could be done.
Basically you return a column from a table and you pass
every row to another query to perform calculations for
each row.
Any idias?

>--Original Message--
>Did my suggestion work? If not and you need more help,
please read the
>following article which explains the best way to desribe
your problem
>here.
>http://www.aspfaq.com/etiquette.asp?id=5006
>--
>David Portas
>SQL Server MVP
>--
>.
>|||"I'm still having problems" doesn't tell me a thing! Did you try out
the query I posted? Did you get an error message? If the result wasn't
what you expected then what was wrong? The query I posted should do
exactly what you want as I understand it: For each row in Table1 return
the matching rows from Table2 where field2 = field1.
I previously posted an excellent article that explains how to describe
your problem for the group, so please try to follow that advice and
post DDL, sample data and show your required end result. You'll find
you get a helpful answer much quicker that way.
David Portas
SQL Server MVP
--

Creating one flat file per record in the data flow

I have a table that holds in each record an image (varbinary(max) actually), a text reference for the image and a MIME type for the image. I need to read this table and for each record that has been created since the last run, I need to create a file with the image as the content, the mime type as the file extension and the text reference as the file name. There will be one file created per record found by the data flow source.

I was assuming that I could use the flat file destination and manipulate the file naming using the contents of each record in the flow but am completely stumped on how to achieve this.

Does anyone have any ideas?

thanks

Have a look at the export column transform.|||cheers, it worked great.....