如何更改xml中所有日期的格式?

2021-09-10 00:00:00 xml regex sql tsql sql-server

我在 SQL 的一个单元格中有一个 xml，例如:

I have an xml in a cell in SQL, like:

表格:

    <?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 中.

I need to update [FRData] and change the date from format mm-dd-yy to format yyyy-mm-dd in all xml.

我写了一个正则表达式来验证所寻求的日期格式:

I wrote a regular expression that validates the date format sought:

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

我知道如何将日期格式 mm-dd-yy 更改为 yyyy-mm-dd:

I know how to change the date format mm-dd-yy to yyyy-mm-dd:

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

但是我不知道如何更改整个xml中的日期

But I do not know how to change dates in the entire 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

相关文章