Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

Get the Grand Total of this query

I need to get the Grand Total of the results of this query.
The query pulls the total customer quotes for each community in a management company and loads my DataGrid:

This query works fine for individual community totals

"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _
 " Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _
 " (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _
 "(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"

At first glance you would think that the following query would return the Grand Total for all communities in the management company:

Current Grand Total Query

"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE
cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session
("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "

But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.

Thanks in advance.The simple answer is to not use DISTINCT. Why is it that you "have" to use that keyword in your grand total query?

Terri|||In my application not using DISTINCT is not an option right now. I plan on changing a lot about this piece, but the president is breathing down my neck so I had to get it working post haste.

The reason for DISTINCT is there are multiple quote lines for a given cust Id. I did end up making it work however. I had to count phone and email in individual arrays and then combine the totals. The problem being that if a cust contacted us by both Email and Phone, the Grand Total query only pick them up based off the last quote entry, of course that is because of using distinct. So it did not match the totals that we report on, which are Phone and Email.

Thanks.

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan

Get previous sale for a customer''s sale?

If I have the query:

select non empty [Ship Date].[Date].[Date] on 0

from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])

where ([Measures].[Internet Extended Amount])

How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished)

For more date data, run this one:

select non empty [Ship Date].[Date].[Date] on 0

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

So, here is one (ugly) way to get what your looking for.

Code Snippet

select

[Measures].[Internet Extended Amount] on 0,

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].&[15566],

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE

[Ship Date].[Date].CurrentMember.MemberValue <

[Ship Date].[Date].&[1082].MemberValue

),

[Ship Date].[Date].CurrentMember.MemberValue,

BDESC

),

1)

on 1

from [Adventure Works]

Did I mention this was ugly? :-)

Bryan

|||

Thanks Bryan.

I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"

Code Snippet

with member x as

-- GET TOP 1 VALUE

HEAD(

-- SORT BASED ON DATE VALUE IN DESCENDING ORDER

ORDER(

FILTER(

-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER

EXISTS(

[Ship Date].[Date].[Date].Members,

[Customer].[Customer].CurrentMember,

'Internet Sales'

),

-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE

[Ship Date].[Date].CurrentMember.MemberValue? <

[Ship Date].[Date].CurrentMember.MemberValue?

),

[Ship Date].[Date].CurrentMember.MemberValue?,

BDESC

)

)

member growth as [Measures].[Internet Extended Amount] / x - 1

select

{[Measures].[Internet Extended Amount], x, growth} on 0,

[Ship Date].[Date].[Date] on 1

on 1

from [Adventure Works]

where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])

|||

Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?

Thanks,
Bryan