Thanks for the replies everyone! That XML-function is really really great.

Didn't know that XML was actually order-"specific", I remember ridiculing some systems myself that required to send XML tags in...

This is part of SQL Unit-tests, so need a TSQL solution :/

HASHBYTES will give different results for following two xmls:

<xml>

<attribute1>1/<attribute1>

<attribute2>2/<attribute2>

</xml>

<xml>

<attribute2>2/<attribute2>

...

That's because Short-Sleeved is > Short, so it doesn't satisfy the <= 'Short' condition

Your XML-SQL is too complex. It's better to do it by following "pseudo"-code:

SELECT 'field'

, another_field

-- ( starts a new XML level

, (

...

Aha, it does something like that:

[Expr1010] = Scalar Operator(CONVERT_IMPLICIT(int,#t.[parcelno_available] as [tpa].[parcelno_available]-[Expr1011],0))

Right before the insert into the #t-table.

and parcelno_available is well above INTs value.

This cast doesn't happens when i just select...

Hi,

Could you explain it a bit more? 🙂 Not sure i follow it exactly

I usually do something like this:

SELECTSetId, CHECKSUM_AGG(CHECKSUM(ItemCode)) ch

FROMSetsItems

GROUP BY SetId

ORDER BY ch

If you're **really** unlucky you might get a false positive because CHECKSUM isn't perfect, but i doubt it. You...

Hi,

Yes, i saw the solution. The reason i'm stubborn is that it works fine if i call different inline TVFs inside each other, as i demonstrated with my second script.

If...

Sean Lange (10/10/2014)

---

Consider closely what this is doing. The return from your function is NOT an nvarchar(max). It returns a table with a single column that you are then trying...

Nah, it works fine, if the function name in the "nest" is different:

IF OBJECT_ID('FN_REPLACE', N'IF') IS NOT NULL

DROP FUNCTION FN_REPLACE

GO

IF OBJECT_ID('FN_REPLACE2', N'IF') IS NOT NULL

DROP FUNCTION FN_REPLACE2

GO

CREATE FUNCTION dbo.FN_REPLACE

(

@pString NVARCHAR(MAX)

,@pReplaceSegment...

I don't actually reference the function inside itself.

Script #2 is what i try to run outside of the created function body.

The function definition is still the same as in Script...

It can make wonders in performance to change the Scalar function into Inline table-valued function (if the function is simple enough that is).

Oh ok, that clears things up. Thanks!

Why not have a UNION of two selects?

One that selects column names for the first row and another that selects actual data?

I've noticed when you have code like:

DECLARE @sql NVARCHAR(MAX)

SET @sql = '... very long sql' + CAST(@someparam AS VARCHAR) + 'continuation of long sql' + '...'

And...