2 Replies Latest reply: May 14, 2013 11:23 PM by 894936 RSS

    convert varchar to decimal in insert statement

      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)
               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