For XML Path - errors first time - then works as an Append

Rx_

Nothing In Moderation
Local time
Today, 03:31
Joined
Oct 22, 2009
Messages
2,803
The first time this code is run, there is an error message (See attachment).
After clicking OK, it just runs fine.
Is this error message normal?

There exist a table that takes a PK in one column, then Concats the (STIP abbreviation, start Day/MO, End Day/MO)

Sometimes, the PK had several STIP records.
The code below combines all of the STIPs in the same PK and Concats them with a comma to seperate them. (See Attachment)
This view is being consumed by another view.

The view that consumes this doesn't bring up this error message.

Code:
SELECT     STIP2.[ID_Wells], substring   
                          ((SELECT     ', ' +  STIP1.[STIPwDate]  AS [text()] 
                              FROM         [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP1
                              WHERE     STIP1.[ID_Wells] = STIP2.[ID_Wells]
                              ORDER BY STIP1.[ID_Wells] FOR XML PATH('')), 2, 1000) [STIPwDate]
FROM         [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP2

Some of the STIPS (e.g. PM) Don't have a date, so we see PM()
Once in a while, there is this extra close parens. No rhyme or reason.
 

Attachments

  • Stips Error message from XML Parsing.jpg
    Stips Error message from XML Parsing.jpg
    74.6 KB · Views: 272
Posted this over in SQL Server Central and got this response:
Your query looks fine to me. Perhaps it's the Query builder GUI...
Try copying and pasting that query into an SSMS query window and executing it from there...

Sure enough, it works perfect with no error in a New - Query with just the code.
It is just the graphical parser that has this error. Explains whey the error only appears once after any change in code - but why the query runs fine.
 

Users who are viewing this thread

Back
Top Bottom