Foreign Key and Nullable column.

Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--

foreign key - relationship

I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)

I created a column in my details table with with ID of the primary key in the master table.

The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.

Not sure why the primary key ID isn't inserted as a foreign key into my details table?

Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?

ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.

Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?

Thanks for your help! I appreciate it.
-Dman100-

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>> How do I find out Foreign keys associated with a table?
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>> Thank you,
>> Lin
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>> How do I find out Foreign keys associated with a table?
>http://www.aspfaq.com/2520
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>sql

foreign key

Hi

is it a good practice to create FK on each & every column in a table whose values are taken from master table.

should I create or not.

If I create will that be heavy i.e what about memory consumption ?

pls reply

Thanks

Shubhangi1. You can foreign keys.

or

2. You can check through code. By using Inner join table name
on parenttabel.field=childtable.field

ro

3.Before inserting a record in child table, check whether it exists in the parent table through code by using if exists or if (select count(*) from tablename where
...)=0

or

if exists(select 1 from tablename where ...)

Foreign Key

how can i create a foreign key which spans on 3 columns.

Quote:

Originally Posted by NaimishGohil

how can i create a foreign key which spans on 3 columns.

Can u explain your problem a bit more. and can u provide some structure that u want to create.

Mandy

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

sql

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin
> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520

> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)
|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>
|||Greg,
Thank you very much, this really helps!
-Lin

>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>
>.
>
|||Aaron,
Thank you very much!
-Lin

>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520

> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin

>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
>
>.
>|||Aaron,
Thank you very much!
-Lin

>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>

Foreign And Composite Keys

Hi.

Could somebody please explain to me how to create a foreign key from a table that
has two composite keys? I have a table, UserPrecedence, with two composite keys -
up_owner, owner_userID. I have a second table, Users, that has the primary key
"emailAddress" and a userID table to which all other tables point their FK columns.

http://i103.photobucket.com/albums/m156/pbd22/Keys.jpg

I want the userID column in Users to be the FK of the owner_userID column in UserPrecedence
but the Modify Table view complains that "both sides of the relationship must have the
same number of columns" when I try to create the relationship.

I am guessing this is because its a composite primary key. Can somebody explain
to me how this is done correctly (and why)?

I appreciate your help.
Thanks.

If you want to reference the "UserPrecedence" table from the "Users" table according to the present key structure of the "UserPrecedence" table you must also include the "up_order" column in the "Users" table. However, there might be other possibilities.

First, is the "owner_userID" column of the "UserPrecedence" table a unique column? If so, you might consider changing your primary key to this column.

|||
hi, thanks.

yes, owner_userID is unique but I need to keep my PK in UserPrecedence as a composite key on (up_order, owner_userID). The foreign key points from this table (source) to Users_userID (destination). Does this mean that I always have to add the composite PK column (I have many tables like this) that is not in the Users table to create the FK relationship?

thanks for your help.

|||

If your owner_userID field is unique, then you can do the following (which may not be best practice but it is an option):

You can alter your table and add a UNIQUE constraint on the owner_userID field

You can use then use the owner_userID field as a foreign key reference from other tables because you have designated it as an alternate key

The syntax for declaring the alternate MUST include explicit references

|||
Thanks for your help.

I am getting the following error:

"There are no primary or candidate keys in the referenced table 'precedence' that match the referencing column list in the foreign key 'FK_users_precedence'.

(Not to confuse you but I have made some naming changes to my tables - I am trying to sync up with the ISO-11179 rules. UserPrecedence is now 'precedence'. emailAddress is now email).

Below are the two tables in question:

DBO.users:

CREATE TABLE [dbo].[users](
[registerdate] [datetime] NOT NULL,
[password] [varchar](50) NOT NULL,
[role] [char](50) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[alternateemail] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[userid] [int] IDENTITY(1,1) NOT NULL,
[gender] [char](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[editdate] [datetime] NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--

DBO.precedence

CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]|||OK.

A little further along...

It seems I didn't create the UNIQE constraint on userid (since userid is a composite primary key and is,
therefore, UNIQUE, is it redundant to explicitly create a UNIQUE constraint on this column?). After I
did this, I ran the following code:

Code Snippet

ALTER TABLE users
ADD CONSTRAINT fk_users_precedence
FOREIGN KEY (userid)
REFERENCES precedence(userid)

