TSQL XML Convert node value to int

Question

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>

Answer 1

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;