如何将不同的字符串值转换为各自的“YYYY”int格式？

Question

我从table中将值插入xml中，如下所示：

DECLARE @XMLRESULT XML

DECLARE @tbl_XMLResult TABLE
(
    RowID INT IDENTITY(1,1),
    Status VARCHAR(50),
    Address VARCHAR(250),
    ListPrice INT,
    SoldPrice INT,
    YearBuilt INT   
)

EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult
(
    Status,
    Address,
    ListPrice,
    SoldPrice,
    YearBuilt
)
SELECT
    L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
    L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
    L.value('(ListPrice/text())[1]','INT') AS ListPrice,
    L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
    L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)

但是，如上面所示，对于YearBuilt列，数据类型是int，我们在其中存储的只是构建属性的year。并相应地完成后续的calculations。但是，有时YearBuilt的值来自xml，如format中的

• “新”
• "0-5岁“
• "70+“

但是，当YearBuilt的上述结果出现在xml中时，我们就得到了conversion error。需要从上述结果中获得值，就好像YearBuilt是：

• “新”-在这里，我们要考虑本年度和存储结果为2016年本年度。
• "0-5岁“-0-5岁请考虑上限，即5. 2011年的存储结果为(2016-5=2011年)。
• "70+“-考虑70名=> 2016 - 70岁=1946年

如何将适当的结果存储到@tbl_xmlResult中？

Answer 1

将XML中的值作为字符串获取，并在case语句中执行一些字符串操作。

使用简化XML的示例：

declare @X xml = '
<YearBuilt>2017</YearBuilt>
<YearBuilt>New</YearBuilt>
<YearBuilt>0-5 Yrs</YearBuilt>
<YearBuilt>70+</YearBuilt>
';

select case
         when T.Value = 'New' then year(getdate())
         when T.Value like '%Yrs' then year(getdate()) - cast(replace(stuff(T.Value, 1, charindex('-', T.Value), ''), 'Yrs', '') as int)
         when T.Value like '%+' then cast(left(T.Value, charindex('+', T.Value) - 1) as int)
         else cast(T.Value as int)
       end
from @X.nodes('/YearBuilt') as Y(X)
  cross apply (
              select Y.X.value('text()[1]', 'varchar(10)')
              ) as T(Value);