Note that the "./" in your code is not required based on the XML Context. Also note that specifying that you need the elements' text, via text(), will improve performance.

For...

To better understand the problem and solution have a look at Well Formed XML:

This is my first time messing with JSON in SQL. The BOL entry on this helped me.

First, JSON is cases sensitive so, $.Data is not the same as $.data. Next,...

As Jeff mentioned, CONVERT + 101 gets you the formatting you need:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101);

For the last day of the previous month:

SELECT EOMONTH(DATEADD(MONTH,-1,GETDATE()));

For the...

You can create your own INSTR function leveraging ngrams8k.

CREATE OR ALTER FUNCTION dbo.Instr8K
(
  @string   VARCHAR(8000),
  @search   VARCHAR(100),
  @instance INT
)
/*
Created by...

For cleaner code you can leverage fnTally. Since fnTally leverages TOP, you can exploit row goals as Steve Collins mentioned above.

CREATE OR ALTER FUNCTION dbo.getYears(@year...

I don't have a server handy at the moment but what you are looking for is in the REPORTSERVER.DBO.CATALOG

This has the RDL XML (Report Definitions) which you can query. To...

@ Alan,

I'm honestly a little bit surprised that you're justifying a sort because it has a low row count.  We know how that usually works out.

I was a little rushed...

• This reply was modified 5 years, 7 months ago by Alan Burstein.

apart from the performance that is likely to be quite bad has errors on its construct

The Construct is a problem but I suspect the performance should be fine, STRING_SPLIT is...

there is currently no way to incrementally populate the table.

...  a [Huge] fact table that gets regenerated nightly

This is a design/process issue more than a technical one. I'll put my...

A couple things to consider. First, In SSMS 2019 the actual execution plan will show you how much time each portion of the query took. You don't need that here...

• This reply was modified 5 years, 9 months ago by Alan Burstein. Reason: Added another option - dbo.many

Grab a copy of PatReplace8K and it should be easy to strip out what you need.

SELECT 
  pa.*, 
  Clean1 = CAST(ISNULL(pr.newString,0) AS FLOAT),
...

@sgmunson

What you posted is similar to my first version of this except that I use my ngrams function to generate the tally table. Here's a simplified version of...

I missed this.

The difference is that I did the split using an indexed view. This way the split only happens once,...

Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like...