Multi-record wrapping textbox?

I know that the subject is badly expressed, but hopefully I can get the
point across...
I'm a total newbie at MS Reporting services and I have a question. I
have a dataset something like this:
Customer Box Item Qty
A 1 X1 50
A 1 X2 50
A 2 X3 75
A 2 X4 25
A 3 X5 15
B 1 X1 35
B 1 X2 65
B 2 X2 10
B 2 X3 10
etc... you get the idea. Each box holds 100 mixed items; each customer
has one or more boxes.
The output report I want should look something like this:
Customer Box Contents
A 001 X1 50 * X2 50
002 X3 75 * X4 25
003 X5 15
Total for customer A: 3 boxes, 215 pieces
B 001 X1 35 * X2 65
002 X2 10 * X3 10 * X4 20 * X5 10 * X6
5
X7 5 * X8 40
003 X9 50
Total for customer B: 3 boxes, 250 pieces
I set it up as a report with two grouping levels. The tricky part is
the "contents" column. I want it to be a textbox that can be up to a
certain width, wrapping if necessary, and containing a string of data
from records. Any way to do that with Reporting Svcs?
If it helps, when I did this with MS Access, I made the wrapping
textbox part as a subreport with columns. I can try that with RS but
it seems like it might be possible another way?
I should also mention that I'm using SQL Server 2000, VB.NET 2003 as my
designer, so perhaps I don't have access to the newest features of 2005.I wrote:
> The tricky part is
> the "contents" column. I want it to be a textbox that can be up to a
> certain width, wrapping if necessary, and containing a string of data
> from records. Any way to do that with Reporting Svcs?
Never mind; I found this blog entry
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
that gives a quite serviceable method for getting what I want.

multiplication by row without a cursor? Myth or Madness?

Hi all...I have a friend who has a problem I'm trying to help with (no...REALLY...it's a FRIEND, not ME!!! *LOL*)

I haven't run across the need...but in a nutshell...we have a table with many rows of data, one column in each row needs to be multiplied by all other same-columns in the table's other rows.

For example...
MyKey int, MyFloat float(53)

I want to multiply Myfloat by all other Myfloat columns in the table.

Similar to SUM(MyFloat) but something like PRODUCT(MyFloat).

Is there a aggregate kept in a basement closet somewhere, or a way to perform this operation rather than using a cursor to do it:

An example of my table:
1 3.2
2 4.1
3 7.1

if I could do a PRODUCT(MyFloat) I would want the result to be (3.2 X 4.1 X 7.1) or 93.152

Do I have to do this with a cursor?

Thanks!
PaulGot it...
DECLARE @.MyInt int
SET @.MyInt = 1
SELECT @.MyInt = @.MyInt * MyFloat from MyTable where MyKey = <whatever>
print @.Myint

Yeah, I know...it's an overflow waiting to happen...but still...

Thanks for reading...and sorry to talk to myself yet again...I guess my Mom was right when she told me that I'm just a waste of bandwidth... :(|||What, did nobody pay attention during High School math classes?

select POWER(10, sum(log10(MyFloat))) from MyTable|||I have a harder time believing he has a freind...

:D

Just joking of course...|||Whee!!! The forum is working again!!! (I HEARD that, Brett...and a DOG and 3 tortoises still count as friends!)

Thanks for the suggestion, Blindman...I guess all that time and money was wasted in my math classes! :) Ya know, somehow, I just KNEW I shoulda paid more attention in my LOG class :D

However...(hey, you knew this was coming) that doesn't work, of course, for the negative numbers I've got in my "product" table...

I know a select can be written to check the sign of each number included in the calculation, but before I go and burn an excessive number of brain cells (or search keystrokes) anyone have a handy-dandy code block avialable for that part of the calculation?

It will, I am reasonably certain, require merely counting the number of negative numbers in the calculation and an ABS, like:
select POWER(10, sum(log10(ABS(MyFloat)))) from MyTable
Then somehow applying the sign ( ans * -1 for odd number of negative MyFloat entries in the calculation) to the result, but I am presently at a loss for how to apply the sign aspect of the problem.

Off to the races...

Thanks again!
Paul|||I imagine it would be as simple as:

select POWER(10, sum(log10(ABS(MyFloat))))*sign(MyFloat) from MyTable

My only problem with it is that if you have a 0 anywhere in the column, you could end up with problems, too. I think for simplicity sake, you should go with your first query. Of course, SQL 2005 is bound to disable Blindman's query that it was based on, since it has proven itself to be way too useful.|||OK, from what I have been able to ascertain (yep, just bought a new dictionary, thankyouverymuch! ;) ) the proposed addition of SIGN(MyFloat) won't work because that isn't (at least as written) an aggregate, so the compiler complains about it being invalid in the select list 'cause there is no GROUP BY nor is it in an aggregate.

