asked
stored procedure xml output
Hello folks: I created a stored procedure that is supposed to output XML of the select statement. For some reason it only outputs up to LAST_NAME. Does anyone know why or how I can change this to include Restrictions 1-5? Thank you. USE [coll18_test02] GO /****** Object: StoredProcedure [dbo].[spCredentials] Script Date: 06/05/2013 10:08:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spCredentials] @Social varchar(12) = null, @StudentID varchar(10) = null, @Data XML OUTPUT AS BEGIN DECLARE @xml XML SET @xml= (SELECT ID, SSN, BIRTH_DATE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, a.[Restriction 1], a.[Restriction 2],a.[Restriction 3], a.[Restriction 4],a.[Restriction 5] FROM PERSON left join (Select b.STR_STUDENT, Max(Case When b.rn = 1 Then b.STR_RESTRICTION End) As [Restriction 1], Max(Case When b.rn = 2 Then b.STR_RESTRICTION End) As [Restriction 2], Max(Case When b.rn = 3 Then b.STR_RESTRICTION End) As [Restriction 3], Max(Case When b.rn = 4 Then b.STR_RESTRICTION End) As [Restriction 4], Max(Case When b.rn = 5 Then b.STR_RESTRICTION End) As [Restriction 5] From (Select STUDENT_RESTRICTIONS.STR_STUDENT, Row_Number() Over (Partition By STUDENT_RESTRICTIONS.STR_STUDENT Order By STUDENT_RESTRICTIONS.STR_RESTRICTION) As rn, STUDENT_RESTRICTIONS.STR_RESTRICTION From STUDENT_RESTRICTIONS Where (STUDENT_RESTRICTIONS.STR_RESTRICTION = 'AR' Or STUDENT_RESTRICTIONS.STR_RESTRICTION = 'AD') And (STUDENT_RESTRICTIONS.STR_END_DATE Is Null Or STUDENT_RESTRICTIONS.STR_END_DATE >= GetDate())) As b Group By b.STR_STUDENT) as a on
PERSON.ID = a.STR_STUDENT WHERE (((@Social IS NULL) AND (ID = @StudentID)) OR ((@StudentID IS NULL) AND (SSN = @Social)) OR ((ID = @StudentID) AND (SSN = @Social))) for XML raw) SET @Data = @xml END
As a test, try outputting the columns in a different order (e.g. putting Restriction 3 before Birth_Date). It shouldn't matter to XML, and that will tell you if it is a text length restriction. If you get the different columns but still get truncation of later columns, I'd look at how you are examining the results (how you use the output parameter when calling the SP) to see if you are either selecting to screen (there's an option in SSMS to change how many characters it displays, if it doesn't offer you the ability to click on the XML text (and if this isn't the cause - can you let us have the calling code?). If not ,then try running the select without the XML conversion (just to check there's nothing up with your data selection). Let us know how you get on (and any associated results / errors) and we can try to suggest further troubleshooting steps.
14 People are following this question.
