Extract multiple date strings from a single varchar(MAX) column SQL Server

Question

I've inherited a report spec subsystem that needs to be tweaked. The mission is to add a date column to the tReports table, and populate it with the CreateDate that is (supposed to be) contained in the XML Code Spec. The problem is that some of the older reports don't have the CREATEDATE attribute, or as in one example below, the XML is valid but poorly formed, and the CREATEDATE cannot be retrieved using the xQuery that works for most of the other reports. Since I don't have an explicit creation date included in the spec, I'm using interpolation to estimate a reasonable date. One factor of the interpolation is to look at the date strings contained in the report spec--some will be useful, others not.

There are too many reports (over 1,200) to visually skim each report spec for date strings. These date strings can appear in any location in the report spec, and there are a very large number of combinations of elements and attributes that can contain a date string.

The ideal solution would be a listing of the reportID's and the date string ready to use in an UPDATE, but because date format varies (m/d/yy, mm/dd/yy, m/dd/yy ...) I'd be grateful to get a vew spurious characters surrounding the date string that I could clean up later.

All the date strings will be from 2000 or later, so the search string I'v been using is '/20' which has provided good results.

I've looked at many sites that discuss this kind of issue, and found only one solution by Mikael Eriksson that is something like what I'm describing, but I can't make it work after several hours of playing with it. How to extract multiple strings from single rows in SQL Server

Is there a way, without using a cursor nor WHILE loop, to extract these embedded dates?

