need help on SQL statement - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

need help on SQL statement

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-19 : 23:04:19

my table, data and sql statement as a following,


declare @x xml
set @x='<data><ag><icNo>60816105987</icNo><icNoOld></icNoOld><payerNme>Samat Bin Sani</payerNme><amt>000002000</amt>
<subCd>5500</subCd></ag><ag><icNo>51130055335</icNo><icNoOld>            
</icNoOld><payerNme>Jalaludin Bin Jalil</payerNme><amt>000004100</amt><subCd>5700</subCd></ag>
<ag><icNo>40301045163G</icNo><icNoOld>0130</icNoOld><payerNme>Abu Mansor Bin Hj Mohd Nor</payerNme>
<amt>000001500</amt><subCd>0390</subCd></ag>
</data>'

select
a.b.value('icNo[1]','varchar(200)') as icNo, --as input1,
a.b.value('icNoOld[1]','varchar(15)') as icNoOld, --as input2,
a.b.value('payerNme[1]','varchar(100)') as payerNme, --as input3,
a.b.value('amt[1]','decimal(10,2)') as amt, --as input4,
a.b.value('subCd[1]','varchar(100)') as subCd --as input5,
from
@x.nodes('/data/ag') a(b)

my <amt> always in 9 character. Current result as following,


60816105987		Samat Bin Sani	2000.00	5500
51130055335		Jalaludin Bin Jalil	4100.00	5700
40301045163G	0130	Abu Mansor Bin Hj Mohd Nor	1500.00	0390

How to make it, my amt as following,

000002000 --> 20.00
000004100 --> 41.00
000001500 --> 15.00

Looking for help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:35:02

[code]
declare @x xml
set @x='<data><ag><icNo>60816105987</icNo><icNoOld></icNoOld><payerNme>Samat Bin Sani</payerNme><amt>000002000</amt>
<subCd>5500</subCd></ag><ag><icNo>51130055335</icNo><icNoOld>
</icNoOld><payerNme>Jalaludin Bin Jalil</payerNme><amt>000004100</amt><subCd>5700</subCd></ag>
<ag><icNo>40301045163G</icNo><icNoOld>0130</icNoOld><payerNme>Abu Mansor Bin Hj Mohd Nor</payerNme>
<amt>000001500</amt><subCd>0390</subCd></ag>
</data>'

select
a.b.value('icNo[1]','varchar(200)') as icNo, --as input1,
a.b.value('icNoOld[1]','varchar(15)') as icNoOld, --as input2,
a.b.value('payerNme[1]','varchar(100)') as payerNme, --as input3,
cast(a.b.value('amt[1]','decimal(10,2)')/100.00 as decimal(10,2)) as amt, --as input4,
a.b.value('subCd[1]','varchar(100)') as subCd --as input5,
from
@x.nodes('/data/ag') a(b)

output
---------------------------------------------
icNo icNoOld payerNme amt subCd
60816105987 Samat Bin Sani 20.00 5500
51130055335 Jalaludin Bin Jalil 41.00 5700
40301045163G 0130 Abu Mansor Bin Hj Mohd Nor 15.00 0390

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-20 : 00:40:13

tq sir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:47:26

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources