This discussion is archived
2 Replies Latest reply: May 14, 2013 9:23 PM by 894936 RSS

convert varchar to decimal in insert statement

894936 Newbie
Currently Being Moderated
Hi i need one help from you.

can you please help me out

I have one insert statement where i am inserting some values.

first when i am inserting the values are fetched from 'xml' file...
so in one of the xml file i have 'varchar' values which i need to convert to decimal and i need to insert.

from the below script i need this conversion for "M.Item.query('./unitPrice').value('0.0','decimal(18,2)') unitPrice"

could you suggest me on this.
INSERT INTO BWBLineItemDetails_New(Transaction_GUID,Transaction_ID,LineItemNumber,Quantity,UnitPrice,ProductCode,Ship_From_Addr,Ship_To_Addr)
        SELECT
        @Transaction_GUID,
        @TransactionId,
         M.Item.query('./lineItemNumber').value('.','int') lineItemNumber,
          M.Item.query('./quantity').value('.','int') quantity,
          M.Item.query('./unitPrice').value('0.0','decimal(18,2)') unitPrice,
          M.Item.query('./unitPrice').value('.','decimal') unitPrice,
          M.Item.query('./productCode').value('.','nvarchar(100)') productCode,
          M.Item.query('./shipFromAddress/address1').value('.','nvarchar(255)') + '' +  M.Item.query('./shipFromAddress/address2').value('.','nvarchar(255)')shipFromAddress,
          M.Item.query('./shipToAddress/address1').value('.','nvarchar(255)') + '' +  M.Item.query('./shipToAddress/address2').value('.','nvarchar(255)')shipToAddress
Edited by: 891933 on Dec 4, 2012 5:27 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points