Как изменить формат всех дат в xml?

Вопрос:

У меня есть xml в ячейке в SQL, например:

Таблица:

<?xml version=»1.0″ encoding=»utf-16″?> <Document xmlns:xsd=»http://www.w3.org/2001/XMLSchema» xmlns:xsi=»http://www.w3.org/2001/XMLSchema-instance»> <Page W=»2480″ H=»3516″> <Word L=»1871″ R=»2031″ T=»221″ B=»252″ Text=»INVOICE» Id=»25509747671106″ /> <Word L=»1988″ R=»2046″ T=»2232″ B=»2279″ Text=»tf.’l» Id=»25886807122412″ /> <Word L=»1872″ R=»1990″ T=»324″ B=»351″ Text=»26603333345″Id=»24493329746300″ /> <Word L=»1871″ R=»2015″ T=»373″ B=»401″ Text=»08-02-17″ Id=»25109308586898″ /> <Word L=»1873″ R=»2007″ T=»422″ B=»448″ Text=»S-44404″ Id=»24914704754685″ /> <Word L=»1874″ R=»1887″ T=»468″ B=»496″ Text=»1″ Id=»22024234663427″ /> <Word L=»1068″ R=»1148″ T=»1278″ B=»1309″ Text=»DHL» Id=»8152496756181″ /> <Word L=»1692″ R=»1848″ T=»1279″ B=»1310″ Text=»08-02-17″ Id=»21119731019927″ /> <Word L=»2096″ R=»2251″ T=»1278″ B=»1310″ Text=»10-01-17″ Id=»31333127836454″ /> <Word L=»112″ R=»243″ T=»1352″ B=»1358″ Text=»_» Id=»365589546232″ /> <Word L=»252″ R=»411″ T=»1322″ B=»1350″ Text=»QUANTITY» Id=»1050334834310″ /> <Word L=»1415″ R=»1913″ T=»745″ B=»787″ Text=»______ShlpTo» Id=»22635743273663″ /> </Page> </Document>

Мне нужно обновить [FRData] и изменить дату из формата mm-dd-yy в формат yyyy-mm-dd во всех xml.

Я написал регулярное выражение, которое проверяет формат даты:

^(0?[1-9]|1[012])[-](0?[1-9]|[12][0-9]|3[01])[-]d{2}$

Я знаю, как изменить формат даты mm-dd-yy на yyyy-mm-dd:

select left(convert(varchar, cast(’08-02-17′ as datetime), 120),10)

Но я не знаю, как изменить даты во всем xml

Лучший ответ:

Попробуйте это, пожалуйста.