and got the following error:

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_users_precedence". The conflict occurred in database "MyDB", table "dbo.precedence", column 'userid'."

here is my updated precedence CREATE script:

Code Snippet

CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_userid] UNIQUE NONCLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

|||
OK. For anybody that is curious about this error and wants to see some possible solutions (if you
have a similar problem) the below thread provided a solution for me:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478256&SiteID=1

Thanks.

Forecast Model

Hi

I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.

Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.

Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this

Thanks

AY

You should probably do some upfront data preparation before you build your time series model. Do you want to do a daily sales forecast per product? In that case, you should aggregate sales figures per item at that level.

If you have data for multiple products, then you have a series for each one and each series needs to have values for all time slices present in the data. For example, if you have daily data, you need to have a sales figure for each product for each day and all the series need to begin/end at the same point. The error you're seeing is due to this issue. Specifying MISSING_VALUE_SUBSTITUTION will allow missing data points for a time slice across multiple series to be filled in with the specified value.

|||Thanks for the reply. Yes it is a Daily Sales Forecast that I am trying to build. I will try your suggestion

ForEach file enumeration with bulk insert problem

OK, a new package, with a Foreach container enumerating CSV files in a directory.

I create the container pointing it at the directory and retrieving the fully qualified name, and create a variable (called 'CSVFiles') with a package scope, but no value.

Inside the container is a bulk insert task. The destination db/table is set, and the input flat file connection manager for the CSV files is defined with the connection string set to the variable created above.

As it iterates through the files, the variable is correctly set to the next file in the directory (I put a message box in the stream to display the file name/variable). It resembles 'C:\temp\Location1.csv'.

But when it gets to the bulk insert, I get this error message:

[Bulk Insert Task] Error: The specified connection "CSVFiles" is either not valid, or points to an invalid object. To continue, specify a valid connection.

What's going on here? Can I not use a bulk insert task in the container? Or some other parameter needs to be set?

SQL Server 9.00.3159

You have to use a file connection instead of the variable.

HTH.

|||

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

|||

Kevin6 wrote:

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

You'd have to put the variable in the expression editor for the property "ConnectionString." Right click on the connection manager object and select properties. Scroll down to find "Expressions." Click the ellipsis and select ConnectionString. There is where you put the variable name.

Re: Execute SQL Task
Make sure that the variable is of package-level scope and that the Execute SQL Task can see it.
Then build a SQL statement like this:
insert into auditTable values (1,"Testing", ?)

Then click on the parameter mapping tab, click Add, and select the variable in the variable name column. Then use zero (0) for the parameter name. Change the data type to "VARCHAR". If you have another parameter, do the same, but use a one (1) in the parameter name box.

Force View || Create View Accessing view that has not been created yet?

Hello All.

Does Sql 2005 support forced-later-compliation? Which is to say, can I create a view that access another view which has not been created yet? e.g. "Create Force View Foo" in Oracle.

Thanks,

Steve

You can only do that for stored procedures. This is known and filed in the BOL under "Deferred Name Resolution".

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

For XML: create elements using cell values

Hi All,
If have a sql table with 2 columns and 2 rows with values
[["col1row1","col2row1"],["col1row2","col2row2"]].

Using t-SQL with "for xml"
How can i create a xml where the cell values (not column names) appear
as elements?
eg:
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2
Thanks,

slyi-- It can be done, but remember that you will have to
-- escape all the XML yourself

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

select 1 as Tag,
null as Parent,
'<'+col1+'>'+col2+'</'+col1+'>' as [TestNode!1!!xml]
from #test

order by Tag,[TestNode!1!!xml]
for xml explicit

drop table #test|||Thanks thats exactly what i needed to know|||On closer examination this wont work it gives

<TestNode><col1row1>col2row1</col1row1></TestNode>
<TestNode><col1row2>col2row2</col1row2></TestNode>
while i need something like
<TestNode>
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2>
</TestNode|||Unless someone else knows better, you're out of luck. Perhaps
you could look at redesigning the XML you are generating
and then apply an XSL transformation at the client.|||Thanks Mark. Could i create a temp table, with the cell values as
columns and build a sql xml query or loop from there?
Although im not too sure if that would work, very efficiently?|||(adrianca@.gmail.com) writes:
> Thanks Mark. Could i create a temp table, with the cell values as
> columns and build a sql xml query or loop from there?
> Although im not too sure if that would work, very efficiently?

