How to add nodes into xml?

I want to add some nodes into xml variable:
For example:
The original xml:
<PO>
<PONO>123</PONO>
<PODATE>07252007</PODATE>
<POCNAME>ABC Company</PONAME>
<POITEMS>
...
...
</POITEMS>
<POFOOTER>No Message</POFOOTER>
</PO>
I want insert several POFEATURES node into xml as:
<PO>
<PONO>123</PONO>
<PODATE>07252007</PODATE>
<POCNAME>ABC Company</PONAME>
<POFEATURES>Special Items Mark</POFEATURES>
<POFEATURES>Special Client Mark</POFEATURES>
<POFEATURES>Credit Account</POFEATURES>
<POITEMS>
...
...
</POITEMS>
<POFOOTER>No Message</POFOOTER>
</PO>
How to use modify() to do that?
ABC wrote:

> How to use modify() to do that?
Here is an example, using a variable of type xml but you could as well
use a column of that type:
DECLARE @.x xml;
SET @.x = '<PO>
<PONO>123</PONO>
<PODATE>07252007</PODATE>
<POCNAME>ABC Company</POCNAME>
<POITEMS>
...
...
</POITEMS>
<POFOOTER>No Message</POFOOTER>
</PO>';
SET @.x.modify('
insert (
<POFEATURES>Special Items Mark</POFEATURES>,
<POFEATURES>Special Client Mark</POFEATURES>,
<POFEATURES>Credit Account</POFEATURES>
) after (PO/POCNAME)[1]
');
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

How to add nodes into xml?

I want to add some nodes into xml variable:
For example:
The original xml:
<PO>
<PONO>123</PONO>
<PODATE>07252007</PODATE>
<POCNAME>ABC Company</PONAME>
<POITEMS>
..
..
</POITEMS>
<POFOOTER>No Message</POFOOTER>
</PO>
I want insert several POFEATURES node into xml as:
<PO>
<PONO>123</PONO>
<PODATE>07252007</PODATE>
<POCNAME>ABC Company</PONAME>
<POFEATURES>Special Items Mark</POFEATURES>
<POFEATURES>Special Client Mark</POFEATURES>
<POFEATURES>Credit Account</POFEATURES>
<POITEMS>
..
..
</POITEMS>
<POFOOTER>No Message</POFOOTER>
</PO>
How to use modify() to do that?ABC wrote:

> How to use modify() to do that?
Here is an example, using a variable of type xml but you could as well
use a column of that type:
DECLARE @.x xml;
SET @.x = '<PO>
<PONO>123</PONO>
<PODATE>07252007</PODATE>
<POCNAME>ABC Company</POCNAME>
<POITEMS>
..
..
</POITEMS>
<POFOOTER>No Message</POFOOTER>
</PO>';
SET @.x.modify('
insert (
<POFEATURES>Special Items Mark</POFEATURES>,
<POFEATURES>Special Client Mark</POFEATURES>,
<POFEATURES>Credit Account</POFEATURES>
) after (PO/POCNAME)[1]
');
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

How to add if ~else to a exist Stored Procedure

here is my original Sql Code

I add a parameters @.CustId in line 13

when CustID not Null, I want to add this to the Where clause line 146 and 156

if CustId not null then where clause will add a reference like CustId = @.CustID

if CustId is Null then not reference @.custID

how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..

1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171

CustID=COALESCE(@.CustID,CustId)

COALESCE function returns the first non-null expression in its expression list.

Refer the below link for more information

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

how to access sum(Some_Column) in SQL

I am trying to build a new dataset based on existing set, by appending a
total value to the original one. (So I could pass this new dataset into a bar
chart to plot)
I know the sum(Some_column) is in the report domain, since we can access
this value in designer. But my question is how can I pick it out using SQL
statement to build another dataset. Or if there is better way to construct
the new dataset.
Micosoft gurus, please help. I am doing a consulting project at client side
for MS. Do not want to blow this one :)
--
SincerelyHave you tried using the ReportItems collection to reference the sum
field,and use it as a parameter into the other query?
ie
ReportItems!Textbox1.Value
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:7D89CBF3-D016-459F-B3F3-B1FC58DE3B3A@.microsoft.com...
> I am trying to build a new dataset based on existing set, by appending a
> total value to the original one. (So I could pass this new dataset into a
bar
> chart to plot)
> I know the sum(Some_column) is in the report domain, since we can access
> this value in designer. But my question is how can I pick it out using SQL
> statement to build another dataset. Or if there is better way to construct
> the new dataset.
> Micosoft gurus, please help. I am doing a consulting project at client
side
> for MS. Do not want to blow this one :)
> --
> Sincerely