declare @I int declare @X nvarchar(100) declare @D date select @I = max(FRData.value(‘count(/Document/Page//Word)’, ‘int’)) from #t while @I > 0 begin set @X = (select top 1 FRData.value(‘(/Document/Page//Word/@Text)[sql:variable(«@I»)][1]’, ‘nvarchar(100)’) from #t) if isdate(@X) = 1 begin set @D = convert(date, @X) update #t set FRData.modify(‘replace value of ((/Document/Page//Word/@Text)[sql:variable(«@I»)])[1] with sql:variable(«@D»)’) end set @I = @I — 1 end Ответ №1

Попробуйте xml linq

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml; using System.Xml.Linq; using System.IO; namespace ConsoleApplication1 { class Program { const string FILENAME = @»c:temptest.xml»; static void Main(string[] args) { StreamReader reader = new StreamReader(FILENAME,Encoding.Unicode); reader.ReadLine(); XDocument doc = XDocument.Load(reader); foreach (XElement word in doc.Descendants(«Word»)) { DateTime date; Boolean isDate = DateTime.TryParse((string)word.Attribute(«Text»), out date); if (isDate) { word.Attribute(«Text»).SetValue(date.ToString(«yyyy-MM-dd»)); } } } } } Ответ №2

Вы, вероятно, ищете: SET DATEFORMAT

SET DATEFORMAT ymd;

Документы: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql

Но это будет зависеть от вашего скрипта для создания xml, который отсутствует, потому что может потребоваться преобразовать/изменить формат даты.

Ответ №3

Вы имеете дело с очень плохим XML:

Вы должны хранить XML в изначально типизированном столбце XML. Ведущая <?xml version=»1.0″ encoding=»utf-16″> ясно показывает, что вы храните это как какую-то строку. XML не сохраняется как текст, который вы видите, а как дерево иерархии. Это делает XML невероятно быстрым. Ваш подход должен будет анализировать XML снова и снова…
Вы никогда не должны использовать форматы даты и времени, зависящие от культуры
Еще хуже: никогда не используйте формат даты с 2- 01-02-03 годом (01-02-03 будут восприниматься как совершенно разные значения в зависимости от настроек системы!)
Xml недействителен: Text=»26603333345″Id=»24493329746300″ отсутствует пробел перед Id

Ваш собственный подход опасен:

Я знаю, как изменить формат даты mm-dd-yy на yyyy-mm-dd: select left(convert(varchar, cast(’08-02-17′ as datetime), 120),10)

Первый шаг выполняется cast(’08-02-17′ as datetime). Это будет неявно полагаться на ваши системные настройки

Лучше попробуйте select left(convert(varchar, CONVERT(DATETIME,’08-02-17′,1), 120),10)

В принятом ответе используется if isdate(@X) = 1 что является опасным по той же причине.

Но то, что вы пытаетесь достичь, хорошо: yyyy-m-dd – это ISO8601, который должен быть форматом даты в XML:

Вы можете использовать XQuery с FLWOR для этого:

DECLARE @dummy TABLE(ID INT IDENTITY,YourXML NVARCHAR(MAX)); INSERT INTO @dummy VALUES (N'<Document xmlns:xsd=»http://www.w3.org/2001/XMLSchema» xmlns:xsi=»http://www.w3.org/2001/XMLSchema-instance»> <Page W=»2480″ H=»3516″> <Word L=»1871″ R=»2031″ T=»221″ B=»252″ Text=»INVOICE» Id=»25509747671106″ /> <Word L=»1988″ R=»2046″ T=»2232″ B=»2279″ Text=»tf.»l» Id=»25886807122412″ /> <Word L=»1872″ R=»1990″ T=»324″ B=»351″ Text=»26603333345″ Id=»24493329746300″ /> <Word L=»1871″ R=»2015″ T=»373″ B=»401″ Text=»08-02-17″ Id=»25109308586898″ /> <Word L=»1873″ R=»2007″ T=»422″ B=»448″ Text=»S-44404″ Id=»24914704754685″ /> <Word L=»1874″ R=»1887″ T=»468″ B=»496″ Text=»1″ Id=»22024234663427″ /> <Word L=»1068″ R=»1148″ T=»1278″ B=»1309″ Text=»DHL» Id=»8152496756181″ /> <Word L=»1692″ R=»1848″ T=»1279″ B=»1310″ Text=»08-02-17″ Id=»21119731019927″ /> <Word L=»2096″ R=»2251″ T=»1278″ B=»1310″ Text=»10-01-17″ Id=»31333127836454″ /> <Word L=»112″ R=»243″ T=»1352″ B=»1358″ Text=»_» Id=»365589546232″ /> <Word L=»252″ R=»411″ T=»1322″ B=»1350″ Text=»QUANTITY» Id=»1050334834310″ /> <Word L=»1415″ R=»1913″ T=»745″ B=»787″ Text=»______ShlpTo» Id=»22635743273663″ /> </Page> </Document>’); WITH CastedToXML AS ( SELECT ID ,CAST(YourXML AS XML) AS TheXml FROM @dummy ) SELECT ID ,TheXml.query (N’ <Document><Page> { for $w in /Document/Page/Word return if(string-length($w/@Text)=8 and substring($w/@Text,3,1)=»-» and substring($w/@Text,6,1)=»-«) then <Word L=»{$w/@L}» R=»{$w/@R}» T=»{$w/@T}» B=»{$w/@B}» Text=»{concat(«20″,substring($w/@Text,7,2),»-«,substring($w/@Text,1,5))}» Id=»{$w/@Id}» /> else $w } </Page></Document> ‘) FROM CastedToXML;

Результат:

Вы можете использовать тот же подход для немедленного обновления с обновляемым CTE:

WITH CastedToXML AS ( SELECT ID ,YourXML ,CAST(YourXML AS XML) AS TheXml FROM @dummy ) UPDATE CastedToXML SET YourXml=CAST(TheXml.query (N’ <Document><Page> { for $w in /Document/Page/Word return if(string-length($w/@Text)=8 and substring($w/@Text,3,1)=»-» and substring($w/@Text,6,1)=»-«) then <Word L=»{$w/@L}» R=»{$w/@R}» T=»{$w/@T}» B=»{$w/@B}» Text=»{concat(«20″,substring($w/@Text,7,2),»-«,substring($w/@Text,1,5))}» Id=»{$w/@Id}» /> else $w } </Page></Document> ‘) AS NVARCHAR(MAX)); SELECT * FROM @dummy;

Заключительный совет: храните это в колонке с типичным типом!