I can't see that you can do this in SQL 2000 at all. Well, you can
build an nvarchar string that has the XML, and forego FOR XML
altogether, but if you exceed 4000 characters you lose anyway.

I think you need to build this document client-side.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--something like below is what i was thinking but is it efficent?
--as the client side asp code works out very slow thats why i want to
do it on the sql server if possible

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

create table #xmltree( xmlblob text)
INSERT INTO #xmltree VALUES ('<table>')

Declare @.sqlq varchar(4000)
DECLARE @.textptr varbinary(16)
DECLARE @.bigtext varchar(8000)
DECLARE @.textlen int
DECLARE @.col1 varchar(32), @.col2 varchar(32)

SELECT @.textptr=TEXTPTR(xmlblob) FROM #xmltree

DECLARE tst_cursor CURSOR FOR select * from #test
OPEN tst_cursor
FETCH NEXT FROM tst_cursor into @.col1, @.col2
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.bigtext='<'+ @.col1 + '>' +@.col2+'</'+ @.col1 + '>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext
FETCH NEXT FROM tst_cursor into @.col1, @.col2
END
CLOSE tst_cursor
DEALLOCATE tst_cursor
SET @.bigtext='</table>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext

select xmlblob from #xmltree

drop table #test
drop table #xmltree|||(adrianca@.gmail.com) writes:
> --something like below is what i was thinking but is it efficent?

More to the point: does it work?

> create table #xmltree( xmlblob text)
> INSERT INTO #xmltree VALUES ('<table>')

There is not really any way go get the xml from FOR XML into the table.
Well, you can get it to the client, and then INSERT back. Please don't
that. You're wasting bandwidth.

> --as the client side asp code works out very slow thats why i want to
> do it on the sql server if possible

For this sort of task, I would expect VBscript to be faster than T-SQL,
since we are only doing string manipulation.

You could write a program in C or C# for the task, but then you would have
to pass the XML string to the C program in some way. If you go by file,
you probably lose on the swings what you gain on the roundabout.

I should add the disclaimer that I have no knowledge about ASP
programming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||asp 6 and asp.net both took about 10 sec to create the xml client side
from a sql table for a 50k table to xml

Using this method it now takes about 2 sec's by just displaying
resultset.

<%@. Page Language="C#" %
<%@. Import Namespace="System.Data.SqlClient" %
<script runat="server">
SqlConnection sqlConnection1;
SqlCommand sqlCommand1;

void Page_Load(Object Sender, EventArgs e) {

sqlConnection1 = new System.Data.SqlClient.SqlConnection();
sqlCommand1 = new System.Data.SqlClient.SqlCommand();
sqlConnection1.ConnectionString = "some connection details";
sqlConnection1.Open();
sqlCommand1.Connection = this.sqlConnection1;
sqlCommand1.CommandText = "sp_getaxml_dataisland";
Response.ContentType = "text/xml";
Response.Write(sqlCommand1.ExecuteScalar().ToStrin g());

}
</script
For me thats a performance gain worth taking.|||(adrianca@.gmail.com) writes:
> asp 6 and asp.net both took about 10 sec to create the xml client side
> from a sql table for a 50k table to xml

Just to check: how did you get the data to the client? You did get
all data into a dataset didn't you?

> sqlCommand1.CommandText = "sp_getaxml_dataisland";

sp_ is a prefix that is reserved for system stored procedure, and
SQL Server first looks in master for these. You should not use it
for your own code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Just to check: how did you get the data to the client? You did get
>all data into a dataset didn't you?
Since the resultset is just one huge SQL Text datatype,
i just wrote it directly to the page, no need for the overhead of
creating a .net dataset object,
and then a javascript dataisland reads it directly.

eg: <xml id="my-dataisland" src="http://pics.10026.com/?src=getdataisland.aspx" /
>> sqlCommand1.CommandText = "sp_getaxml_dataisland";

>sp_ is a prefix that is reserved for system stored procedure, and
>SQL Server first looks in master for these. You should not use it
>for your own code.
Thanks for the tip i didnt know that.
Do you know if first checks the master table, will that slow down the
request correct / target sp?
I had thought you needed to put "master.dbo.sp_" to access a master sp?

Thanks for your help.|||(adrianca@.gmail.com) writes:
> Since the resultset is just one huge SQL Text datatype,
> i just wrote it directly to the page, no need for the overhead of
> creating a .net dataset object,
> and then a javascript dataisland reads it directly.

