Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
nextaxtion
Yak Posting Veteran
54 Posts |
Posted - 2014-04-21 : 08:53:22
|
DEAR TEAM , I CREATE A XML WITH FOLLOWING QUERY. SELECT * FROM (SELECT TOP 1 1000000103 AS CHARGE_ID, 'ADVANCE EMI' AS CHARGE_NAME, 0.00 AS CHARGE_AMOUNT FROM QT_LOANDETAIL LD WHERE LD.PROSPECTID = 1001000839 UNION ALL SELECT TOP 1 1000000104 AS CHARGE_ID, 'EXCESS AMOUNT' AS CHARGE_NAME, 0.00 AS CHARGE_AMOUNT FROM QT_LOANDETAIL LD WHERE LD.PROSPECTID = 1001000839 UNION ALL SELECT TOP 1 1000000124 AS CHARGE_ID, 'PRINCIPAL DUE' AS CHARGE_NAME, SANCTIONED_AMOUNT AS CHARGE_AMOUNT FROM QT_LOANDETAIL LD WHERE LD.PROSPECTID = 1001000839) AFOR XML PATH('ROW'), ROOT('ROOT') AND GET RESULT AS BELOW <ROOT> <ROW> <CHARGE_ID>1000000103</CHARGE_ID> <CHARGE_NAME>ADVANCE EMI</CHARGE_NAME> <CHARGE_AMOUNT>0.00</CHARGE_AMOUNT> </ROW> <ROW> <CHARGE_ID>1000000104</CHARGE_ID> <CHARGE_NAME>EXCESS AMOUNT</CHARGE_NAME> <CHARGE_AMOUNT>0.00</CHARGE_AMOUNT> </ROW> <ROW> <CHARGE_ID>1000000124</CHARGE_ID> <CHARGE_NAME>PRINCIPAL DUE</CHARGE_NAME> <CHARGE_AMOUNT>50000.00</CHARGE_AMOUNT> </ROW></ROOT>BUT THE REQUIREMENT IS TO SHOW RESULT AS FOLLOWING ,pls suggest<ROOT> <RESCHEDULEDATA> <ROWSET> <ROW> <CHARGE_ID>1000000103</CHARGE_ID> <CHARGE_NAME>ADVANCE EMI</CHARGE_NAME> <CHARGE_AMOUNT>0.00</CHARGE_AMOUNT> </ROW> <ROW> <CHARGE_ID>1000000104</CHARGE_ID> <CHARGE_NAME>EXCESS AMOUNT</CHARGE_NAME> <CHARGE_AMOUNT>0.00</CHARGE_AMOUNT> </ROW> <ROW> <CHARGE_ID>1000000124</CHARGE_ID> <CHARGE_NAME>PRINCIPAL DUE</CHARGE_NAME> <CHARGE_AMOUNT>50000.00</CHARGE_AMOUNT> </ROW> <ROW> <CHARGE_ID>1000000134</CHARGE_ID> <CHARGE_NAME>INTEREST DUE</CHARGE_NAME> <CHARGE_AMOUNT>23664.00</CHARGE_AMOUNT> </ROW> </ROWSET> </RESCHEDULEDATA></ROOT>prithvi nath pandey |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-21 : 09:00:37
|
Please provide your table DDL and sample data as an INSERT command to populate the table. |
|
|
|
|
|