You can take this further and split the rows into columns like this

DECLARE @XMLData xml;

SET @XMLData = '<PlaceDescriptions>
<descriptions>0|Home</descriptions>
<descriptions>1|Office|False</descriptions>
<descriptions>2|Play Ground</descriptions>
<descriptions>3|School|True</descriptions>
<descriptions>4|Movie Hall</descriptions>
</PlaceDescriptions>';

WITH cteXML AS (
  SELECT  descriptions...

Firstly, you don't need to use sp_xml_preparedocument and OPENXML.

Secondly, your xml is not properly formed - There is an extra ">" before "</PlaceDescriptions>"

Below is code that will extract the descriptions...

• This reply was modified 4 years, 11 months ago by DesNorton.

I love the use of math to solve this.  However, I am stumped as to why/how the math works.  An explanation for Dummies would be greatly appreciated.

y /7...

I love the use of math to solve this.  However, I am stumped as to why/how the math works.  An explanation for Dummies would be greatly appreciated.

y /7 -- Full...

It appears that you may have a mismatch between your @ColumnName and your @sql

• @ColumnName returns [April-19], [August-19], etc
• @sql returns MonthYearOf as [April 19], [August 19], etc

            
October 20, 2020 at 5:02 am
            
#3799096

            
            
                                                
        

You will  need to test, but using PATINDEX *might* give you slightly better performance.

SELECT T1.DocumentNo, T2.LongSentence
FROM Table2 T2
INNER JOIN Table1 T1 ON PATINDEX('%' + T1.DocumentNo + '%',...

Since your query is returning no results, it cannot display a yes/no answer.

What you need to do is change it so that it always returns a result

SELECT...

Looks like you are missing a closing quote in the WHEN part of your case statement.

Here's my version, also no recursion, with added alias names for more clarity:

SELECT 
    start_date, end_date,
    full_weeks * 2 +
...

You can use a splitter function, and a cross tab to get what you are looking for

DECLARE @TestData table (JoinedData varchar(50));

INSERT INTO @TestData ( JoinedData )
VALUES (...

In my previous post I stated that you would find more data anomalies - Wonder how I knew that.

This is not something that can be fixed with a single query. ...

Based on the latest sample data that you provided, this code works.

However, I fully expect that you will get other non-compliant records.

You can keep tweaking the code to the point...

Your downfall here is going to be trying to do it all at once.

You cannot touch the vendor DB, but there is nothing stopping you from creating your own staging...

That speaks to a record whose data does not match the pattern of data that was used for creating the solution at hand.

You will need to find the record(s) that...

That code will not work for a declared table variable.

It is for a table parameter as requested in the original post.

Also note that the code is looking for the declared...