Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

No Intellisense when an XML variable exists

melancemelance Posts: 4
edited March 11, 2014 7:38AM in SQL Prompt
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

Comments

  • Aaron LAaron L Posts: 596 New member
    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?
  • 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 LAaron L Posts: 596 New member
    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,
    Thank you, that was the solution.

    Lance
Sign In or Register to comment.