How can we help you today? How can we help you today?

Hints don't show up for code after a join to a XML node.

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')
dzwahlen
0

Comments

1 comment

  • tanya
    Hello,

    Unfortunately SQL Prompt v4 does not yet support CLR types (including xml) and so it fails to prompt suggestions.
    However when you start writing the next WHERE clause or ORDER BY clauses in your SELECT query, SQL Prompt will continue to provide suggestions.
    We have this issue logged in our database and we will review it for our future releases.
    Apologies for any inconvenience caused:(

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
    tanya
    0

Add comment

Please sign in to leave a comment.