SQL CAST from XML

Question

I try to CAST a XML Value e.g. 0.19 to INT and calkulate it with * 100.

But I get an error like: Error by converting nvarchar-Value '0.19' in the INT Type.

This is the Code: (but the Row with the "CAST" is the Problem)

insert into BELEGP(Kennung, Belegtyp, Belegnummer, Posnummer, Postext, Zeilentyp, Menge, Steuerprozent, Eingabemenge, Editmenge, Artikelnummer, Bezeichnung, Preismenge, Einzelpreis, Gesamtpreis, Netto, Adressnr)

   SELECT  NEWID() as Kennung,
       'F' as Belegtyp,
       Myorder.j.value('OrderNumber[1]', 'varchar(50)') as Belegnummer,
       ROW_NUMBER() OVER (ORDER BY Myorder.j.value('OrderNumber[1]', 'varchar(50)')) as  Posnummer,
       ROW_NUMBER() OVER (ORDER BY Myorder.j.value('OrderNumber[1]', 'varchar(50)')) as  Postext,
       'A' as Zeilentyp,
       MyItem.j.value('Quantity[1]', 'varchar(50)') as Menge,
       CAST(Cast(MyItem.j.value('TaxRate[1]', 'nvarchar(50)') as nvarchar) as INT) * 100 as Steuerprozent,
       MyItem.j.value('Quantity[1]', 'varchar(50)') as Eingabemenge,
       MyItem.j.value('Quantity[1]', 'varchar(50)') as Editmenge,
       MyItem.j.value('Id[1]', 'varchar(50)') as Artikelnummer,
       MyItem.j.value('Name[1]', 'varchar(50)') as Bezeichnung,
       MyItem.j.value('TotalPrice[1]', 'varchar(50)') as Preismenge,
       MyItem.j.value('TotalPrice[1]', 'varchar(50)') as Einzelpreis,
       MyItem.j.value('TotalPrice[1]', 'varchar(50)') as Gesamtpreis,
       MyItem.j.value('UnityPrice[1]', 'varchar(50)') as Netto,
       Myorder.j.value('CustomerNumber[1]', 'varchar(50)') as Adressnr

   FROM (
 SELECT CAST(x AS XML)
 FROM
 OPENROWSET( BULK 'D:\shop\xml\Bestellungen.xml',SINGLE_BLOB) AS k(x))
 AS k(x)
 CROSS APPLY x.nodes('Orders/Order') AS MYorder(j)
 CROSS APPLY x.nodes('Orders/Order/Addresses/BillingAddress') AS MYAddresses(j)
 CROSS APPLY x.nodes('Orders/Order/LineItems/LineItem') AS MYItem(j)

score 2 · Accepted Answer · answered Aug 13 '13 at 12:48

2

Even if you could cast value 0.19 to int it would be rounded to 0. I think you have to fetch value as decimal and then do you conversion:

declare @Data xml = '<TaxRate>0.19</TaxRate>'
select cast(@Data.value('TaxRate[1]', 'decimal(29, 2)') * 100 as int)

answered Aug 13 '13 at 12:48

Roman Pekar

107,110
28
195
197

this works: CAST(Cast(MyItem.j.value('TaxRate[1]', 'nvarchar(50)') as nvarchar) as decimal(29,2)) * 100 as Steuerprozent, – user2675045 Aug 13 '13 at 12:54
yes, but you could get value from xml right into decimal, no need to get it into varchar and then cast – Roman Pekar Aug 13 '13 at 12:56
Yes !!! -> MyItem.j.value('TaxRate[1]', 'decimal(29,2)') * 100 as Steuerprozent, it is realy better!! – user2675045 Aug 13 '13 at 13:00

SQL CAST from XML

1 Answers1