SQL Refactor ignores the XML WITH when laying out fields

jonathan.baggaley

I have the following stored procedure (ignore the lack of normalization - the data is imported from a spreadsheet and gets broken down later! :roll: ) which, when I lay out should break all the XML WITH columns to shorter lines or at least break them at the 120 character point since wrapping is on too. Is this a bug/design "feature" or have I missed something in the SQL Refactor options?

sql refactor v1.3.0.207 wrapping: 120chars subquery placement max length 80 column placements - not ticked schema statements.definition - all ticked parenthasis placement - all new line

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:		
-- Create date: 19 may 10
-- Description:	Batch save imported quit attempt smoking lines
-- =============================================
ALTER PROCEDURE [Smoking].[usp_BatchSaveSmokingLines]
    (
      @BatchMasterId INT,
      @SmokingLinesDoc TEXT
    )
AS 
    BEGIN
        DECLARE @iDoc INT,
            @MaxArchivedByVersion INT,
            @ModifiedDate DATETIME

        CREATE TABLE #tmpUpdateValues
            (
              QuitAttemptId INT,
              BatchMasterId INT,
              ClientId INT,
              VenueId INT,
              AdviserId INT,
              PeriodId INT,
              Surname VARCHAR(50),
              FirstName VARCHAR(50),
              Title VARCHAR(20),
              Address VARCHAR(500),
              PostCode VARCHAR(10),
              NHSNumber VARCHAR(50),
              DaytimeTel VARCHAR(30),
              MobileTel VARCHAR(30),
              AltContactNumber VARCHAR(30),
              DateOfBirth DATETIME,
              AgeYears INT,
              GenderCode INT,
              PresciptionChargeExempt BIT,
              Pregnant BIT,
              BreastFeeding BIT,
              AnyKnownAllergies VARCHAR(50),
              HistoryOfCardioVasc BIT,
              CardioVascDetails VARCHAR(50),
              OccupationId INT,
              EthnicCode VARCHAR(3),
              TimeToFirstSmokeId INT,
              AvgNumberSmokedPerDay VARCHAR(50),
              HeardAboutId INT,
              HeardAboutOther VARCHAR(50),
              AgreedQuitDate DATETIME,
              DateOfLastTobaccoUse DATETIME,
              DateOf4WeekFollowUp DATETIME,
              InterventionTypeId INT,
              InterventionTypeOther VARCHAR(50),
              PharmSupportId1 INT,
              PharmSupportDesc1 VARCHAR(10),
              PharmSupportId2 INT,
              PharmSupportDesc2 VARCHAR(10),
              PharmSupportId3 INT,
              PharmSupportDesc3 VARCHAR(10),
              PharmSupportId4 INT,
              PharmSupportDesc4 VARCHAR(10),
              TreatmentOutcomeId INT,
              NameOfGP VARCHAR(50),
              PracticeCode VARCHAR(10),
              SubmittedDate DATETIME,
              CreatedBy UNIQUEIDENTIFIER,
              CreatedDate DATETIME,
              LastModifiedDate DATETIME,
              LastModifiedBy UNIQUEIDENTIFIER,
              StatusId INT,
              SubmittedInPeriodId INT,
              Active BIT,
              DepartmentWard VARCHAR(30),
              LocationSetting VARCHAR(30),
              ContactTelNo VARCHAR(30),
              AdviserCodeRef VARCHAR(30)
            )

        CREATE TABLE #tempDiary
            (
              ParentWorkingId INT,
              DiaryContactId INT,
              QuitAttemptId INT,
              ContactDate DATETIME,
              COReading VARCHAR(50),
              TypeOfContact VARCHAR(50),
              NRTVoucherNo VARCHAR(15),
              Comments VARCHAR(100)
            )

        SET @ModifiedDate = GETDATE()

	--Create an internal representation of the XML document.  If doc not valid will fail gracefully
        EXEC sp_xml_preparedocument @idoc OUTPUT, @SmokingLinesDoc
