asked
XML -Replacing value of
I am trying to update some XML in a variable and I am having trouble with the syntax. declare @x xml = N' ' I want to update v for specific values of k. For example where k="user name" I want to update v The following syntax will update the first v (in this case "timestamp") set @x.modify('replace value of (/LOG/ERR/@v)[1] with test') The number of ERR nodes can change so I can't simply change the [1] to [5]. Any one know the proper syntax? Thanks
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
Never liked working with XML's and it's probably “The Hard Way” of doing it but If there is no syntax suitable for your requirements I’d load that small xml into a table, modify it and put it back in xml form… declare @x xml = N' ' DECLARE @hDoc AS INT EXEC sp_xml_preparedocument @hDoc OUTPUT, @x IF OBJECT_ID('tempdb..#xXML') IS NOT NULL EXEC ('DROP TABLE #xXML') SELECT k,v INTO #xXML FROM OPENXML(@hDoc, 'LOG/ERR') WITH ( k NVARCHAR(100) '@k', v NVARCHAR(100) '@v' ) -- Modify in table #xXML SELECT k AS [@k], v AS [@v] FROM #xXML FOR XML PATH ('ERR'), ROOT('LOG') EXEC sp_xml_removedocument @hDoc
The simplest way is to convert the XML to a string, manipulate it, and convert back to XML select cast( replace(cast(@x as varchar(max)), 'k="user name" v=" "', 'k="user name" v="test"') as xml)
16 People are following this question.
