Help with XML update

[gepett...@yahoo.com]

I have an xml document stored in a table column that has inaccurate times in the date fields

I 'd like to update all LineStartTime and LineEndTime values in the document by 15 seconds so, for instance, 2012-02-01T00:07:00 becomes 2012-02-01T00:07:15 (long story as to why it needs to be this way; it's out of my hands). There can be 1 to many transactions and each transaction can have 1 or more line entries.

I have tried OPENXML, modify method, etc and I can't get it right. I am
at my wits end. Any help is appreciated. Thanks in advance!!

Sample is below

CREATE TABLE XMLTable (doc xml);

INSERT INTO XMLTable (doc)
VALUES
(
'<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Transaction>
<SaleID>1</SaleID>
<Sale>
<SaleDateTime>2012-02-01T00:07:00</SaleDateTime>
<LineItem>
<Line>1</Line>
<LineStartTime>2012-02-01T00:07:00</LineStartTime>
<LineEndTime>2012-02-01T00:07:00</LineEndTime>
<Amount>13.50</Amount>
</LineItem>
</Sale>
</Transaction>
<Transaction>
<SaleID>2</SaleID>
<Sale>
<SaleDateTime>2012-02-01T00:11:00</SaleDateTime>
<LineItem>
<Line>1</Line>
<LineStartTime>2012-02-01T00:11:00</LineStartTime>
<LineEndTime>2012-02-01T00:11:00</LineEndTime>
<Amount>13.50</Amount>
</LineItem>
<LineItem>
<Line>2</Line>
<LineStartTime>2012-02-01T00:11:00</LineStartTime>
<LineEndTime>2012-02-01T00:11:00</LineEndTime>
<Amount>5.22</Amount>
</LineItem>
</Sale>
</Transaction>
</Root>')

SELECT * FROM XMLTable

[Erland Sommarskog]

> I have an xml document stored in a table column that has inaccurate
> times in the date fields
>
> I 'd like to update all LineStartTime and LineEndTime values in the
> document by 15 seconds so, for instance, 2012-02-01T00:07:00 becomes
> 2012-02-01T00:07:15 (long story as to why it needs to be this way; it's

> out of my hands).. There can be 1 to many transactions and each

> transaction can have 1 or more line entries.
>
> I have tried OPENXML, modify method, etc and I can't get it right. I am
> at my wits end. Any help is appreciated. Thanks in advance!!

I first tried .modify, but the first roadblock is that there are no
datetime operations in XQuery in SQL Server. I decided to cheat with
concat, assuming that the seconds are always 00. After some battling,
I consulted my MVP mates, and I leart that replace value of only handles
singleton values. You could write a loop where you use sqlvariable, but
I could not get that to work.

I gave up XQuery, and instead wrote the boring solution: extract to a
table, and then write back with FOR XML. If you have many rows in the table,
you will to take a cursor.

DECLARE @t TABLE (SaleID int NOT NULL,
SaleDateTime datetime2(0) NOT NULL,
Line int NOT NULL,
LineStartTime datetime2(0) NOT NULL,
LineEndTime datetime2(0) NOT NULL,
Amount decimal(10, 2) NOT NULL,
PRIMARY KEY (SaleID, Line))

INSERT @t (SaleID, SaleDateTime, Line, LineStartTime, LineEndTime, Amount)
SELECT T.c.value('(SaleID/text())[1]', 'int'),
S.c.value('(SaleDateTime/text())[1]', 'datetime2(0)'),
L.c.value('(Line/text())[1]', 'int'),
L.c.value('(LineStartTime/text())[1]', 'datetime2(0)'),
L.c.value('(LineEndTime/text())[1]', 'datetime2(0)'),
L.c.value('(Amount/text())[1]', 'decimal(10,2)')
FROM XMLTable
CROSS APPLY doc.nodes('/Root/Transaction') AS T(c)
CROSS APPLY T.c.nodes('Sale') AS S(c)
CROSS APPLY S.c.nodes('LineItem') AS L(c)

UPDATE @t
SET LineStartTime = dateadd(second, 15, LineStartTime),
LineEndTime = dateadd(second, 15, LineEndTime)

UPDATE XMLTable
SET doc = (
SELECT s.SaleID AS [SaleID],
s.SaleDateTime AS [Sale/SaleDateTime],
(SELECT t.Line,
t.LineStartTime,
t.LineEndTime,
t.Amount
FROM @t t
WHERE s.SaleID = t.SaleID
FOR XML PATH('LineItem'), TYPE) AS [Sale]
FROM (SELECT DISTINCT SaleID, SaleDateTime FROM @t) AS s
FOR XML PATH('Transaction'), ROOT('Root')

)




SELECT * FROM XMLTable

go
DROP TABLE XMLTable

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Here is a second solution that I got from my friend and MVP mate Alejandro
Mesa. It uses .modify and some other XQuery stuff that I don't master too
well myself.

SET NOCOUNT ON;
USE tempdb;
GO

DECLARE @c CURSOR, @sk int, @elem_rn int, @elem_name nvarchar(128),
@elem_new_value datetime;
DECLARE @XMLTable TABLE (sk int NOT NULL IDENTITY (1, 1) PRIMARY KEY, doc
xml);

INSERT INTO @XMLTable (doc)

</Root>'),

(

'<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Transaction>

<SaleID>17</SaleID>
<Sale>
<SaleDateTime>2013-02-02T00:10:00</SaleDateTime>
<LineItem>
<Line>1</Line>
<LineStartTime>2013-02-01T00:10:00</LineStartTime>
<LineEndTime>2013-02-01T00:11:00</LineEndTime>
<Amount>13.50</Amount>
</LineItem>
</Sale>
</Transaction>

</Root>');
SET @c = CURSOR LOCAL STATIC FOR


SELECT T.sk,
DENSE_RANK() OVER(PARTITION BY T.sk ORDER BY L.n) AS elem_rn,
R.n.value('local-name(.)', 'nvarchar(128)') AS elem_name,
DATEADD([second], 15, R.n.value('(text())[1]', 'datetime')) as
elem_new_value
FROM @XMLTable AS T
CROSS APPLY T.doc.nodes('/Root/Transaction/Sale/LineItem') as L(n)
CROSS APPLY L.n.nodes('LineStartTime,LineEndTime') AS R(n)
ORDER BY sk,elem_rn;

OPEN @c;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM @c INTO @sk, @elem_rn, @elem_name, @elem_new_value;
IF @@ERROR <> 0 OR @@FETCH_STATUS <> 0 BREAK;

UPDATE @XMLTable
SET doc.modify('
replace value of (/Root/Transaction/Sale/LineItem/*[local-name(.) =
sql:variable("@elem_name")]/text())[position()=sql:variable("@elem_rn")][1]
with sql:variable("@elem_new_value")
')
WHERE sk = @sk;
END;

SELECT * FROM @XMLTable;


GO