-- Some representative data:  (I'm using SQL Server 2008 R2)
CREATE TABLE #ReportSpecs (ReportID INT, ReportSpec VARCHAR(MAX))
INSERT INTO #ReportSpecs
         ( ReportID, ReportSpec )
VALUES
(136,           
'<ReportID>136</ReportID>
<EmpIDCreator>23816</EmpIDCreator>
<EmpName>Blanc, Melvin J</EmpName>
<ReportType>0</ReportType>
<ReportName>PSST Sys Spec</ReportName>
<ReportData>
   <REPORT>
      <COLUMNS>
         <Column Name="JobNumber" Position="1" />
         <Column Name="TaskType" Position="2" />
         <Column Name="Assignees" Position="3" />
         <Column NAME="JobDueDate" Position="4" />
         <Column Name="ReferenceNumber" Position="5" />
         <Column Name="Deliverable" Position="6" />
         <Column Name="Priority" Position="7" />
      </COLUMNS>
      <FILTERS>
         <FILTER NAME="TYPE" VALUE="To_Me" />
         <FILTER NAME="Status" VALUE="All" />
         <FILTER NAME="DateOptions" VALUE="DateRange" From="8/16/2002" To="8/23/2002" />
         <FILTER NAME="FromDate" VALUE="8/16/2002" />
         <FILTER NAME="ToDate" VALUE="8/23/2002" />
         <FILTER NAME="Role" VALUE="All" />
      </FILTERS>
      <parameters>
         <PARAMETER  NAME="@Cascading" TYPE="integer" VALUE="0" />
         <PARAMETER  NAME="@EmpID" SYSTEM="true" TYPE="integer" VALUE="#Request.EmployeeIDAlias#" />
         <PARAMETER  NAME="@FromOrgs" TYPE="varchar(250)" VALUE="" />
         <PARAMETER  NAME="@ToOrgs" TYPE="varchar(250)" VALUE="" />
      </parameters>
      <NAME>PSST Sys Spec</NAME>
      <OWNER>
         <ID>23816</ID>
      </OWNER>
      <source id="8" useinternalid="True" />
   </REPORT>
</ReportData>'),

(311, 
'<ReportID>311</ReportID>
<EmpIDCreator>7162</EmpIDCreator>
<EmpName>Potter, Harry J</EmpName>
<ReportType>0</ReportType>
<ReportName>CPVC Synch Test</ReportName>
<ReportData>
   <REPORT>
      <COLUMNS>
         <Column Name="JobNumber" Position="1" />
         <Column Name="TaskType" Position="2" />
         <Column Name="Subject" Position="3" />
         <Column Name="CurrentAssignee" Position="4" />
         <Column NAME="JobDueDate" Position="5" />
         <Column Name="Deliverable" Position="6" />
         <Column Name="Category" Position="7" />
         <Column Name="Priority" Position="8" />
      </COLUMNS>
      <FILTERS>
         <FILTER  NAME="TYPE" VALUE="By_Orgs_6098,By_Orgs_6123" />
         <FILTER NAME="Status" VALUE="Open" />
         <FILTER NAME="DateOptions" VALUE="DateRange" From="3/25/2002" To="4/4/2002" />
         <FILTER NAME="ReviewFromDate" VALUE="3/25/2002" />
         <FILTER NAME="ReviewToDate" VALUE="4/4/2002" />
         <FILTER NAME="Role" VALUE="All" />
      </FILTERS>
      <parameters>
         <PARAMETER  NAME="@Act" TYPE="integer" VALUE="0" />
         <PARAMETER  NAME="@MgrID" SYSTEM="true" TYPE="integer" VALUE="#Request.EmployeeIDAlias#" />
         <PARAMETER  NAME="@MgrIDActing" TYPE="integer" VALUE="" />
         <PARAMETER  NAME="@FromDept" TYPE="varchar(250)" VALUE="" />
         <PARAMETER  NAME="@FromEmp" TYPE="varchar(250)" VALUE="" />
         <PARAMETER  NAME="@ToDept" TYPE="varchar(250)" VALUE="" />
      </parameters>
      <NAME>CPVC Synch Test</NAME>
      <OWNER>
         <ID>7162</ID>
      </OWNER>
      <source id="17" useinternalid="True" />
   </REPORT>
</ReportData>'),

(1131, 
'<ReportID>1131</ReportID>
<EmpIDCreator>13185</EmpIDCreator>
<EmpName>Reed, Alan</EmpName>
<ReportType>0</ReportType>
<ReportName>
   ''"><script>alert(''hello'')</script>
</ReportName>
<ReportData>
   <Report NAME="''">
      <script>alert(''hello'')</script>" CREATEDATE="12/7/2009">
      <DESCRIPTION>sfasf</DESCRIPTION>
      <OWNER ID="13185"/>
      <SOURCE ID="1" USEINTERNALID="TRUE"/>
      <COLUMNS>
         <COLUMN NAME="JobNumber" POSITION="1" SORTORDER="asc"/>
      </COLUMNS>
      <FILTERS>
         <FILTER NAME="TYPE" VALUE="By_Me,To_Me" />
         <FILTER NAME="ASGSTATUS" VALUE="Open" />
         <FILTER NAME="DATEOPTIONS" VALUE="All" />
         <FILTER NAME="STATUS" VALUE="Open" />
         <FILTER NAME="ASGDATEOPTIONS" VALUE="All" />
         <FILTER NAME="ROLE" VALUE="All" />
      </FILTERS>
      <PARAMETERS>
         <PARAMETER  NAME="@Me" TYPE="integer" VALUE="3" />
         <PARAMETER  NAME="@FromCost" TYPE="varchar(250)" VALUE=""/>
         <PARAMETER  NAME="@ToCost" TYPE="varchar(250)" VALUE="" />
      </PARAMETERS>
      <ADVANCEDSORT SortByA="JobNumber" SortOrderA="asc" SortByB="" SortOrderB="" SortByC="" SortOrderC="" />
   </Report>
</ReportData>');

/*
Desired Output  (A DISTINCT list would be better, but just getting this output would be GREAT.)
ReportID  DateString
--------  ----------
     136  8/16/2002
     136  8/23/2002
     136  8/16/2002
     136  8/23/2002
     311  3/25/2002
     311  4/4/2002
     311  3/25/2002
     311  4/4/2002
    1131  12/7/2009
*/

DROP TABLE #ReportSpecs

Thanks for your time.

Mikael Eriksson · Accepted Answer · 2014-01-16T07:17:26.140

1

select R.ReportID,
       D.V as DateString
from #ReportSpecs as R
  cross apply (select cast(R.ReportSpec as xml)) as X(R)
  cross apply X.R.nodes('//@*, //*/text()') as T(X)
  cross apply (select T.X.value('.', 'varchar(max)')) as D(V)
where charindex('/20', D.V) > 0

Result:

ReportID    DateString
----------- --------------------------
136         8/16/2002
136         8/23/2002
136         8/16/2002
136         8/23/2002
311         3/25/2002
311         4/4/2002
311         3/25/2002
311         4/4/2002
1131        " CREATEDATE="12/7/2009">

edited Jan 16 '14 at 07:17

answered Jan 15 '14 at 14:32

Mikael Eriksson

136,425
22
210
281

This is exceptional work, Mikael, Thanks. Your first version, with some minor mods, knocked the list down from over 1200 to 468 reports. 166 reports with "tricky" dates, as in Report 1131 shown above, were identified with this edited version. The remaining 302 will have to be approached on a text-search basis, because the XML is invalid (usually due to embedded SQL) and fails the "CAST as XML". Again, THANKS. – Jim the Frayed Jan 16 '14 at 19:08

Extract multiple date strings from a single varchar(MAX) column SQL Server

1 Answers1