Neither can it be added into the POWER(xxx) expression - because, again, POWER() apparently maintains a strong and lasting hatred of negative numbers - preferring to relegate them to nothingness rather than deal with them in a play-nice-in-the-sandbox way. But I digress...

What I needed was a way to apply the sign AFTER the PRODUCT-like operation.

What I found was several web hits that had logic for PRODUCT-type implementations in SQL that were NEAR what I wanted...and so...without further delay, here is my final product:

SELECT (EXP (SUM (LOG (CASE WHEN MyFloat = 0.00
THEN NULL
ELSE ABS(MyFloat) END))))
* (CASE WHEN (SUM (CASE WHEN SIGN(MyFloat) = -1
THEN 1
ELSE 0 END)) % 2 = 1
THEN -1.00
ELSE 1.00 END) AS Prod
FROM MyTable

The first CASE handles the situation of having NULL or Zero values in the table (which I simply want to ignore).

The second CASE applies the sign to the result, by keeping track of the NUMBER of negative values, and then multiplying the EXP(SUM(LOG())) overall result by -1 if the COUNT of negative values is odd, or by 1 if the COUNT is even.

I wish I could take credit for this, but alas, just for a workable b@.st@.rdization of the work of other folks ;)

I still am bothered by the complexity...I mean, if the calculation changes a year from now, I KNOW they are gonna send that Jr. Programmer to ask me about what the HEY I was thinking, or else just have him/her make changes without knowing what it does to start with (though I'm SURE that never happens ;) ).

The whole thing is further complicated because I (err...my "friend", I mean) have to perform some other calculations on the value returned from the above select...and if I was to use my original method, I'd have to make two passes through the table. That may be preferable to the relative complexity of this select...but I'll try it and see if my peers shoot me down in the code review ;)

Thanks guys!!! Great soundboarding!

Oh, and it REALLY started out to be the question of a co-worker working on the same project as I...it became mine when it took me too long to get back to her with a "I would do this..." email... :(|||Accounts for negative values and nulls:

------------------
set nocount on
declare @.PRODUCT table (Product varchar(30), Value float)

insert into @.Product (Product, Value) Values ('Coffee', -4.4)
insert into @.Product (Product, Value) Values ('Coffee', -5.5)
insert into @.Product (Product, Value) Values ('Coffee', 6.6)
insert into @.Product (Product, Value) Values ('Coffee', 7.7)
insert into @.Product (Product, Value) Values ('Tea', -44)
insert into @.Product (Product, Value) Values ('Tea', 55)
insert into @.Product (Product, Value) Values ('Tea', 66)
insert into @.Product (Product, Value) Values ('Tea', 77)
insert into @.Product (Product, Value) Values ('Water', -44)
insert into @.Product (Product, Value) Values ('Water', 55)
insert into @.Product (Product, Value) Values ('Water', 66)
insert into @.Product (Product, Value) Values ('Water', 77)
insert into @.Product (Product, Value) Values ('Water', 0)

select Product, EXP(sum(log(abs(nullif(Value, 0))))) *
(1+2*(cast(sum(sign(Value)-1)/2 as int) % 2)) * min(abs(sign(value)))
from @.PRODUCT
group by Product
------------------
This is truly mental mssql-bation...|||Hi, this is Paul's boss...thanks for exploding his head...

Now..WHO is going to cleanup this mess? ;)|||Well, if you'll check out my title I AM the Electronic Janitor.

Now where did I put my Electro-mop...?

Multiple threads

Is it safe to use one database connection shared across multiple threads. Each thread has its own set of data i.e its own SQL command etc, but sharing a single database connection. Actually the scenario is that in a web site I want to open a single database connection when web application starts up. On each page hit will construct a SQL command and query/update the database.

I just want to know if multiple requests come at the same time will threads be blocked if I share global connection. What will happen if one request is trying to retrieve the data and another request is updating the database using the same connection?

Thanks

Hi!

I don't think it's a good idea to share DB connection across threads. One of them may open it, another may close, another may break... So many things to keep in mind.

DB connections are fast for open/close and if you make many of them, internally there is pool of connections, so framework will optimize access. If you do not have proven performance drop because of frequent connections opening, then don't waste your time with it - create, open and close connections as logic requires.

One more thing - if you have local connections, then your code is less dependent and can be better maintained, supported, evolved.

|||

Let us imagine a situation that thousands of requests come to web site at a particular time then if I open connection for every request then even the connection pool will be exausted. Second thing I want to clarify that thread does not know if the connection is shared or not so thread will never end up closing the connection. Thread will talk to some kind of manager which will keep connection internally and leave it open for every request.