Javascript is maybe not the fastest. Can you save to a file, and run a
program in a non-interpreted langauge?

> Do you know if first checks the master table, will that slow down the
> request correct / target sp?
> I had thought you needed to put "master.dbo.sp_" to access a master sp?

In such case "sp_help" would not work. In fact when you say

somedatabase.dbo.sp_help tbl

what you get information about is somedatabase.dbo.tbl.

Exactly what happens is difficult describe, because it changes every
now and then. But if Microsoft would ship a system procedure called
sp_getaxml_dataisland, you would be in for a nasty surprise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

For XML Path problems

Can anyone tell me how to add multiple columns with the same name? Here is an example of the XML format I'm trying to create using For XML Path

<TABLETYPE TYPEABBRV=“IDEADISAB” TOTALINDICATOR=“N”>
<CATEGORY TYPE=“DISABCATIDEA” VALUE=“AUT”/>
<CATEGORY TYPE=“AGESA” VALUE=“6”/>
<CATEGORY TYPE=“EDENVIRIDEASA” VALUE=“RC80”/>
<AMOUNT>10</AMOUNT>
</TABLETYPE>

Here is the query I was trying to use

SELECT

'IDEADISAB' AS '@.TYPEABBRV',

'N' AS '@.TOTALINDICATOR',

'DISABCATIDEA' AS 'CATEGORY/@.TYPE',

IdeaCategory AS 'CATEGORY/@.VALUE',

'AGESA' AS 'CATEGORY/@.TYPE',

AGE AS 'CATEGORY/@.VALUE',

'EDENVIRIDESAS' AS 'CATEGORY/@.TYPE',

EECATEGORY AS 'CATEGORY/@.VALUE',

COUNT(*) AS 'AMOUNT'

FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory

FOR XML PATH('TABLETYPE'), TYPE)

And this is the error I'm getting

Msg 6810, Level 16, State 1, Line 1

Column name 'CATEGORY/@.TYPE' is repeated. The same attribute cannot be generated more than once on the same XML tag.

Any help would be much appreciated

Use subqueries

