Red Gate forums :: View topic - No Intellisense when an XML variable exists
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

No Intellisense when an XML variable exists

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
melance



Joined: 12 Feb 2014
Posts: 3
Location: United States

PostPosted: Wed Feb 12, 2014 3:10 pm    Post subject: No Intellisense when an XML variable exists Reply with quote

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

Code:
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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 244
Location: Red Gate Software

PostPosted: Thu Feb 13, 2014 10:39 am    Post subject: Reply with quote

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    [Id],
      [SubValues]
FROM    @table
WHERE

I get a popup with the SubValues and Id columns listed, is this not the case for you?
Back to top
View user's profile Send private message
melance



Joined: 12 Feb 2014
Posts: 3
Location: United States

PostPosted: Fri Mar 07, 2014 7:16 pm    Post subject: Reply with quote

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:
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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 244
Location: Red Gate Software

PostPosted: Tue Mar 11, 2014 9:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
melance



Joined: 12 Feb 2014
Posts: 3
Location: United States

PostPosted: Tue Mar 11, 2014 12:38 pm    Post subject: Reply with quote

Aaron,
Thank you, that was the solution.

Lance
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group