TSQL - Insert record in a table and increment one column. Data comes from XML.

Today one more task on SQL –

While inserting a record, incremental value of a column, FeeScheduleChargeID to be updated tacitly based on the XML data.
1.       XML data comes from XML datatype
2.       Table has Primary Key, PK which is auto identity column.

Sample

CREATE TABLE [dbo].[SomeTable](

      [PK] [int] IDENTITY(1,1) NOT NULL,
      [FeeScheduleID] [int] NOT NULL,
      [ThisColumnToBeUpdatedID] [int] NULL,
      [ChargeID] [int] NULL,
      [FeeAmount] [money] NULL,
      [sysDateCreated] [datetime] NOT NULL,
      [CreatedByName] [varchar](50) NOT NULL,
      [sysDateLastUpdated] [datetime] NOT NULL,
      [LastUpdateByName] [varchar](50) NULL,
      [ChargeClassification] [int] NULL,
      [FeeType] [bit] NOT NULL,
      [MinimumHours] [real] NULL,
      [Taxable] [bit] NOT NULL,
      [ApplyCharge] [tinyint] NOT NULL,
      [AcctNum] [int] NOT NULL)

-- Insert one record in the above mentioned table.

DECLARE @Xml xml;

SET @Xml ='   
 <Root> 
 <SomeTable> 
 <ChargeID>133</ChargeID> 
 <FeeAmount>123</FeeAmount> 
 <ChargeClassification>82</ChargeClassification> 
 <FeeType>1</FeeType> 
 <MinimumHours>27</MinimumHours> 
 <Taxable>0</Taxable> 
 <ApplyCharge>27</ApplyCharge> 
 <AcctNum>100</AcctNum> 
 </SomeTable> 

<SomeTable> 
 <ChargeID>133</ChargeID> 
<FeeAmount>656</FeeAmount> 
<ChargeClassification>82</ChargeClassification> 
<FeeType>1</FeeType> 
<MinimumHours>27</MinimumHours> 
<Taxable>1</Taxable> 
<ApplyCharge>27</ApplyCharge> 
<AcctNum>100</AcctNum> 
</SomeTable> 
</Root>'

INSERT INTO SomeTable

(ThisColumnToBeUpdatedID , FeeScheduleID ,ChargeID ,

FeeAmount ,sysDateCreated ,CreatedByName , sysDateLastUpdated ,
LastUpdateByName ,ChargeClassification ,FeeType ,MinimumHours ,
Taxable , ApplyCharge ,AcctNum)

SELECT(

        SELECT MAX( ThisColumnToBeUpdatedID) FROM SomeTable
      ) + ROW_NUMBER()OVER( ORDER BY GSQry.ThisColumnToBeUpdatedID ASC ) ThisColumnToBeUpdatedID ,
      GSQry.FeeScheduleID ,
      GSqry.ChargeID ,
      GSqry.FeeAmount ,
      GSqry.sysDateCreated ,
      GSqry.CreatedByName ,
      GSqry.sysDateLastUpdated ,
      GSqry.LastUpdateByName ,
      GSqry.ChargeClassification ,
      GSqry.FeeType ,
      GSqry.MinimumHours ,
      GSqry.Taxable ,
      GSqry.ApplyCharge ,
      GSqry.AcctNum
  FROM(
        SELECT N.a.value( 'ThisColumnToBeUpdatedID[1]' , 'INT')ThisColumnToBeUpdatedID ,
               342 AS FeeScheduleID ,
               N.a.value( 'ChargeID[1]' , 'INT')ChargeID ,
               N.a.value( 'FeeAmount[1]' , 'MONEY')FeeAmount ,
               GETDATE()sysDateCreated ,
               'CreatedByName' CreatedByName ,
               GETDATE()sysDateLastUpdated ,
               'UpdatedByName' LastUpdateByName ,
               N.a.value( 'ChargeClassification[1]' , 'INT') ChargeClassification ,
               N.a.value( 'FeeType[1]' , 'BIT')FeeType ,
               N.a.value( 'MinimumHours[1]' , 'REAL')MinimumHours ,
               N.a.value( 'Taxable[1]' , 'BIT')Taxable ,
               N.a.value( 'ApplyCharge[1]' , 'TINYINT')ApplyCharge ,
               N.a.value( 'AcctNum[1]' , 'INT')AcctNum
          FROM @Xml.nodes( '/Root/SomeTable')AS N( a)
      )GSQry; 

-- Thank You.