No Intellisense when an XML variable exists

• 
  Hi Melance,
  I'm having difficulty recreating this on the current 6.3 beta.
  
  eg. If I continue with your select statement and type where:

  SELECT    [Id], 
        [SubValues] 
  FROM    @table
  WHERE
  

  I get a popup with the SubValues and Id columns listed, is this not the case for you?

  Aaron L February 13, 2014 04:39

  - 0 +
• 
  Hi Aaron,
  I just retried the example I gave and it appears to work correctly. The code I am actually working with is a lot more complicated and doesn't work:
  

  USE [CORA_AppDev]
  GO
  /****** Object:  StoredProcedure [ReportSystem].[spUpsertReportXML]    Script Date: 3/7/2014 12:13:09 PM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  -- ============================================================================================================
  --
  -- Description: Inserts and updates report configuration data
  --
  -- Date       	Author				Notes
  -- ------------	-------------------	--------------------------------------------
  -- 2009-05-04	Lance Boudreaux		Created
  --
  -- ============================================================================================================
  ALTER PROCEDURE [ReportSystem].[spUpsertReportXML] 
  	-- Add the parameters for the stored procedure here
  	@xmlData XML
  AS
  BEGIN
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  	SET NOCOUNT ON;
  
  	DECLARE @debug BIT = 0
  
  	-- Debug Section
  	SET @debug = 1
  	DECLARE @xmlData XML = '<?xml version="1.0"?>
  <Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <PkReportId>330</PkReportId>
    <Name>Lance Test</Name>
    <FkReportCategoryId>19</FkReportCategoryId>
    <Description />
    <StoredProcedure>spLanceTest</StoredProcedure>
    <ArchiveCopies>0</ArchiveCopies>
    <Template>1</Template>
    <BatchOnly>false</BatchOnly>
    <Schedule>0</Schedule>
    <NoView>0</NoView>
    <DefaultSchedule>false</DefaultSchedule>
    <DefaultNoView>false</DefaultNoView>
    <Layout>//48AD8AeABtAGwAIAB2AGUAcgBzAGkAbwBuAD0AIgAxAC4AMAAiACAAZQBuAGMAbwBkAGkAbgBnAD0AIgB1AHQAZgAtADEANgAiAD8APgA8AEEAYwB0AGkAdgBlAFIAZQBwAG8AcgB0AHMATABhAHkAbwB1AHQAIABWAGUAcgBzAGkAbwBuAD0AIgAzAC4AMQAiACAAUAByAGkAbgB0AFcAaQBkAHQAaAA9ACIAOQAzADYAMAAiACAARABvAGMAdQBtAGUAbgB0AE4AYQBtAGUAPQAiAEEAUgBOAGUAdAAgAEQAbwBjAHUAbQBlAG4AdAAiACAAUwBjAHIAaQBwAHQATABhAG4AZwA9ACIAVgBCAC4ATgBFAFQAIgAgAE0AYQBzAHQAZQByAFIAZQBwAG8AcgB0AD0AIgAwACIAPgA8AFMAdAB5AGwAZQBTAGgAZQBlAHQAPgA8AFMAdAB5AGwAZQAgAE4AYQBtAGUAPQAiAE4AbwByAG0AYQBsACIAIABWAGEAbAB1AGUAPQAiAGYAbwBuAHQALQBmAGEAbQBpAGwAeQA6ACAAQQByAGkAYQBsADsAIABmAG8AbgB0AC0AcwB0AHkAbABlADoAIABuAG8AcgBtAGEAbAA7ACAAdABlAHgAdAAtAGQAZQBjAG8AcgBhAHQAaQBvAG4AOgAgAG4AbwBuAGUAOwAgAGYAbwBuAHQALQB3AGUAaQBnAGgAdAA6ACAAbgBvAHIAbQBhAGwAOwAgAGYAbwBuAHQALQBzAGkAegBlADoAIAAxADAAcAB0ADsAIABjAG8AbABvAHIAOgAgAEIAbABhAGMAawA7ACAAIgAgAC8APgA8AFMAdAB5AGwAZQAgAE4AYQBtAGUAPQAiAEgAZQBhAGQAaQBuAGcAMQAiACAAVgBhAGwAdQBlAD0AIgBmAG8AbgB0AC0AcwBpAHoAZQA6ACAAMQA2AHAAdAA7ACAAZgBvAG4AdAAtAHcAZQBpAGcAaAB0ADoAIABiAG8AbABkADsAIAAiACAALwA+ADwAUwB0AHkAbABlACAATgBhAG0AZQA9ACIASABlAGEAZABpAG4AZwAyACIAIABWAGEAbAB1AGUAPQAiAGYAbwBuAHQALQBmAGEAbQBpAGwAeQA6ACAAVABpAG0AZQBzACAATgBlAHcAIABSAG8AbQBhAG4AOwAgAGYAbwBuAHQALQBzAGkAegBlADoAIAAxADQAcAB0ADsAIABmAG8AbgB0AC0AdwBlAGkAZwBoAHQAOgAgAGIAbwBsAGQAOwAgAGYAbwBuAHQALQBzAHQAeQBsAGUAOgAgAGkAdABhAGwAaQBjADsAIAAiACAALwA+ADwAUwB0AHkAbABlACAATgBhAG0AZQA9ACIASABlAGEAZABpAG4AZwAzACIAIABWAGEAbAB1AGUAPQAiAGYAbwBuAHQALQBzAGkAegBlADoAIAAxADMAcAB0ADsAIABmAG8AbgB0AC0AdwBlAGkAZwBoAHQAOgAgAGIAbwBsAGQAOwAgACIAIAAvAD4APAAvAFMAdAB5AGwAZQBTAGgAZQBlAHQAPgA8AFMAZQBjAHQAaQBvAG4AcwA+ADwAUwBlAGMAdABpAG8AbgAgAFQAeQBwAGUAPQAiAEcAcgBvAHUAcABIAGUAYQBkAGUAcgAiACAATgBhAG0AZQA9ACIARwByAG8AdQBwAEgAZQBhAGQAZQByADEAIgAgAEgAZQBpAGcAaAB0AD0AIgA0ADkANQAiACAAQgBhAGMAawBDAG8AbABvAHIAPQAiADEANgA3ADcANwAyADEANQAiACAALwA+ADwAUwBlAGMAdABpAG8AbgAgAFQAeQBwAGUAPQAiAEQAZQB0AGEAaQBsACIAIABOAGEAbQBlAD0AIgBEAGUAdABhAGkAbAAxACIAIABIAGUAaQBnAGgAdAA9ACIAMgA3ADAAIgAgAEIAYQBjAGsAQwBvAGwAbwByAD0AIgAxADYANwA3ADcAMgAxADUAIgA+ADwAQwBvAG4AdAByAG8AbAAgAFQAeQBwAGUAPQAiAEEAUgAuAEYAaQBlAGwAZAAiACAATgBhAG0AZQA9ACIAVABlAHgAdABCAG8AeAAxACIAIABEAGEAdABhAEYAaQBlAGwAZAA9ACIARgBpAHIAcwB0AE4AYQBtAGUAIgAgAEwAZQBmAHQAPQAiADAAIgAgAFQAbwBwAD0AIgAwACIAIABXAGkAZAB0AGgAPQAiADEANAA0ADAAIgAgAEgAZQBpAGcAaAB0AD0AIgAyADgANQAiACAAVABlAHgAdAA9ACIAVABlAHgAdABCAG8AeAAxACIAIAAvAD4APABDAG8AbgB0AHIAbwBsACAAVAB5AHAAZQA9ACIAQQBSAC4ARgBpAGUAbABkACIAIABOAGEAbQBlAD0AIgBUAGUAeAB0AEIAbwB4ADIAIgAgAEQAYQB0AGEARgBpAGUAbABkAD0AIgBMAGEAcwB0AE4AYQBtAGUAIgAgAEwAZQBmAHQAPQAiADEANAA0ADAAIgAgAFQAbwBwAD0AIgAwACIAIABXAGkAZAB0AGgAPQAiADcAOQAyADAAIgAgAEgAZQBpAGcAaAB0AD0AIgAyADcAMAAiACAAVABlAHgAdAA9ACIAVABlAHgAdABCAG8AeAAxACIAIABTAHQAeQBsAGUAPQAiAGYAbwBuAHQALQB3AGUAaQBnAGgAdAA6ACAAYgBvAGwAZAA7ACAAIgAgAC8APgA8AC8AUwBlAGMAdABpAG8AbgA+ADwAUwBlAGMAdABpAG8AbgAgAFQAeQBwAGUAPQAiAEcAcgBvAHUAcABGAG8AbwB0AGUAcgAiACAATgBhAG0AZQA9ACIARwByAG8AdQBwAEYAbwBvAHQAZQByADEAIgAgAEgAZQBpAGcAaAB0AD0AIgAzADYAMAAiACAAQgBhAGMAawBDAG8AbABvAHIAPQAiADEANgA3ADcANwAyADEANQAiACAALwA+ADwALwBTAGUAYwB0AGkAbwBuAHMAPgA8AFIAZQBwAG8AcgB0AEMAbwBtAHAAbwBuAGUAbgB0AFQAcgBhAHkAIAAvAD4APABQAGEAZwBlAFMAZQB0AHQAaQBuAGcAcwAgAC8APgA8AFAAYQByAGEAbQBlAHQAZQByAHMAIAAvAD4APAAvAEEAYwB0AGkAdgBlAFIAZQBwAG8AcgB0AHMATABhAHkAbwB1AHQAPgA=</Layout>
    <CheckedOut>true</CheckedOut>
    <User>GCR1\lboudreaux</User>
    <DateStamp>2014-02-11T08:57:20</DateStamp>
    <Active>true</Active>
    <ArchiveFilter />
    <ShowParishList>false</ShowParishList>
    <Timeout>0</Timeout>
    <AllowCSVExport>false</AllowCSVExport>
    <QueryOnly>false</QueryOnly>
    <HasHistory>true</HasHistory>
    <ReportParameter>
      <PkReportParameterId>1826</PkReportParameterId>
      <FkReportId>330</FkReportId>
      <Name>userId</Name>
      <Order>0</Order>
      <Type>System.Int32</Type>
      <Value />
      <DataSource />
      <DataSourceType />
      <DisplayMember />
      <ValueMember />
      <Visible>true</Visible>
      <Active>true</Active>
      <DisplayName />
      <SQLType>int</SQLType>
    </ReportParameter>
    <ReportParameter>
      <PkReportParameterId>-1</PkReportParameterId>
      <FkReportId>0</FkReportId>
      <Name>test</Name>
      <Order>0</Order>
      <Type>System.String</Type>
      <Visible>true</Visible>
      <Active>true</Active>
      <SQLType>varchar</SQLType>
    </ReportParameter>
  </Report>'
  	-- End Debug Section
  
  	DECLARE @reportId INT
  	DECLARE @recentInsert TABLE
  			([Id] INT,
  			[Match1] VARCHAR(MAX),
  			[Match2] VARCHAR(MAX))
  	DECLARE @insert VARCHAR(10) = 'INSERT'
  	DECLARE @update VARCHAR(10) = 'UPDATE'
  
  	SELECT 	@reportId = Report.Data.value('PkReportId[1]','INT')
  	FROM 	@xmlData.nodes('//Report') AS Report(Data)
  
  END
  

  
  Thank you,
  Lance

  melance March 07, 2014 13:16

  - 0 +
• 
  Hi Lance,
  Thank you for the script, I can recreate your issue here. It looks like the problem is that Prompt isn't "looking back" far enough to cover the xml string, a work around for now is to change the ParserLookBackDistance to a larger value as described here.
  
  The restriction is there for performance reasons but we are currently investigating a better solution, I'll keep you updated if we get a new build out with this look back removed.
  
  Thanks,
  Aaron.

  Aaron L March 11, 2014 04:30

  - 0 +
• 
  Aaron,
  Thank you, that was the solution.
  
  Lance

  melance March 11, 2014 07:38

  - 0 +

Add comment

Please sign in to leave a comment.