No Intellisense when an XML variable exists

melance

When an xml variable exists and has xml assigned to it, intellisense no longer works. For example:

DECLARE @xmlData XML = '
<Root>
	<Value>
		<Id>1</Id>
		<SubValue>
			<Value>A</Value>
		</SubValue>
		<SubValue>
			<Value>B</Value>
		</SubValue>
	</Value>
	<Value>
		<Id>2</Id>
		<SubValue>
			<Value>C</Value>
		</SubValue>
		<SubValue>
			<Value>D</Value>
		</SubValue>
	</Value>
</Root>'

DECLARE @table TABLE ([Id] INT,[SubValues] XML)

INSERT	@table
SELECT 	r.v.value('Id[1]','INT'),
		r.v.query('SubValue')
FROM 	@xmlData.nodes('//Root/Value') AS r(v)

SELECT 	[Id],
		[SubValues]
FROM 	@table

Aaron L

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?

melance

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

Aaron L

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.

melance

Aaron,
Thank you, that was the solution.

Lance