主页 / dba / 问题 / 131827
Accepted
Kaishu
Asked: 2016-03-11 05:12:20 +0800 CST

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

  • 772

我将值插入tablefromxml作为：

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操作。但有些时候 for 的值YearBuilt来自xml于format

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

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

  • “新”- 在这里，我们要将当前年份和存储结果视为 2016 当年。
  • “0-5 岁”- 0-5 岁。这里考虑上限，即 5。因此将结果存储为 2011 as(2016 - 5 = 2011)
  • “70+” - 将 70 视为 => 2016 - 70 年 = 1946

我们如何才能将适当的结果存储到@tbl_xmlResult

sql-server-2005 xml

1 个回答

  1. Best Answer

    从 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);
    • 3

相关问题