Red Gate forums :: View topic - SQL Refactor ignores the XML WITH when laying out fields
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Refactor 1
SQL Refactor 1 forum

SQL Refactor ignores the XML WITH when laying out fields

Search in SQL Refactor 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jonathan.baggaley



Joined: 20 Apr 2010
Posts: 5
Location: Berkshire

PostPosted: Fri May 21, 2010 10:41 am    Post subject: SQL Refactor ignores the XML WITH when laying out fields Reply with quote

I have the following stored procedure (ignore the lack of normalization - the data is imported from a spreadsheet and gets broken down later! Rolling Eyes ) 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:
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



Rolling Eyes
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Tue May 25, 2010 4:54 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
jonathan.baggaley



Joined: 20 Apr 2010
Posts: 5
Location: Berkshire

PostPosted: Tue May 25, 2010 5:11 pm    Post subject: wrapping... Reply with quote

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
_________________
:-)zz[
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group