none
Visual Studio 2010 and Query Designer SQL Problem with XML Path

    問題

  • Hello,

    I have below sql which works fine in SSMS but when i run this in query designer of BIDS I get result like

    Code  EmployeeNames
    ----  -------------------------
    1234  <Expr1>First Last</Expr1>

     

    Code I use..

    SELECT DISTINCT C.Name, T.firstName, T.lastName, C.gradeLevel, C.SubjectArea,
    STUFF((SELECT ',' + CAST(StudentID AS VARCHAR(MAX))
    FROM CourseProfile
    WHERE CourseID = C.CourseID
    FOR XML PATH('')),1,1,'') AS StudentID, CP.SchoolProfileID
    FROM Course C
    INNER JOIN CourseProfile CP ON CP.CourseID = C.CourseID INNER JOIN Teacher T ON CP.teacherId = T.teacherId
    order by name

    But in BIDS it add "AS Expr1" in the sql(below), is there any workaround to remove <Expr1></Expr1> appear in result??

      SELECT DISTINCT C.name, T.firstName, T.lastName, C.gradeLevel, C.subjectArea, STUFF
                                 ((SELECT        ',' + CAST(studentId AS VARCHAR(MAX)) AS Expr1
                                     FROM            CourseProfile
                                     WHERE        (courseId = C.courseId) FOR XML PATH('')), 1, 1, '') AS StudentID, CP.schoolProfileId
    FROM            Course AS C INNER JOIN
                             CourseProfile AS CP ON CP.courseId = C.courseId INNER JOIN
                             Teacher AS T ON CP.teacherId = T.teacherId
    ORDER BY C.name


    JL
    2011年12月9日 下午 03:08

所有回覆

  • Hi jazzz308,

    Do you mean Visual Studio automatically adds “Expr1” as the column alias?

    It appears to me that there is no such an option to disable it in Visual Studio. You can remove the added alias (AS Expr1) manually.

     

    Jian Kang
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • 已編輯 KJian_ 2011年12月12日 上午 02:36
    2011年12月12日 上午 02:36
  • Not sure what you mean by remove manually?  If i remove it from the sql it re-appears when I run the query.  So is this mean that I cant use this sql in SRSS environment? 

    Been googling for past couple of days with no luck, anyone out there had similar issues?


    JL

    Jian, What I meant was it adds Expr1 into my results.  So basically if I run this in SS management Studio I will get something like 1,2,3,4,5 but if I run this in Query Designer in VS it returns as <Expr1>1,</Expr1><Expr1>2,</Expr1> etc....

    • 已編輯 jazzz308 2011年12月12日 下午 04:26
    2011年12月12日 下午 01:58
  • Try using AS "*" or AS "data()" instead of AS Expr1, eg

    SELECT DISTINCT C.name, T.firstName, T.lastName, C.gradeLevel, C.subjectArea, STUFF
    ((SELECT ',' + CAST(studentId AS VARCHAR(MAX)) AS "*"
    FROM CourseProfile
    WHERE (courseId = C.courseId) FOR XML PATH('')), 1, 1, '') AS StudentID, CP.schoolProfileId
    FROM Course AS C INNER JOIN
    CourseProfile AS CP ON CP.courseId = C.courseId INNER JOIN
    Teacher AS T ON CP.teacherId = T.teacherId
    ORDER BY C.name
    
    
    SELECT DISTINCT C.name, T.firstName, T.lastName, C.gradeLevel, C.subjectArea, STUFF
    ((SELECT ',' + CAST(studentId AS VARCHAR(MAX)) AS "data()"
    FROM CourseProfile
    WHERE (courseId = C.courseId) FOR XML PATH('')), 1, 1, '') AS StudentID, CP.schoolProfileId
    FROM Course AS C INNER JOIN
    CourseProfile AS CP ON CP.courseId = C.courseId INNER JOIN
    Teacher AS T ON CP.teacherId = T.teacherId
    ORDER BY C.name
    

    • 已提議為解答 HunchbackMVP 2011年12月13日 下午 03:18
    2011年12月13日 下午 02:32
    解答者
  • Great suggestion wBob.

    Still, VS should not add an alias, since it changes the behavior of the XQuery. Using the wildcard character "*", behaves exactly as not using a column name.

    Columns with a Name Specified as a Wildcard Character
    http://msdn.microsoft.com/en-us/library/bb500154.aspx

     


    AMB

    Some guidelines for posting questions...

    2011年12月13日 下午 03:35
  • Thanks HB, you are right about the tool.  I seem to remember something similar with DataDude where behaviour between SSMS and Visual Studio differed.  Worth raising a connect?

    connect.microsoft.com

    2011年12月13日 下午 09:00
    解答者