SQLTeam.com | Weblogs | Forums

& showing up as & in a SQL view


#1

I have the following code to derive a column in a view:

SELECT UPPER(PED_AGENCY.AGENCY_NAME) + ',' AS [text()]
    FROM PED_AGENCY INNER JOIN PED_AGENTS_SEGMENTS_AGENCY_RELATIONSHIP AGREL ON PED_AGENCY.AGENCY_ID=AGREL.AGENCYID
    WHERE (AGREL.AGENTID=GS.AGENTID) AND (AGREL.STARTDATE >= GS.STARTDATE) AND (AGREL.ENDDATE IS NULL OR AGREL.ENDDATE<= GS.ENDDATE) FOR XML PATH('')
    )
    AS 'AGENCY',

The result is what I need (splitting out multiple records into one delimited set of values in one column), however, in any case where the original value(s) had an ampersand, such as the value: D&S Preston Partnership, the following occurs: In cases where there actually WERE multiple values (such as D&S Preston Partnership and something else), it gets messed up and shows up as D&amp;S PRESTON PARTNERSHIP but in cases where that was the only value, it shows up OK.

Something to do with my casting as text and/or my concatenation? Any help to correct this much appreciated.


#2

You need to de-tokenize the XML. One way is to use the value function as shown below.

SELECT((
SELECT UPPER(PED_AGENCY.AGENCY_NAME) + ',' AS [text()]
    FROM PED_AGENCY INNER JOIN PED_AGENTS_SEGMENTS_AGENCY_RELATIONSHIP AGREL ON PED_AGENCY.AGENCY_ID=AGREL.AGENCYID
    WHERE (AGREL.AGENTID=GS.AGENTID) AND (AGREL.STARTDATE >= GS.STARTDATE) AND (AGREL.ENDDATE IS NULL OR AGREL.ENDDATE<= GS.ENDDATE) FOR XML PATH(''),TYPE
    ).value('.[1]','nvarchar(max)'))
    AS 'AGENCY'

#3

Hi James,
Thank you - I get this error:

Msg 258, Level 15, State 1, Line 23
Cannot call methods on nvarchar(max).


#4

Is what you posted the whole query, or is there more to it? The error message sounds like you are processing the output of this query and treating it as XML. If that is the case, then you shouldn't have to worry about the tokenizing, and there should be no need to detokenize.


#5

Sorry, here is the whole query:

SELECT
    GS.AGENTID,
    '2015' AS 'PROGRAM_YEAR',
    UPPER(GS.FIRSTNAME) AS 'FIRSTNAME',
    UPPER(GS.LASTNAME) AS 'LASTNAME',
    UPPER(GS.EMAIL) AS 'EMAIL-GENERAL',
    (SELECT UPPER(EMAIL_COMMISSION) FROM PED_AGENTS_EMAIL_COMMISSION C WHERE C.AGENTID=GS.AGENTID) AS 'EMAIL-COMMISSION',
    UPPER(GS.ADDRESS1) + ' ' + UPPER(GS.ADDRESS2) + ' ' + UPPER(GS.CITY) + ' ' + UPPER(GS.[STATE]) + ' ' + GS.ZIP  AS 'ADDRESS',
    GS.PRIMARYPHONE AS 'PHONE1',
    GS.SECONDARYPHONE AS 'PHONE2',
    GS.LICENSEAZ,
    GS.LICENSEMI,
    GS.LICENSETX,
    GS.NPN,
    GS.MS4NUMBER,
    GS.STARTDATE,
    GS.ENDDATE,
    (
    (
    SELECT UPPER(PED_FMO.FMONAME) + ',' AS [text()]
    FROM PED_FMO INNER JOIN PED_AGENTS_SEGMENTS_FMO_RELATIONSHIP FMOREL ON PED_FMO.FMOID=FMOREL.FMOID
    WHERE (FMOREL.AGENTID=GS.AGENTID) AND (FMOREL.STARTDATE >= GS.STARTDATE) AND (FMOREL.ENDDATE IS NULL OR FMOREL.ENDDATE<= GS.ENDDATE) FOR XML PATH('')
    ).value('.[1]','nvarchar(max)'))
    AS 'FMO',
    (
    (
    SELECT UPPER(PED_AGENCY.AGENCY_NAME) + ',' AS [text()]
    FROM PED_AGENCY INNER JOIN PED_AGENTS_SEGMENTS_AGENCY_RELATIONSHIP AGREL ON PED_AGENCY.AGENCY_ID=AGREL.AGENCYID
    WHERE (AGREL.AGENTID=GS.AGENTID) AND (AGREL.STARTDATE >= GS.STARTDATE) AND (AGREL.ENDDATE IS NULL OR AGREL.ENDDATE<= GS.ENDDATE) FOR XML PATH(''),TYPE
    ).value('.[1]','nvarchar(max)'))
    AS 'AGENCY',
    (
    (SELECT REL.AGENCYID + ',' AS [text()]
    FROM PED_AGENCY AGENCY INNER JOIN PED_AGENTS_SEGMENTS_AGENCY_RELATIONSHIP REL ON AGENCY.AGENCY_ID=REL.AGENCYID
    WHERE (REL.AGENTID=GS.AGENTID) AND (REL.STARTDATE>=GS.STARTDATE) AND (REL.ENDDATE IS NULL OR REL.ENDDATE<=GS.ENDDATE) FOR XML PATH('')
    ).value('.[1]','nvarchar(max)'))
        AS 'AGENCY_ID'

FROM PED_AGENTS_SEGMENTS_GOODSTANDING GS