I still don't know how the thread behaviour will be. If all the requests use the same connection then whether each request will be serialized (running one after previous one is finished) or each can run in parallel.

Any idea?

Thanks

|||

I will agree about thousands and speed problems when I will see some real numbers. So, you can try to simulate test load and watch how the system performs.

Also I want to notice, that connection is logical, it have state, which can be not only Open or Close, but also Executing, Fetching, Broken. What if one of your threads do fetching? All others must wait until it finish. And If you have 2 connections, then you can install 2 network adapters and they can split job. So I still think global connection is not a good thing to do because of scalability & reliability reasons.

Don't think of connections as of physical connections, you do not connect to some port in fact, instead you connect to client side layer, which will take care of real connection. You work with logical connections.

|||Thanks for the suggestions. I will see how it goes.

Multiple rows returned by for xml explicit

I have an xml query that runs across five tables. For some reasosn after I
get to the third table my xml returned is breaking into two rows in the
recordset that is returned. It just stops on the first record returned and
breaks mid element name to the second record. I have attached my query up
through table five. It breaks down on the last few closing elements and put
them on a second row. Not sure why this is happening and dealing with the
returned data is a pain. i have never seen for xml explicit returning the
xml as multiple rows before.
Rob
SELECT
1 AS Tag
,NULL AS Parent
,null as [catsubcatproduct!1!category]
,null as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,null as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
union
SELECT
2 AS Tag
,1 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,tcategory.catdesc as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,null as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory
--for xml explicit
union
SELECT
3 AS Tag
,2 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,tsubcategory.subcatname as [subcategory!3!subcatname!element]
,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
union
SELECT
4 AS Tag
,3 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,tsubcategory.subcatname as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,tproduct.productname as [product!4!productname!element]
,tproduct.productdesc as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner join
tproduct on tsubcatproduct.productname=tproduct.productname
order by[category!2!catname!element],[subcateg
ory!3!subcatname!element]
for xml explicitI assume that you use the query analyzer of SQL Server 2000 to see the
result?
If so, that is the wrong tool to get reliable XML results. XML is streamed
back in rowset chunks over TDS. In order to see the data as the stream it
is, you either use the command stream object in ADO/OLEDB (or the equivalent
ADO.Net API), or - if you use ODBC - you need to concatenate the result
yourself.
QA basically shows the rowsets explicit and thus adds the linebreaks every
2034 or so characters...
Note that this will be much better dealt with in the SQL Server 2005
management studio. Hyperlinked XML opens an XML editor. Pretty if I may
say so myself... :-)
Best regards
Michael
"roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>I have an xml query that runs across five tables. For some reasosn after I
> get to the third table my xml returned is breaking into two rows in the
> recordset that is returned. It just stops on the first record returned
> and
> breaks mid element name to the second record. I have attached my query up
> through table five. It breaks down on the last few closing elements and
> put
> them on a second row. Not sure why this is happening and dealing with the
> returned data is a pain. i have never seen for xml explicit returning the
> xml as multiple rows before.
> Rob
>
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,null as [catsubcatproduct!1!category]
> ,null as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> union
> SELECT
> 2 AS Tag
> ,1 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,tcategory.catdesc as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory
> --for xml explicit
> union
>
> SELECT
> 3 AS Tag
> ,2 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
>
> union
> SELECT
> 4 AS Tag
> ,3 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,tproduct.productname as [product!4!productname!element]
> ,tproduct.productdesc as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
> tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner
> join
> tproduct on tsubcatproduct.productname=tproduct.productname
>
> order by[category!2!catname!element],[subcateg
ory!3!subcatname!element]
> for xml explicit|||You are right. I was using the sql reader and not the xmlreader. The sql
reader returns records and the xmlreader returns one xml. If you use the
sqlreader you can concat the records and it will work but it is a waste to d
o
it that way. Thanks
Rob
"Michael Rys [MSFT]" wrote:

> I assume that you use the query analyzer of SQL Server 2000 to see the
> result?
> If so, that is the wrong tool to get reliable XML results. XML is streamed
> back in rowset chunks over TDS. In order to see the data as the stream it
> is, you either use the command stream object in ADO/OLEDB (or the equivale
nt
> ADO.Net API), or - if you use ODBC - you need to concatenate the result
> yourself.
> QA basically shows the rowsets explicit and thus adds the linebreaks every
> 2034 or so characters...
> Note that this will be much better dealt with in the SQL Server 2005
> management studio. Hyperlinked XML opens an XML editor. Pretty if I m
ay
> say so myself... :-)
> Best regards
> Michael
> "roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
> news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>
>

