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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 xml help

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) A
FOR 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.
Go to Top of Page
   

- Advertisement -