SQL Update From XML always return null

Question

I have a bit of problem, I get this code from the internet and i alter it for my own use. i run the sample and it works fine, no problem. after i change it for my program, it doesnt show any error, just 0 rows affected. turned out, the xml column return nothing.I'm hoping for some hint.

declare @input XML 

set @input = '<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <CurrencyTransact>
<BCurrencyCode>USD</BCurrencyCode>
<StockBalance>18000.00</StockBalance>
</CurrencyTransact>
<CurrencyTransact>   
<BCurrencyCode>MYR</BCurrencyCode>
 <StockBalance>30000.00</StockBalance>
</CurrencyTransact>

'

;with GrabXML AS
 (
  select
       CurrencyBal.value('@BCurrencyCode', 'varchar(10)') as 'BCurrencyCode',
       CurrencyBal.value('@StockBalance', 'decimal(18,2)') as 'StockBalance'
       from
       @input.nodes('/NewDataSet/CurrencyTransact') as n(CurrencyBal)
)

update dbo.Currency
set StockBalance = g.StockBalance
from GrabXML g
where dbo.Currency.CurrencyCode = g.BCurrencyCode

Muhammad Nazri · Accepted Answer · 2015-10-05 21:56:34Z

1

i just altered this line to make it works.Problem solve!

   CurrencyBal.value('(BCurrencyCode)[1]', 'varchar(10)') as 'BCurrencyCode',
   CurrencyBal.value('(StockBalance)[1]', 'decimal(18,2)') as 'StockBalance'

answered Oct 5, 2015 at 21:56

Muhammad Nazri

251 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Update From XML always return null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related