Multiple rows returned by for xml explicit

I have an xml query that runs across five tables. For some reasosn after I
get to the third table my xml returned is breaking into two rows in the
recordset that is returned. It just stops on the first record returned and
breaks mid element name to the second record. I have attached my query up
through table five. It breaks down on the last few closing elements and put
them on a second row. Not sure why this is happening and dealing with the
returned data is a pain. i have never seen for xml explicit returning the
xml as multiple rows before.
Rob
SELECT
1 AS Tag
,NULLAS Parent
,nullas [catsubcatproduct!1!category]
,nullas [category!2!catname!element]
,nullas [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,nullas [subcategory!3!subcatname!element]
,nullas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,nullas [product!4!productname!element]
,nullas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
union
SELECT
2 AS Tag
,1AS Parent
,nullas [catsubcatproduct!1!category]
,tcategory.catnameas [category!2!catname!element]
,tcategory.catdesc as [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,nullas [subcategory!3!subcatname!element]
,nullas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,nullas [product!4!productname!element]
,nullas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
from tcategory
--for xml explicit
union
SELECT
3 AS Tag
,2AS Parent
,nullas [catsubcatproduct!1!category]
,tcategory.catnameas [category!2!catname!element]
,nullas [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,tsubcategory.subcatnameas [subcategory!3!subcatname!element]
,tsubcategory.subcatdescas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,nullas [product!4!productname!element]
,nullas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
union
SELECT
4 AS Tag
,3AS Parent
,nullas [catsubcatproduct!1!category]
,tcategory.catnameas [category!2!catname!element]
,nullas [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,tsubcategory.subcatnameas [subcategory!3!subcatname!element]
,nullas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,tproduct.productnameas [product!4!productname!element]
,tproduct.productdescas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner join
tproduct on tsubcatproduct.productname=tproduct.productname
order by[category!2!catname!element],[subcategory!3!subcatname!element]
for xml explicit
I assume that you use the query analyzer of SQL Server 2000 to see the
result?
If so, that is the wrong tool to get reliable XML results. XML is streamed
back in rowset chunks over TDS. In order to see the data as the stream it
is, you either use the command stream object in ADO/OLEDB (or the equivalent
ADO.Net API), or - if you use ODBC - you need to concatenate the result
yourself.
QA basically shows the rowsets explicit and thus adds the linebreaks every
2034 or so characters...
Note that this will be much better dealt with in the SQL Server 2005
management studio. Hyperlinked XML opens an XML editor. Pretty cool if I may
say so myself... :-)
Best regards
Michael
"roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>I have an xml query that runs across five tables. For some reasosn after I
> get to the third table my xml returned is breaking into two rows in the
> recordset that is returned. It just stops on the first record returned
> and
> breaks mid element name to the second record. I have attached my query up
> through table five. It breaks down on the last few closing elements and
> put
> them on a second row. Not sure why this is happening and dealing with the
> returned data is a pain. i have never seen for xml explicit returning the
> xml as multiple rows before.
> Rob
>
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,null as [catsubcatproduct!1!category]
> ,null as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> union
> SELECT
> 2 AS Tag
> ,1 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,tcategory.catdesc as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory
> --for xml explicit
> union
>
> SELECT
> 3 AS Tag
> ,2 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
>
> union
> SELECT
> 4 AS Tag
> ,3 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,tproduct.productname as [product!4!productname!element]
> ,tproduct.productdesc as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
> tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner
> join
> tproduct on tsubcatproduct.productname=tproduct.productname
>
> order by[category!2!catname!element],[subcategory!3!subcatname!element]
> for xml explicit
|||You are right. I was using the sql reader and not the xmlreader. The sql
reader returns records and the xmlreader returns one xml. If you use the
sqlreader you can concat the records and it will work but it is a waste to do
it that way. Thanks
Rob
"Michael Rys [MSFT]" wrote:

> I assume that you use the query analyzer of SQL Server 2000 to see the
> result?
> If so, that is the wrong tool to get reliable XML results. XML is streamed
> back in rowset chunks over TDS. In order to see the data as the stream it
> is, you either use the command stream object in ADO/OLEDB (or the equivalent
> ADO.Net API), or - if you use ODBC - you need to concatenate the result
> yourself.
> QA basically shows the rowsets explicit and thus adds the linebreaks every
> 2034 or so characters...
> Note that this will be much better dealt with in the SQL Server 2005
> management studio. Hyperlinked XML opens an XML editor. Pretty cool if I may
> say so myself... :-)
> Best regards
> Michael
> "roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
> news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>
>