--
        INSERT  INTO #tmpUpdateValues
                ( QuitAttemptId, BatchMasterId, ClientId, VenueId, AdviserId, PeriodId, Surname, FirstName, Title,
                  Address, PostCode, NHSNumber, DaytimeTel, MobileTel, AltContactNumber, DateOfBirth, AgeYears,
                  GenderCode, PresciptionChargeExempt, Pregnant, BreastFeeding, AnyKnownAllergies, HistoryOfCardioVasc,
                  CardioVascDetails, OccupationId, EthnicCode, TimeToFirstSmokeId, AvgNumberSmokedPerDay, HeardAboutId,
                  HeardAboutOther, AgreedQuitDate, DateOfLastTobaccoUse, DateOf4WeekFollowUp, InterventionTypeId,
                  InterventionTypeOther, PharmSupportId1, PharmSupportDesc1, PharmSupportId2, PharmSupportDesc2,
                  PharmSupportId3, PharmSupportDesc3, PharmSupportId4, PharmSupportDesc4, TreatmentOutcomeId, NameOfGP,
                  PracticeCode, SubmittedDate, CreatedBy, CreatedDate, LastModifiedDate, LastModifiedBy, StatusId,
                  SubmittedInPeriodId, Active, DepartmentWard, LocationSetting, ContactTelNo, AdviserCodeRef )
                SELECT  *
                FROM    OPENXML (@idoc, '/SmokingBatch/DetailItems/SmokingRow',1) WITH ( QuitAttemptId INT 'QuitAttemptId', BatchMasterId INT 'BatchMasterId', ClientId INT 'ClientId', VenueId INT 'VenueId', AdviserId INT 'AdviserId', PeriodId INT 'PeriodId', Surname VARCHAR(50) 'Surname', FirstName VARCHAR(50) 'FirstName', Title VARCHAR(20) 'Title', Address VARCHAR(500) 'Address', PostCode VARCHAR(10) 'PostCode', NHSNumber VARCHAR(50) 'NHSNumber', DaytimeTel VARCHAR(30) 'DaytimeTel', MobileTel VARCHAR(30) 'MobileTel', AltContactNumber VARCHAR(30) 'AltContactNumber', DateOfBirth DATETIME 'DateOfBirth', AgeYears INT 'AgeYears', GenderCode INT 'GenderCode', PresciptionChargeExempt BIT 'PresciptionChargeExempt', Pregnant BIT 'Pregnant', BreastFeeding BIT 'BreastFeeding', AnyKnownAllergies VARCHAR(50) 'AnyKnownAllergies', HistoryOfCardioVasc BIT 'HistoryOfCardioVasc', CardioVascDetails VARCHAR(50) 'CardioVascDetails', OccupationId INT 'OccupationId', EthnicCode VARCHAR(3) 'EthnicCode', TimeToFirstSmokeId INT 'TimeToFirstSmokeId', AvgNumberSmokedPerDay VARCHAR(50) 'AvgNumberSmokedPerDay', HeardAboutId INT 'HeardAboutId', HeardAboutOther VARCHAR(50) 'HeardAboutOther', AgreedQuitDate DATETIME 'AgreedQuitDate', DateOfLastTobaccoUse DATETIME 'DateOfLastTobaccoUse', DateOf4WeekFollowUp DATETIME 'DateOf4WeekFollowUp', InterventionTypeId INT 'InterventionTypeId', InterventionTypeOther VARCHAR(50) 'InterventionTypeOther', PharmSupportId1 INT 'PharmSupportId1', PharmSupportDesc1 VARCHAR(10) 'PharmSupportDesc1', PharmSupportId2 INT 'PharmSupportId2', PharmSupportDesc2 VARCHAR(10) 'PharmSupportDesc2', PharmSupportId3 INT 'PharmSupportId3', PharmSupportDesc3 VARCHAR(10) 'PharmSupportDesc3', PharmSupportId4 INT 'PharmSupportId4', PharmSupportDesc4 VARCHAR(10) 'PharmSupportDesc4', TreatmentOutcomeId INT 'TreatmentOutcomeId', NameOfGP VARCHAR(50) 'NameOfGP', PracticeCode VARCHAR(10) 'PracticeCode', SubmittedDate DATETIME 'SubmittedDate', CreatedBy UNIQUEIDENTIFIER 'CreatedBy', CreatedDate DATETIME 'CreatedDate', LastModifiedDate DATETIME 'LastModifiedDate', LastModifiedBy UNIQUEIDENTIFIER 'LastModifiedBy', StatusId INT 'StatusId', SubmittedInPeriodId INT 'SubmittedInPeriodId', Active BIT 'Active', DepartmentWard VARCHAR(30) 'DepartmentWard', LocationSetting VARCHAR(30) 'LocationSetting', ContactTelNo VARCHAR(30) 'ContactTelNo', AdviserCodeRef VARCHAR(30) 'AdviserCodeRef' )		


        SELECT  *
        FROM    #tmpUpdateValues

        SELECT  *
        FROM    OPENXML (@idoc, '/SmokingBatch/DetailItems/SmokingRow/DiaryEntriesRows/SmokingDiaryEntry',1) WITH ( ParentWorkingId INT 'ParentWorkingId', DiaryContactId INT 'DiaryContactId', QuitAttemptId INT 'QuitAttemptId', ContactDate DATETIME 'ContactDate', COReading VARCHAR(50) 'COReading', TypeOfContact VARCHAR(50) 'TypeOfContact', NRTVoucherNo VARCHAR(15) 'NRTVoucherNo', Comments VARCHAR(100) 'Comments', CreatedBy UNIQUEIDENTIFIER 'CreatedBy', CreatedDate DATETIME 'CreatedDate', LastModifiedDate DATETIME 'LastModifiedDate', LastModifiedBy UNIQUEIDENTIFIER 'LastModifiedBy' )
		





--DetailId INT 'CategoryDetailId', PlannedActivity money 'PlannedActivity', ActualActivityQ1 money 'ActualActivityQ1', ActualActivityQ2 money 'ActualActivityQ2', ActualActivityQ3 money 'ActualActivityQ3', ActualActivityQ4 money 'ActualActivityQ4', CalculatedTotal money 'CalculatedResult', ActualEarningsTotal money 'ActualEarningsTotal' 
--)



    END

:roll:

Brian Donahue

Hi Jonathan,

We're aware that OPENXML...WITH does not wrap and have opened a development issue for this with an ID of SR-568. Part of the problem is that we haven't decided on a style for the wrapping, so if you have any suggestions, this would be a good time!

jonathan.baggaley

Multiple options
> Each field set on a different line (commas before or after)
> Line length - any field sets crossing the line (e.g. 120 chars) get wrapped
> Leave unchanged

Options how far to indent below the OPENXML

Hope this helps!

thanks

Jon