SQL Refactor ignores the XML WITH when laying out fields

Refactors and formats SQL code.

SQL Refactor ignores the XML WITH when laying out fields

Postby jonathan.baggaley » Fri May 21, 2010 10:41 am

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

Code: Select all

-- =============================================
-- Author:      
-- Create date: 19 may 10
-- Description:   Batch save imported quit attempt smoking lines
-- =============================================
ALTER PROCEDURE [Smoking].[usp_BatchSaveSmokingLines]
      @BatchMasterId INT,
      @SmokingLinesDoc TEXT
        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'


Posts: 5
Joined: Tue Apr 20, 2010 4:56 pm
Location: Berkshire

Postby Brian Donahue » Tue May 25, 2010 4:54 pm

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!
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 10:48 am


Postby jonathan.baggaley » Tue May 25, 2010 5:11 pm

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!


Posts: 5
Joined: Tue Apr 20, 2010 4:56 pm
Location: Berkshire

Return to SQL Refactor Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests