MS SQL Server
  1. MS SQL Server
  3. Modification du champ de date dans un document XML stocké dans la table SQL Server
Intereting Posts
Exécuter une requête SQL avec des critères de correspondance minimum dans sql server 2008 Le thread de travail de requête parallèle était impliqué dans un blocage Algorithme pour le numéro de série généré automatiquement en sql Quelle est la contrepartie de SUM () dans SQL Server? Comment stocker des données très très grandes plus de varchar (max) dans une colonne SQL Server? Foreach ADO Enumerator n'affiche pas la variable de source d'object ADO SQL Server UNION – Quel est le comportement ORDER BY par défaut Les données de vue SQL ne sont pas mises à jour correctement Comment utiliser l'opérateur IN dans SQL pour une string générée dynamicment? SQL Union provoque "La conversion a échoué lors de la conversion de la valeur varchar en int" FK référençant PK avec la même colonne Exécution d'un script volumineux dans SQL Server 2008 Comment utiliser Dense Rank et générer automatiquement des dates Y a-t-il des différences de performances entre les éditions SQL Server Web et Express? Ajout de lettres en fonction du nombre de transactions dans SQL SERVER

Modification du champ de date dans un document XML stocké dans la table SQL Server

J'ai un document XML stocké dans une colonne xml de table SQL Server qui a des heures inexactes dans les champs de date

J'aimerais mettre à jour toutes les valeurs de la date et de l'heure (SaleDateTime, LineStartTime, LineEndTime) dans le document de 15 secondes, par exemple, 2012-02-01T00: 07: 50 devient 2012-02-01T00: 08: 05 ( longue histoire quant à savoir pourquoi il doit être de cette façon, c'est hors de mes mains). Il peut y avoir de 1 à plusieurs transactions et chaque transaction peut avoir 1 ou plusieurs inputs de ligne.

J'ai essayé OPENXML, modifier la méthode, etc avec DATEADD et je ne peux pas le faire correctement. Je suis à bout de nerfs. Toute aide est appréciée. Merci d'avance!!

Échantillon est ci-dessous 

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

Vous pouvez utiliser la méthode modify . Par exemple pour replace la première SaleDateTime de SaleDateTime : 

 declare @now datetime = getdate() update XMLTable set doc.modify('replace value of (/Root/Transaction/Sale/SaleDateTime/text())[1] with sql:variable("@now")')

Si ces documents XML ne sont pas énormes, ou s'il s'agit d'une chose ponctuelle et que les performances ne sont pas une priorité, vous pouvez convertir le document en tant que varchar et effectuer une REPLACE dessus pour incrémenter les dates.

Un exemple est ci-dessous (vous pourriez envelopper ceci dans une fonction): 

 declare @doc xml = '<?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>' declare @New xml = @doc; ;with dates (LineStartTime, LineEndTime) as ( -- get the start/end dates in any LineItem select pnvalue('(LineStartTime)[1]', 'datetime'), pnvalue('(LineEndTime)[1]', 'datetime') from @doc.nodes('Root/Transaction/Sale/LineItem')p(n) ), upd (OldValue, NewValue) as ( -- add 15 min to each, and cast as varchar select '<LineStartTime>' + convert(varchar, LineStartTime, 126) + '</LineStartTime>', '<LineStartTime>' + convert(varchar, dateadd(mi, 15, LineStartTime), 126) + '</LineStartTime>' from dates union select '<LineEndTime>' + convert(varchar, LineEndTime, 126) + '</LineEndTime>', '<LineEndTime>' + convert(varchar, dateadd(mi, 15, LineEndTime), 126) + '</LineEndTime>' from dates ) -- cast @doc as varchar, and replace each occurrence of start/end elements with NewValue select @new = cast(replace(cast(@new as varchar(max)), OldValue, NewValue) as xml) from upd; select [Old]=@doc, [New]=@new;

Si la structure du document XML vous est connue, l'option la plus rapide pourrait être de séparer le document et de le rebuild en utilisant for xml . Cette solution peut également être facilement modifiée pour effectuer la transformation sur plusieurs lignes dans une table en une seule fois au lieu de traiter un document à la fois.

Pour la structure donnée dans la question, la requête par rapport à une variable XML ressemblerait à ceci. 

 select TXvalue('(SaleID/text())[1]', 'int') as SaleID, ( select dateadd(second, 15, SXvalue('(SaleDateTime/text())[1]', 'datetime')) as SaleDateTime, ( select LXvalue('(Line/text())[1]', 'int') as Line, dateadd(second, 15, LXvalue('(LineStartTime/text())[1]', 'datetime')) as LineStartTime, dateadd(second, 15, LXvalue('(LineEndTime/text())[1]', 'datetime')) as LineEndTime, LXvalue('(Amount/text())[1]', 'varchar(20)') as Amount from SXnodes('LineItem') as L(X) for xml path('LineItem'), type ) from TXnodes('Sale') as S(X) for xml path('Sale'), type ) from @doc.nodes('/Root/Transaction') as T(X) for xml path('Transaction'), root('Root'), type

Comme je l'ai dit, cela peut être modifié pour fonctionner à la place contre une table et même être changé en mise à jour de la colonne XML.