Any whitespace (blanks, tabs, line ends, comments /* */) around an "=" sign in a where clause are ignored by SQL. You can have one, ten, one hundred etc.,...

I was referring to setting breakpoints in SSIS, not in your script. You can set breakpoints on most tasks, both before and after they execute. Then, you run...

You need to nest your queries to get nested xml. e.g. this gets your details nested (but not the addresses):

select H.SAOID

,H.SAODate

,H.City "HeaderAddress/City" --Header Address New Tag

,H.State...

You need to specify the namespaces for the query e.g.

; with xmlnamespaces ('#RowsetSchema' as z)

select

Tbl.Col.value('@ItemCode', 'varchar(8)'),

Tbl.Col.value('@OptionPrice_Code', 'bigint'),

Tbl.Col.value('@OptionName_Code', 'bigint')

from

@x.nodes('//z:row') Tbl(Col)

produces:

0207740011006

0207740051009

0207750011006

0207750051009

02078100549

IIRC the SSIS variables list shows only the default values. WHen you examine the vars at a breakpoint, you see the current values

This worked for me:

declare @xml as xml = '

<Category>

<Employee EmployeeID="01">

<Name>

<Title>Mr</Title>

...

You can check the number of rows returned:

=iif(countrows()=0,"no data", "some data")

but that is for the whole query. Is that what you want?

If the SP can be modified to a TVF, then you can use the result in your query.

Why not use this splitter?

http://www.sqlservercentral.com/articles/Tally+Table/72993/

if the numeric portion is fixed length (always four characters), use the Left function e.g.

Left("1234-abc", 4)

yields "1234"

If the numeric portion is variable length, add a call to InStr:

=Left("1234-abc",InStr("1234-abc","-")-1)

check out Windowing functions. Something like:

select count(*) over (partition by city) citycount

, count(*) over (partition by state) statecount

...

Here's an approach using Chris Morris Pattern Splitter:

select Delivery_Site, firstword.Item

from p

cross apply (

select top 1 * from dbo.PatternSplitCM(Delivery_Site, '%[a-z0-9]%')

where Matched = 1

order by ItemNumber

) firstword

[/url]

Wow! That's confusing. Can you post some sample data and expected results?

I don't know how your tables are set up, but if they are integers, SQL is correct. That is:

using integer division

However, if they...

So, we have conflicting opinions! I suspect that the old Technet article is incorrect, or at least no longer correct. It only makes sense that SQL would build...