SELECT
'IDEADISAB' AS '@.TYPEABBRV',
'N' AS '@.TOTALINDICATOR',
(SELECT
'DISABCATIDEA' AS '@.TYPE',
IdeaCategory AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'AGESA' AS '@.TYPE',
AGE AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'EDENVIRIDESAS' AS '@.TYPE',
EECATEGORY AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
COUNT(*) AS 'AMOUNT'
FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory
FOR XML PATH('TABLETYPE'), TYPE

|||

Thanks Mark,

I had already tried using subqueries but could not quite get the syntax correct. I was using the 'from' statement after every select and that was throwing all of my records under one tag. Thanks again.

For XML Path problem?

I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporar
y
> table is created with the recordset I wish to call the results as per belo
w
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||And it certainly doesn't support FOR PATH.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:uJYgjngmHHA.960@.TK2MSFTNGP03.phx.gbl...
> Daniel Badger wrote:
> I don't see anything wrong with that snippet, unless you are using SQL
> Server 2000 which does not support the root clause I think as it is a new
> feature only supported in SQL server 2005.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.

For XML Path problem?

I have a stored procedure that is to create an XML file, once the temporary
table is created with the recordset I wish to call the results as per below
query -
select mailid,
addresstypeid,
mailtexttypeid,
registereduserid,
emailaddressid,
attachmentid,
emailpriority,
emailsubject,
fromemail,
emailbody,
mailaction,
createddate,
emaildate
from #EmailHeaderXMLOutput
for xml path ('row'), root('root')
I keep getting an error message saying:
Line 168: Incorrect syntax near 'path'.
What is wrong with this query? The reason I am doing it this way is that I
need to have a root node as well as a row node before the actual data.
Daniel Badger wrote:
> I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query?
I don't see anything wrong with that snippet, unless you are using SQL
Server 2000 which does not support the root clause I think as it is a
new feature only supported in SQL server 2005.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Are you using SQL Server 2000 or 2005?
As others have mentioned, you need 2005 for FOR XML PATH to work.
however for this simple query, you could use FOR XML RAW in 2000 and use the
client-side capabilities to add the root node (all providers have the
ability to set a root node property on the SQLXML provider).
Best regards
Michael
"Daniel Badger" <DanielBadger@.discussions.microsoft.com> wrote in message
news:570058BA-F3A6-4405-8CF6-42E5F9D09A80@.microsoft.com...
>I have a stored procedure that is to create an XML file, once the temporary
> table is created with the recordset I wish to call the results as per
> below
> query -
> select mailid,
> addresstypeid,
> mailtexttypeid,
> registereduserid,
> emailaddressid,
> attachmentid,
> emailpriority,
> emailsubject,
> fromemail,
> emailbody,
> mailaction,
> createddate,
> emaildate
> from #EmailHeaderXMLOutput
> for xml path ('row'), root('root')
> I keep getting an error message saying:
> Line 168: Incorrect syntax near 'path'.
> What is wrong with this query? The reason I am doing it this way is that I
> need to have a root node as well as a row node before the actual data.

FOR XML in web page- OK what's next

(1) I need to select records from a SQL database and create a XML document which I then need to write to a users directory. I am using SQL express with VWD 2005. I located the FOR XML and can execute in VWD's SQL graphical tool. I haven't tried in a Web Form yet but I assume it will work ok. But then how do I write the xml results from the SQL query to local user's directory?

(2) I can execute the FOR XML in the SQL graphical tool but how do i Connect to the DB in the Web Form? Use data.sqlClient.Connection and use .SQLcommand to perform the SQL query? If so, then what?

Following is an example I found that shows use of a NameSpace

WITH XMLNAMESPACES (DEFAULT 'urn:example.com/doc'

, 'urn:example.com/customer' as "c"

, 'urn:example.com/order' as"o"

)

SELECT CustomerID as "@.ID",

(SELECT OrderID as "@.OrderID"

from Orders

where Customers.CustomerID=Orders.CustomerID

FOR XML PATH('o:Order'), TYPE

) as "c:Orders",

CompanyName as "c:CompanyName",

ContactTitle as "c:ContactName/@.ContactTitle",

ContactName as "c:ContactName/text()",

PostalCode as "c:Address/@.ZIP",

Address as "c:Address/c:Street",

City as "c:Address/c:City"

FROM Customers

FOR XML PATH('c:Customer'), ROOT('doc')

My research turns up nothing on the above. Is FOR SQL the best way to go? I see that the SQLXML is not available in SQL server express 2005.

Thanks for any help.

Pauley

Once you have the data in a dataset you can write the XML to a file(froma datatable as well if you want) Link is here;

http://msdn2.microsoft.com/en-us/library/zx8h06sz.aspx

To do this you do not need to use for xml or even the XML-DT on the server.

For XML Explicit Structure

Hello everyone,
I'm trying to create an XML file using FOR XML EXPLICIT. However, I
need part of my result to look like this:
<Serial>
<SerialNuber>123456</SerialNumber>
<SerialNuber>123457</SerialNumber>
<SerialNuber>123458</SerialNumber>
</Serial>
What is happening is that I am only able to get repeating attributes
to appear as follows:
<Serial>
<SerialNumber>123456</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123457</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123458</SerialNumber>
</Serial>
Does anyone know what I may be doing wrong?
Thanks!!
Rey
Bear in mind that FOR XML EXPLICIT only returns an XML fragment - not a
well-formed document. So your query could return the <SerialNumber>
elements, but your client application would have to add the <Serial> root
tag.
The query should look something like
SELECT 1 AS Tag,
NULL As Parent,
Serialno As [SerialNumber!1]
FROM myTable
FOR XML EXPLICIT
Adding the root tag depends on the client-side application used to execute
the query. The SQLXML OLEDB provider and the SQLXML Managed classes have a
property on the Command object to do that.
NB: FOR XML in SQL Server 2005 includes a ROOT directive to add a root tag
but I'm afraid in SQL Server 2000 you can only get a fragment.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rey" <rdiaz@.hotmail.com> wrote in message
news:a696a6c7.0408240723.1ae34deb@.posting.google.c om...
Hello everyone,
I'm trying to create an XML file using FOR XML EXPLICIT. However, I
need part of my result to look like this:
<Serial>
<SerialNuber>123456</SerialNumber>
<SerialNuber>123457</SerialNumber>
<SerialNuber>123458</SerialNumber>
</Serial>
What is happening is that I am only able to get repeating attributes
to appear as follows:
<Serial>
<SerialNumber>123456</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123457</SerialNumber>
</Serial>
<Serial>
<SerialNumber>123458</SerialNumber>
</Serial>
Does anyone know what I may be doing wrong?
Thanks!!
Rey
|||You could add a second select clause in the explicit mode to add a root
clause (although that is not as performant as using the provider property).
See the FOR XML in SQL Server 2005 whitepaper on MSDN for an example of such
an explicit mode query
(http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp).
HTH
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:%23iJQXKgiEHA.4056@.TK2MSFTNGP09.phx.gbl...
> Bear in mind that FOR XML EXPLICIT only returns an XML fragment - not a
> well-formed document. So your query could return the <SerialNumber>
> elements, but your client application would have to add the <Serial> root
> tag.
> The query should look something like
> SELECT 1 AS Tag,
> NULL As Parent,
> Serialno As [SerialNumber!1]
> FROM myTable
> FOR XML EXPLICIT
> Adding the root tag depends on the client-side application used to execute
> the query. The SQLXML OLEDB provider and the SQLXML Managed classes have a
> property on the Command object to do that.
> NB: FOR XML in SQL Server 2005 includes a ROOT directive to add a root tag
> but I'm afraid in SQL Server 2000 you can only get a fragment.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rey" <rdiaz@.hotmail.com> wrote in message
> news:a696a6c7.0408240723.1ae34deb@.posting.google.c om...
> Hello everyone,
> I'm trying to create an XML file using FOR XML EXPLICIT. However, I
> need part of my result to look like this:
> <Serial>
> <SerialNuber>123456</SerialNumber>
> <SerialNuber>123457</SerialNumber>
> <SerialNuber>123458</SerialNumber>
> </Serial>
> What is happening is that I am only able to get repeating attributes
> to appear as follows:
> <Serial>
> <SerialNumber>123456</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123457</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123458</SerialNumber>
> </Serial>
> Does anyone know what I may be doing wrong?
> Thanks!!
> Rey
>
|||Michael is (as always) right on the money! You could write the query like
this:
SELECT 1 AS Tag,
NULL As Parent,
NULL As [Serial!1!dummyattribute],
NULL AS [SerialNumber!2]
UNION ALL
SELECT 2,
1,
NULL,
SerialNo
FROM myTable
ORDER BY Tag
FOR XML EXPLICIT
This would generate the root tag for you.
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:euFZISgiEHA.2704@.TK2MSFTNGP10.phx.gbl...
You could add a second select clause in the explicit mode to add a root
clause (although that is not as performant as using the provider property).
See the FOR XML in SQL Server 2005 whitepaper on MSDN for an example of such
an explicit mode query
(http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp).
HTH
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:%23iJQXKgiEHA.4056@.TK2MSFTNGP09.phx.gbl...
> Bear in mind that FOR XML EXPLICIT only returns an XML fragment - not a
> well-formed document. So your query could return the <SerialNumber>
> elements, but your client application would have to add the <Serial> root
> tag.
> The query should look something like
> SELECT 1 AS Tag,
> NULL As Parent,
> Serialno As [SerialNumber!1]
> FROM myTable
> FOR XML EXPLICIT
> Adding the root tag depends on the client-side application used to execute
> the query. The SQLXML OLEDB provider and the SQLXML Managed classes have a
> property on the Command object to do that.
> NB: FOR XML in SQL Server 2005 includes a ROOT directive to add a root tag
> but I'm afraid in SQL Server 2000 you can only get a fragment.
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rey" <rdiaz@.hotmail.com> wrote in message
> news:a696a6c7.0408240723.1ae34deb@.posting.google.c om...
> Hello everyone,
> I'm trying to create an XML file using FOR XML EXPLICIT. However, I
> need part of my result to look like this:
> <Serial>
> <SerialNuber>123456</SerialNumber>
> <SerialNuber>123457</SerialNumber>
> <SerialNuber>123458</SerialNumber>
> </Serial>
> What is happening is that I am only able to get repeating attributes
> to appear as follows:
> <Serial>
> <SerialNumber>123456</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123457</SerialNumber>
> </Serial>
> <Serial>
> <SerialNumber>123458</SerialNumber>
> </Serial>
> Does anyone know what I may be doing wrong?
> Thanks!!
> Rey
>

for xml explicit or xsl

I need to create a big xml report (about 60 elements, 9 levels deep). The data is in a single sql server table. For this I have been given an xsd file that the report must match in format.
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>