Here is the example:
There are 2 tables and an XML variable, this is just one example. When you join to the XML variable before the 2nd table there are no more hints shown for any code there after. Swap the joins around and it works for the join to the second table but still no hints for code after the join XML variable.
CREATE TABLE #temp (id INT IDENTITY(1,1), someText NVARCHAR(50))
INSERT INTO #temp
( someText )
VALUES (
'Text-1'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-2'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-3'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-4'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-5'
)
CREATE TABLE #join (id INT, someDate DATETIME)
INSERT INTO #join
( id, someDate )
VALUES ( 1, -- id - int
'2009-07-28 12:53:14' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 2, -- id - int
'2008-01-24 00:00:14' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 3, -- id - int
'2009-07-25 15:53:19' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 4, -- id - int
'2009-01-01 00:00:01' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 5, -- id - int
'2009-07-04 23:53:14' -- someDate - datetime
)
DECLARE @xml XML
SET @xml = '<ROOT><V a="1" b="boo1"/><V a="2" b="boo2"/><V a="3" b="boo3"/><V a="4" b="boo4"/><V a="5" b="boo5"/></ROOT>'
SELECT t.*,R.v.value('@a','INT'), R.v.value('@b','nvarchar(50)')
FROM #temp t
JOIN @XML.nodes('/ROOT/V') AS R ( v ) ON T.id = R.v.value('@a','INT')
There are 2 tables and an XML variable, this is just one example. When you join to the XML variable before the 2nd table there are no more hints shown for any code there after. Swap the joins around and it works for the join to the second table but still no hints for code after the join XML variable.
CREATE TABLE #temp (id INT IDENTITY(1,1), someText NVARCHAR(50))
INSERT INTO #temp
( someText )
VALUES (
'Text-1'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-2'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-3'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-4'
)
INSERT INTO #temp
( someText )
VALUES (
'Text-5'
)
CREATE TABLE #join (id INT, someDate DATETIME)
INSERT INTO #join
( id, someDate )
VALUES ( 1, -- id - int
'2009-07-28 12:53:14' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 2, -- id - int
'2008-01-24 00:00:14' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 3, -- id - int
'2009-07-25 15:53:19' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 4, -- id - int
'2009-01-01 00:00:01' -- someDate - datetime
)
INSERT INTO #join
( id, someDate )
VALUES ( 5, -- id - int
'2009-07-04 23:53:14' -- someDate - datetime
)
DECLARE @xml XML
SET @xml = '<ROOT><V a="1" b="boo1"/><V a="2" b="boo2"/><V a="3" b="boo3"/><V a="4" b="boo4"/><V a="5" b="boo5"/></ROOT>'
SELECT t.*,R.v.value('@a','INT'), R.v.value('@b','nvarchar(50)')
FROM #temp t
JOIN @XML.nodes('/ROOT/V') AS R ( v ) ON T.id = R.v.value('@a','INT')