How to convert different string value to respective 'YYYY' int format? I'm inserting values into table from xml as:

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)


But as shown above for YearBuilt column the datatype is int in which we are storing just year on which property is built. And accordingly subsequent calculations are done. But some of the time the values for YearBuilt comes from xml as in format


"New"     
"0-5 Yrs" 
"70+"     


But when above type of result for YearBuilt comes in xml we are getting conversion error.
Need to get values from above result as if YearBuilt as:


"New"     - Here we want consider current year and store result as 2016 current year.
"0-5 Yrs" - 0-5Yrs. Here consider upper limit, i.e. 5. So store result as 2011 as(2016 - 5 = 2011)
"70+"     - consider 70 as => 2016 - 70 Year = 1946


How we can do this to store appropriate result into @tbl_xmlResult
Get the value from the XML as a string and do some string manipulation in a case statement.

Example with simplified XML:

declare @X xml = '
&lt;YearBuilt&gt;2017&lt;/YearBuilt&gt;
&lt;YearBuilt&gt;New&lt;/YearBuilt&gt;
&lt;YearBuilt&gt;0-5 Yrs&lt;/YearBuilt&gt;
&lt;YearBuilt&gt;70+&lt;/YearBuilt&gt;
';

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);