No Intellisense when an XML variable exists

Provides intelligent code completion for SQL Server editors.

Moderators: Anu Deshpande, Aaron Law, David Priddle

No Intellisense when an XML variable exists

Postby melance » Wed Feb 12, 2014 3:10 pm

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

Code: Select all
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
melance
 
Posts: 3
Joined: Wed Feb 12, 2014 3:06 pm
Location: United States

Postby Aaron Law » Thu Feb 13, 2014 10:39 am

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:
Code: Select all
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 Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby melance » Fri Mar 07, 2014 7:16 pm

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:

Code: Select all
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
 
Posts: 3
Joined: Wed Feb 12, 2014 3:06 pm
Location: United States

Postby Aaron Law » Tue Mar 11, 2014 9:30 am

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 Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby melance » Tue Mar 11, 2014 12:38 pm

Aaron,
Thank you, that was the solution.

Lance
melance
 
Posts: 3
Joined: Wed Feb 12, 2014 3:06 pm
Location: United States


Return to SQL Prompt 6

Who is online

Users browsing this forum: No registered users and 2 guests