I have XML in TSQL,
Declare @xml XML = '<soap:Envelope xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" xmlns:urn1="urn:core_2015_2.platform.webservices.netsuite.com" xmlns:urn="urn:messages_2015_2.platform.webservices.netsuite.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" xmlns:urn1="urn:core_2015_2.platform.webservices.netsuite.com" xmlns:urn="urn:messages_2015_2.platform.webservices.netsuite.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<upsertList>
<q1:record xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" xsi:type="q1:CustomRecord" externalId="F95C35CF-950E-4756-8C33-43CA0C47FF45">
<q1:recType internalId="12" type="customRecord"/>
<q1:customFieldList xmlns="urn:core_2015_2.platform.webservices.netsuite.com">
<customField scriptId="custrecord_sps_content_package" xsi:type="SelectCustomFieldRef">
<value internalId="25"/>
</customField>
<customField scriptId="custrecord_sps_content_item" xsi:type="SelectCustomFieldRef">
<value internalId="1849"/>
</customField>
<customField scriptId="custrecord_sps_content_qty" xsi:type="LongCustomFieldRef">
<value>6.00</value>
</customField>
</q1:customFieldList>
</q1:record>
<q1:record xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" externalId="D596F4DB-D7FE-409A-9D40-916FF88FB188">
<q1:recType internalId="12" type="customRecord"/>
<q1:customFieldList xmlns="urn:core_2015_2.platform.webservices.netsuite.com">
<customField scriptId="custrecord_sps_content_package" xsi:type="SelectCustomFieldRef">
<value internalId="24"/>
</customField>
<customField scriptId="custrecord_sps_content_item" xsi:type="SelectCustomFieldRef">
<value internalId="1902"/>
</customField>
<customField scriptId="custrecord_sps_content_qty" xsi:type="LongCustomFieldRef">
<value>2.00</value>
</customField>
</q1:customFieldList>
</q1:record>
</upsertList>
</soap:Body>
</soap:Envelope>'
Now, I want to find customField has attribute => xsi:type="LongCustomFieldRef" then update Value to Integer. Mean I want to have Integer: 6 instead of 6.00
<customField scriptId="custrecord_sps_content_qty" xsi:type="LongCustomFieldRef">
<value>6</value>
</customField>
I do not know, why you need this. A number with a .00 will be casteable to an int without any troubles. If this is primarly cosmetic, I'd not touch this... If you need this (might be due to a very strict schema check), you can walk this road, but it's not trivial:
As you probably know, XML's .modify() can update only one value per call. This is quite limitating.
If your structure is always the same, you might use a CTE to shredd this into pieces and re-construct the XML from scratch. But this would lead into new troubles with your namespaces assumably.
You can try this:
--Your Xml
DECLARE @xml XML = '<soap:Envelope xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com"
xmlns:urn1="urn:core_2015_2.platform.webservices.netsuite.com"
xmlns:urn="urn:messages_2015_2.platform.webservices.netsuite.com"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" xmlns:urn1="urn:core_2015_2.platform.webservices.netsuite.com" xmlns:urn="urn:messages_2015_2.platform.webservices.netsuite.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<upsertList>
<q1:record xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" xsi:type="q1:CustomRecord" externalId="F95C35CF-950E-4756-8C33-43CA0C47FF45">
<q1:recType internalId="12" type="customRecord"/>
<q1:customFieldList xmlns="urn:core_2015_2.platform.webservices.netsuite.com">
<customField scriptId="custrecord_sps_content_package" xsi:type="SelectCustomFieldRef">
<value internalId="25"/>
</customField>
<customField scriptId="custrecord_sps_content_item" xsi:type="SelectCustomFieldRef">
<value internalId="1849"/>
</customField>
<customField scriptId="custrecord_sps_content_qty" xsi:type="LongCustomFieldRef">
<value>6.00</value>
</customField>
</q1:customFieldList>
</q1:record>
<q1:record xmlns:q1="urn:customization_2015_2.setup.webservices.netsuite.com" externalId="D596F4DB-D7FE-409A-9D40-916FF88FB188">
<q1:recType internalId="12" type="customRecord"/>
<q1:customFieldList xmlns="urn:core_2015_2.platform.webservices.netsuite.com">
<customField scriptId="custrecord_sps_content_package" xsi:type="SelectCustomFieldRef">
<value internalId="24"/>
</customField>
<customField scriptId="custrecord_sps_content_item" xsi:type="SelectCustomFieldRef">
<value internalId="1902"/>
</customField>
<customField scriptId="custrecord_sps_content_qty" xsi:type="LongCustomFieldRef">
<value>2.00</value>
</customField>
</q1:customFieldList>
</q1:record>
</upsertList>
</soap:Body>
</soap:Envelope>';
--Write it into a temp table
SELECT @xml AS TheXml INTO #tmpXml;
--Read all values with the given xsi:type where the value contains a dot
--Attention: Your customFieldList defines a new default namespace!
WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS [soap]
,'urn:customization_2015_2.setup.webservices.netsuite.com' AS q1
,'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'urn:core_2015_2.platform.webservices.netsuite.com' AS innerDflt)
SELECT r.value('@externalId','uniqueidentifier') AS Record_ExternalId
,cf.value('@scriptId','nvarchar(max)') AS CustomField_ScriptId
,cf.value('(innerDflt:value/text())[1]','decimal(10,4)') AS OriginalValue
,CAST(cf.value('(innerDflt:value/text())[1]','decimal(10,4)') AS INT) AS CastedValue
INTO #tmpValues
FROM #tmpXml
CROSS APPLY TheXml.nodes('/soap:Envelope
/soap:Body
/upsertList
/q1:record') AS A(r)
CROSS APPLY A.r.nodes('q1:customFieldList
/innerDflt:customField[@xsi:type="LongCustomFieldRef" and innerDflt:value[contains(text()[1],".")]]') AS B(cf);
--Intermediate results
SELECT * FROM #tmpXml
SELECT * FROM #tmpValues;
--Use a CURSOR to read down the lines and .modify() to replace the "wrong" values.
DECLARE @rId NVARCHAR(MAX), @fId NVARCHAR(MAX), @v NVARCHAR(MAX);
DECLARE cur CURSOR FOR SELECT Record_ExternalId
,CustomField_ScriptId
,CAST(CastedValue AS NVARCHAR(MAX))
FROM #tmpValues;
OPEN cur;
FETCH NEXT FROM cur INTO @rId,@fId,@v;
WHILE @@FETCH_STATUS=0
BEGIN
WITH XMLNAMESPACES( 'http://schemas.xmlsoap.org/soap/envelope/' AS [soap]
,'urn:customization_2015_2.setup.webservices.netsuite.com' AS q1
,'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'urn:core_2015_2.platform.webservices.netsuite.com' AS innerDflt)
UPDATE #tmpXml SET TheXml.modify('replace value of (/soap:Envelope
/soap:Body
/upsertList
/q1:record[@externalId=sql:variable("@rId")]
/q1:customFieldList
/innerDflt:customField[@scriptId=sql:variable("@fId")]
/innerDflt:value
/text())[1] with sql:variable("@v")');
FETCH NEXT FROM cur INTO @rId,@fId,@v;
END
CLOSE cur;
DEALLOCATE cur;
--Reset the @xml
SET @xml=(SELECT TheXml FROM #tmpXml);
--Final result
SELECT @xml;