Nested SELECT ... FOR XML PATH Return ESCAPED <>s

Hi,
I'm trying to build a complicated web service request using the (much better than FOR XML EXPLICIT) PATH mode, and it's great and all except that when I nest them I am getting &lt, &gt for the nested nodes. Here's a snippit:

BEGIN

SET NOCOUNT ON;

SELECT
'P' AS "Item/DataBlkInd",
'A' AS "Item/PhoneQual/EditTypeInd",
'CITYCODE' AS "Item/PhoneQual/AddPhoneQual/City",
...
(SELECT DISTINCT
'R' AS "DataBlkInd",
'A' AS "EmailQual/EditTypeInd",
'1' AS "EmailQual/LineNum",
'T' AS "EmailQual/Type",
bpe.EmailAddress AS "EmailQual/EmailData"
FROM dbo.BPEmail bpe WHERE bpe.BusPartyId = @.CustomerId
FOR XML PATH('Item')) AS "node()",
...
FOR XML PATH('ItemAry'), ROOT('PNRBFSecondaryBldChgMods')
END

Any idea why the nested FOR XML PATH would be escaped, and how to return it as XML instead of "&lt Item &gt &lt ..."?

Many thanks!
Andy

Hi Andy

FOR XML queries per default return the resulting XML as a string value for backwards-compatibility reasons (regardless of the mode). So you should say

FOR XML PATH('Item'), TYPE

if you need the result to be XML. Also, I think you then will not need the AS "node()". Just leave the column alias away.

Best regards
Michael

Nested CASE statements Problem

I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'

I think there are two problems:

(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE

(b) Each CASE WHEN needs an END

I was not able to completely modify your T-SQL...but your changes should look similar to:

select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end

Peter

|||I think I probably should just use IF statements inside my first CASE statement|||

Well, this throws an error also.

The error is:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'IF'.

Msg 147, Level 15, State 1, Line 5

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Msg 147, Level 15, State 1, Line 9

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

END

ELSE

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + (rf.rmstranamt)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)

END

END

END AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

|||

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB < ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax

|||

typo on my last statement (change is bolditalic)

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB > ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.

otherwise, just take off the case words from inside (to have a correct syntax)

Nested CASE statements Problem

I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'

I think there are two problems:

(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE

(b) Each CASE WHEN needs an END

I was not able to completely modify your T-SQL...but your changes should look similar to:

select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end

Peter

|||I think I probably should just use IF statements inside my first CASE statement|||

Well, this throws an error also.

The error is:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'IF'.

Msg 147, Level 15, State 1, Line 5

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Msg 147, Level 15, State 1, Line 9

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

END

ELSE

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + (rf.rmstranamt)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)

END

END

END AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

|||

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB < ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax

|||

typo on my last statement (change is bolditalic)

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB > ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.

otherwise, just take off the case words from inside (to have a correct syntax)

NEEDED: SQL Server Express 2005 Setup Walkthrough

Someone please help me, Im trying to set up SQLServer on my website(IIS v4.0) and I cant seem to get any web apps that use SQL to intall correctly, I get errors like, SQL does not recieve remote connections by default, or just 'Server Not Found'.

This is what I need to know

- How to set SQL to accept incomming remote connections

- Where to find the Name of the SQL instance

- Is it a good idea to have a password on the db, and how do I add one

- A walkthrough on how to set up SQL Express 2005(what to do after the istall is complete)

As you can see Im very new to SQL. So any information you could give to help would be greatly appreciated.

Thanks

Admin @. Something-to-do.com

1. Here is a KB Article that goes through setting up remote connections. http://support.microsoft.com/kb/914277

2. With SQL Server 2005 Express the server install defaults to ".\SQLEXPRESS" for the instance. To find the name though you can check the windows service list in the Services mmc inside the administration folder under the control panel. The name will be inside brackets beside the SQL Server Service.

3. You should try and stay with Windows Logons for the security.. if you do need to use sql logons you should try a strong password on the database accounts.

Need Web Client for doing Adhoc querry

Does anyone know of a good Web Client where I can expose SQL tables to
selected users for the purpose of doing AdHoc query?
ThanksSeveral listed here, not all web-based though, you'll have to fish for them
yourself, but it's better than sending you to Google. :-)
http://www.aspfaq.com/2442
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:5BEB54E4-A8A5-4684-AF09-2AD801229F1C@.microsoft.com...
> Does anyone know of a good Web Client where I can expose SQL tables to
> selected users for the purpose of doing AdHoc query?
> Thanks

Need urgent help about sql connection denied

From the same web server to sql server, ASP pages work fine, but .NET pages get "SQL Server does not exist or access denied" error message.
Environment:
both are Windows 2000 sp4,
sql server 2000 sp4
not firewall between
Everything is okay before I got some windows security updates fails on the sql server! and I reboot the sql server!
What happened with the security update? Why does it only effect the .NET pages not the ASP pages? They even use the same connection string!
Thanks for any comment

Are you using integrated security? That is, are you passing in a user name and password (in which case you are using SQL Server security) or not (in which case you are using integrated security). ASP and ASP.NET operate under different security contexts. ASP operates as IUSR_<machinename>, whereas ASP.NET operates under the security context of the ASPNET windows user.

I have no idea what the security update would have done.

|||

Thanks for the reply.
I do pass a user name and password in the connection string. it's the same string that I used in the ASP pages. it works fine. But I reboot the servers, only the ASP pages work.
the string looks like "DATABASE=fansion;server=dbsvr; uid=sa; pwd=password;"

|||Just tested, this problam only happens on the windows 2000 web server, windows 2003 is fine.|||

Did you ever get an answer for this re: sql connection - same string works under asp, not under asp.net, with userid= and password= and not with a dsn?

thanks a lot

Neil Jay warner