Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Visual Studio 2010 and Query Designer SQL Problem with XML Path

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

  • Friday, December 09, 2011 3:08 PM
     
      Has Code
    Sign In to Vote
    0

    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
     

All Replies

  • Monday, December 12, 2011 2:36 AM
     
     
    Sign In to Vote
    0

    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.
    • Edited by KJian_ Monday, December 12, 2011 2:36 AM
    •  
     
  • Monday, December 12, 2011 1:58 PM
     
     
    Sign In to Vote
    0

    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....

    • Edited by jazzz308 Monday, December 12, 2011 4:26 PM
    •  
     
  • Tuesday, December 13, 2011 2:32 PM
    Answerer
     
     Proposed Has Code
    Sign In to Vote
    0

    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

    • Proposed As Answer by HunchbackMVP Tuesday, December 13, 2011 3:18 PM
    •  
     
  • Tuesday, December 13, 2011 3:35 PM
     
     
    Sign In to Vote
    0

    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...

    • Edited by HunchbackMVP Tuesday, December 13, 2011 3:35 PM
    •  
     
  • Tuesday, December 13, 2011 9:00 PM
    Answerer
     
     
    Sign In to Vote
    0

    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