Modify the value in xml with proper format

Question

I have a table with xml data. For some reason certain fields in the xml value has inconsistent data. I need to modify it accordingly. In below template we can see for the field txtCapacity, the value has out of range value. I need to convert it to proper format make it as float ?

Below is the table structure

Table XML_Capacity 
( RID int,
 xml_detail xml
) 

 Xml template

     <Form>
 <LT004>
<Field>
  <id>txtDate</id>
  <value>3/14/2017</value>
  <tag />
  <visible>true</visible>
    <description>Install Date:</description>
  <comment />
</Field>
<Field>
  <id>txtAge</id>
  <value />
  <tag />
  <visible>true</visible>
   <description>Age:</description>
  <comment />
</Field>
<Field>
  <id>txtCapacity</id>
  **<value>4.000000000000000e+003</value>**
  <tag />
  <visible>true</visible>
    <description> Capacity:</description>
  <comment />
</Field>
<Field>
  <id>Status</id>
  <value>Upgrade Repair Not Marked</value>
  <tag />
  <visible>true</visible>
    <description>Status</description>
  <comment />
</Field>
<Field>
  <id>RemovedDate</id>
  <value />
  <tag />
  <visible>false</visible>
    <description>Date:</description>
  <comment />
 </Field>

  I used the below code to modify but it is not allowing me to use convert   
  function inside that update statement

 UPDATE XML_Capacity 
 SET xml_detail.modify('replace value of (/Form/L004/Field/text())[16] with    (/FormValue/L004/Field/text())[16]')
Where 
xml_detail.value('(/Form/L004/Field/node())[15]', 'varchar(45)') = 'txtCapacity'

Answer 1

I do not understand your statement

In below template we can see for the field txtCapacity, the value has out of range value

How can we see this? What is out of range?

This is the normal way, a float value is stored within XML. Try this

SELECT CAST(10.0/3 AS FLOAT(5)) AS SimpleFloat
      ,CAST(10.0/3 AS FLOAT(53)) AS MaxFloat
      ,CAST(10000.0/3 AS FLOAT(5)) AS SimpleFloatBig
      ,CAST(10000.0/3 AS FLOAT(53)) AS MaxFloatBig
FOR XML RAW,ELEMENTS

The result

<row>
  <SimpleFloat>3.3333330e+000</SimpleFloat>
  <MaxFloat>3.333333000000000e+000</MaxFloat>
  <SimpleFloatBig>3.3333333e+003</SimpleFloatBig>
  <MaxFloatBig>3.333333333000000e+003</MaxFloatBig>
</row>

This is simply the scientific notation with a power to ten. You can read this easily with implicit conversion like this:

DECLARE @xml XML=
'<row>
  <SimpleFloat>3.3333330e+000</SimpleFloat>
  <MaxFloat>3.333333000000000e+000</MaxFloat>
  <SimpleFloatBig>3.3333333e+003</SimpleFloatBig>
  <MaxFloatBig>3.333333333000000e+003</MaxFloatBig>
</row>';
SELECT @xml.value(N'(/row/SimpleFloatBig/text())[1]',N'float(53)')

...which returns 3333.3333.

In your example you are doing something very dangerous: You try to address the node with its ordinal position (e.g. '(/Form/L004/Field/node())[15]'). You should rather use the proper XQuery predicate to get hands on the right node. Check this fully working test scenario:

CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE XML_Capacity 
( RID int,
 xml_detail xml
);

INSERT INTO XML_Capacity(RID,xml_detail)
VALUES(1,
N'<Form>
  <LT004>
    <Field>
      <id>txtDate</id>
      <value>3/14/2017</value>
      <tag />
      <visible>true</visible>
      <description>Install Date:</description>
      <comment />
    </Field>
    <Field>
      <id>txtAge</id>
      <value />
      <tag />
      <visible>true</visible>
      <description>Age:</description>
      <comment />
    </Field>
    <Field>
      <id>txtCapacity</id>
      <value>4.000000000000000e+003</value>
      <tag />
      <visible>true</visible>
      <description> Capacity:</description>
      <comment />
    </Field>
    <Field>
      <id>Status</id>
      <value>Upgrade Repair Not Marked</value>
      <tag />
      <visible>true</visible>
      <description>Status</description>
      <comment />
    </Field>
    <Field>
      <id>RemovedDate</id>
      <value />
      <tag />
      <visible>false</visible>
      <description>Date:</description>
      <comment />
    </Field>
  </LT004>
</Form>');

 SELECT xml_detail.value(N'(/Form/LT004/Field[(id/text())[1]="txtCapacity"]/value/text())[1]',N'float') 
 FROM XML_Capacity;
 GO

 DECLARE @NewValue FLOAT=12.345;
 UPDATE XML_Capacity
 SET xml_detail.modify(N'replace value of (/Form/LT004/Field[(id/text())[1]="txtCapacity"]/value/text())[1] with sql:variable("@NewValue")')
 WHERE RID=1;


 SELECT xml_detail.value(N'(/Form/LT004/Field[(id/text())[1]="txtCapacity"]/value/text())[1]',N'float') 
 FROM XML_Capacity;

 USE master;
 GO
 DROP DATABASE TestDB;
 GO

The return values are 4000 and - after the modification - 12.345.