Generating XML based on Schema

Hi,

i need Info regarding this

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

Can i find any article on how to do this.

Thanks,

Vaishu

How about using "FOR XML AUTO" in your select!

select * from tbl1 FOR XML AUTO

Generating an audit trail in SQLS 2000

Hi,
Following on from a recent post regarding Identity fields with David Portas,
I was discussing a few issues with a collegue and an interesting issue was
raised.
Namely, creating an audit trail in which every row is assigned a unique,
gapless sequential reference. For example, our accountancy package (Sage)
has an audit trail in which every transaction has a numbered reference. Each
number is unique and there are no missing numbers. Obviously some
transactions are cancelled, others may not be assigned in exactly the order
that they were created, etc etc.
I'm just wondering how I would achieve such a thing in SQLS. From talking
with David Portas in my last thread I understand that IDENTITY is not the
way ot achieve this, but I'm curious as to how exactly to achieve this in a
multi-user concurrent environment?
For example, have a unique ID table in which numbers are assigned as they
are required - although I imagine that this would require extensive use of
transactions and locking...
Alternatively would be to have an ON INSERT trigger which simply assigns the
last row reference plus one to the reference field?
Any pointers / sites / etc?
I appreciate that this is a big vague but it's something that I can see
being very useful in the near future.
Any and all advice is gratefully received.
Regards
Chris.One possible solution is at
http://solidqualitylearning.com/blo...04/04/446.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Chris Strug" <hotmail@.solace1884.com> wrote in message
news:%23r5dC30PFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Following on from a recent post regarding Identity fields with David
Portas,
> I was discussing a few issues with a collegue and an interesting issue was
> raised.
> Namely, creating an audit trail in which every row is assigned a unique,
> gapless sequential reference. For example, our accountancy package (Sage)
> has an audit trail in which every transaction has a numbered reference.
Each
> number is unique and there are no missing numbers. Obviously some
> transactions are cancelled, others may not be assigned in exactly the
order
> that they were created, etc etc.
> I'm just wondering how I would achieve such a thing in SQLS. From talking
> with David Portas in my last thread I understand that IDENTITY is not the
> way ot achieve this, but I'm curious as to how exactly to achieve this in
a
> multi-user concurrent environment?
> For example, have a unique ID table in which numbers are assigned as they
> are required - although I imagine that this would require extensive use of
> transactions and locking...
> Alternatively would be to have an ON INSERT trigger which simply assigns
the
> last row reference plus one to the reference field?
> Any pointers / sites / etc?
> I appreciate that this is a big vague but it's something that I can see
> being very useful in the near future.
> Any and all advice is gratefully received.
> Regards
> Chris.
>
>|||If you don't mind having gaps in numbers i.e reserve IDs before you
write. Sticking the next available ID in a one row table is good
performance wise. You only lock one very small table rather than the
table you are inserting data into.
The processes that will write can take their time because they have
their unique IDs already and so don't stop other writes reserving
their IDs. Also you can reserve 100 IDs very easily at no cost just by
adding 100.
But you will get gaps, after reserving ID if your write fails.

Generate Script and Insert Data

I have a question regarding generating SQL script from a database.If you use SQL Server Management Express, it lets you generate SQLscript that can be used to recreate table structure, stored procedures,etc. But it does not include the actual data stored in the tables.

Is it possible to generate SQL to *INSERT* data as well? If yes, then how?

You can get a template query for inserting data to 1 table by right clicking the table in Object Explorer->choose Script Table As->INSERT To. However there is no such generated script to do actual INSERTs and it is not necessary, because there are serveral easy way to transfer data into the new table, for example you can just use such INSERT command:

INSERT INTO newTable SELECT * FROM oldTable

|||

Here is software that does exactly what you want:http://www.denovations.com/products/sqlpopulator/

I'm using it by myself while creating database installation script and should say this solution matched all my requirements.