Keith Keith - 1 year ago 94
SQL Question

SQL XML find and replace

So I'm dealing with a field in a table that contains XML data and from line to line the number of parameters in the XML field will vary (as will the name of the variables).

I need to be able to search a field containing XML for my


(I did not design the XML structure, I'm just the one who has to work around it) and replace the
<ValueAsString>data here</ValueAsString>

with new data that is dynamically generated by a trigger that monitors table changes.

Because of how the XML is setup I've spent days trying to figure this out but I'm at a loss. Can anyone help? The Trigger part is easy it's find the right XML location to replace that I'm having a hard time with.

<Parameter><VariableID>(1012,14505)</VariableID><VariableName>ArtworkFormat</VariableName><ListID>(1042,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(2226,14505)</VariableID><VariableName>ArtworkProofType</VariableName><ListID>(1045,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(2224,14505)</VariableID><VariableName>ArtworkReceivedVia</VariableName><ListID>(1043,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(2225,14505)</VariableID><VariableName>ArtworkReturnVia</VariableName><ListID>(1044,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(10306,14505)</VariableID><VariableName>tminus1</VariableName><ValueAsString>10/12/2016 4:00 PM</ValueAsString></Parameter>
<Parameter><VariableID>(10308,14505)</VariableID><VariableName>tminus3</VariableName><ValueAsString>10/10/2016 4:00 PM</ValueAsString></Parameter>
<Parameter><VariableID>(10307,14505)</VariableID><VariableName>tminus2</VariableName><ValueAsString>10/11/2016 4:00 PM</ValueAsString></Parameter>

Answer Source

One- or Two-step replacement

As others pointed out, there is a problem, if you want to replace the text value of a node given as <ValueAsString />. The .modify(N'replace value of... demands for a /text() at the end as target for the replacment, but there isn't... The simple way will work for the given case, but will not work if in the <Parameter> you want to change the target node is empty as it is in VariableName="ArtworkReturnVia":

DECLARE @SearchFor NVARCHAR(100)='tminus1';
DECLARE @SetValue NVARCHAR(100)='NewData';

UPDATE @tbl SET XmlColumn.modify(N'replace value of (/Parameters/Parameter[VariableName=sql:variable("@SearchFor")]/ValueAsString)[1]/text()[1]
                                   with sql:variable("@SetValue")')

One straight approach was to delete this element in any case and do an insert:

UPDATE @tbl SET XmlColumn.modify(N'delete (/Parameters/Parameter[VariableName=sql:variable("@SearchFor")]/ValueAsString)[1]')
UPDATE @tbl SET XmlColumn.modify(N'insert <ValueAsString>{sql:variable("@SetValue")}</ValueAsString> into (/Parameters/Parameter[VariableName=sql:variable("@SearchFor")])[1]')

One more approach: Full FLWOR

UPDATE @tbl SET XmlColumn=XmlColumn.query
    for $p in /Parameters/Parameter
    return <Parameter>
    if($p/*/text()=sql:variable("@SearchFor")) then
            for $nd in $p/*
            if(local-name($nd)="ValueAsString") then
    else $p

Another one: Semi FLWOR

    SELECT ID --needs an ID here
          ,p.query('.') AS prm
    FROM @tbl 
    CROSS APPLY XmlColumn.nodes('/Parameters/Parameter') AS A(p)
UPDATE tbl SET XmlColumn=
SELECT CASE WHEN prm.value('(/Parameter/VariableName)[1]','nvarchar(max)')=@SearchFor 
            THEN prm.query('<Parameter>
                            for $nd in /Parameter/*
                                if(local-name($nd)="ValueAsString") then
            ELSE prm END
WHERE Prms.ID=tbl.ID
ORDER BY Prms.NodeOrder
FOR XML PATH(''),ROOT('Parameters')
FROM @tbl AS tbl
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download