CODEWITHSUNDEEP
sql-serverxmlxpath
Mike B
Mike B

Reputation: 644

SQL REPLACE function inside Xml.modify 'replace value of'

I'm trying to update some XML data in SQL Server. The XML contains data that looks like this:

<root>
    <id>1</id>
    <timestamp>16-10-2017 19:24:55</timestamp>
</root>

Let's say this XML exists in a column called Data in a table called TestTable. I would like to be able to change the hyphens in the timestamp to forward slashes.

I was hoping I might be able to do something like:

update TestTable
set Data.modify('replace value of 
(/root/timestamp/text())[1] with REPLACE((/root/timestamp/text())[1], "-", "/")')

I get the following error:

XQuery [TestTable]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:REPLACE()'

When I think about it, this makes sense. But I wonder, is there a way to do this in a single update statement? Or do I first need to query the timestamp value and save it as a variable, and then update the XML with the variable?

Upvotes: 5

Views: 5423

Answers (4)

TrystanC
TrystanC

Reputation: 341

You can also do this with a join to an inline view and use the SQL REPLACE function:

   CREATE TABLE TestTable
   (    
       Id INT IDENTITY(1,1) NOT NULL,
       Data XML NOT NULL
   )

   INSERT TestTable (Data) VALUES ('<root>
        <id>1</id>
        <timestamp>16-10-2017 19:24:55</timestamp>
    </root>')

    UPDATE TestTable
    SET Data.modify('replace value of 
    (/root/timestamp/text())[1] with sql:column("T2.NewData")')
    FROM TestTable T1
    INNER JOIN (
        SELECT Id
        , REPLACE( Data.value('(/root/timestamp/text())[1]', 'nvarchar(max)'), '-', '/') AS NewData
        FROM TestTable
    ) T2
    ON T1.Id = T2.Id

    SELECT * FROM TestTable

Upvotes: 4

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If there's no external need you have to fullfill, you should use ISO8601 date/time strings within XML.

Your dateTime-string is culture related. Reading this on different systems with differing language or dateformat settings will lead to errors or - even worse!!! - to wrong results.

A date like "08-10-2017" can be the 8th of October or the 10th of August...

The worst point is, that this might pass all your tests successfully, but will break on a customer's machine with strange error messages or bad results down to real data dammage!

Switching the hyphens to slashes is just cosmetic! An XML is a strictly defined data container. Any non-string data must be represented as a secure convertible string.

This is what you should do:

DECLARE @tbl TABLE(ID INT IDENTITY,YourXML XML);
INSERT INTO @tbl VALUES
(N'<root>
    <id>1</id>
    <timestamp>16-10-2017 19:24:55</timestamp>
</root>');

UPDATE @tbl SET YourXml.modify(N'replace value of (/root/timestamp/text())[1] 
                                 with concat(  substring((/root/timestamp/text())[1],7,4), "-"
                                              ,substring((/root/timestamp/text())[1],4,2), "-"
                                              ,substring((/root/timestamp/text())[1],1,2), "T"
                                              ,substring((/root/timestamp/text())[1],12,8)
                                            ) cast as xs:dateTime?');

SELECT * FROM @tbl;

The result

<root>
  <id>1</id>
  <timestamp>2017-10-16T19:24:55</timestamp>
</root>

Upvotes: 1

TT.
TT.

Reputation: 16145

Note: this answer assumes you want to have this formatted for the purpose of displaying this as a string, and not parsing the content as a xs:dateTime. If you want the latter, Shungo's answer will format it as such.

It seems that replace is not a supported XQuery function in SQL Server at the time of this writing. You can use the substring function along with the concat function in a "replace value of (XML DML)" though.

CREATE TABLE #t(x XML);
INSERT INTO #t(x)VALUES(N'<root><id>1</id><timestamp>16-10-2017 19:24:55</timestamp></root>');

UPDATE
    #t
SET
    x.modify('replace value of (/root/timestamp/text())[1]
              with concat(substring((/root/timestamp/text())[1],1,2),
                          "/",
                          substring((/root/timestamp/text())[1],4,2),
                          "/",
                          substring((/root/timestamp/text())[1],7)
                         ) ') 

SELECT*FROM #t;

Giving as a result:

<root><id>1</id><timestamp>16/10/2017 19:24:55</timestamp></root>

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17126

you can try string replacement like below

update testtable 
set data= cast(
concat(
    left(cast(data as varchar(max)),charindex('<timestamp>',cast(data as varchar(max)))+len('<timestamp>')-1),
    replace(
        substring(
                    cast(data as varchar(max)),
                    len('<timestamp>')   + 
                        charindex( '<timestamp>', cast(data as varchar(max))) ,
                    charindex('</timestamp>',cast(data as varchar(max)))
                        -charindex('<timestamp>',cast(data as varchar(max)))
                        -len('<timestamp>')
                 ),
        '-','/'),
    right(cast(data as varchar(max)),len(cast(data as varchar(max)))-charindex('</timestamp>',cast(data as varchar(max)))+1)
       ) as xml)

select * 
from testtable

working demo

Upvotes: 0

Related Questions