Conversion from OPENXMLTo Nodes

Question

ALTER PROCEDURE [dbo].[USP_WG_CustomerSaveBulk]

@XMLCustomers VARCHAR(max),

@Mode NVARCHAR(50),

@ProfileID INT,

@MerchantID INT,

@LastIP NVARCHAR(40),

@Password NVARCHAR(100) = NULL,

@PasswordExpiryLimit int=0,

@IsAdminID Int=null

,@AdminID INT=null -- added by Govind

,@LogDescription NVARCHAR(max)='' -- added by Govind

AS

BEGIN

SET NOCOUNT OFF;

BEGIN TRY

BRANCH_ONE:

IF @Mode = 'insert'

BEGIN

Declare @CustTable Table(CustID Bigint,PasswordFormail varchar(100))

DECLARE @xmlDataHandle INT

DECLARE @CustomerNotSave TABLE

(

merchantid INT,

profileid INT,

loginid INT,

[password] NVARCHAR(100),

pin INT,

email NVARCHAR(100),

fname NVARCHAR(50),

lname NVARCHAR(50),

address NVARCHAR(100),

city NVARCHAR(50),

state NVARCHAR(2),

zip NVARCHAR(50),

shippingaddress NVARCHAR(100),

shippingcity NVARCHAR(100),

shippingstate NVARCHAR(100),

shippingzip NVARCHAR(100),

phone NVARCHAR(100),

cell NVARCHAR(100),

f1 NVARCHAR(100),

f2 NVARCHAR(100),

f3 NVARCHAR(100),

f4 NVARCHAR(100),

isactive BIT,

qblistid NVARCHAR(100),

lastlogin DATETIME,

lastip NVARCHAR(100),

insertedon DATETIME,

modifiedon DATETIME,

allowsms BIT,

[status] NVARCHAR(100),-- Added For Sending Status

PasswordFormail NVARCHAR(100),

ccname NVARCHAR(100),

card NVARCHAR(100),

expMonth NVARCHAR(100),

expYear NVARCHAR(100)

)

EXEC Sp_xml_preparedocument

@xmlDataHandle output,

@XMLCustomers

DECLARE @LoginID INT

DECLARE @Pin INT

DECLARE @Email NVARCHAR(200)

DECLARE @Fname NVARCHAR(100)

DECLARE @LName NVARCHAR(100)

DECLARE @Address NVARCHAR(200)

DECLARE @City NVARCHAR(100)

DECLARE @State NVARCHAR(4)

DECLARE @Zip NVARCHAR(100)

DECLARE @ShippingAddress NVARCHAR(200) = NULL

DECLARE @ShippingCity NVARCHAR(100) = NULL

DECLARE @ShippingState NVARCHAR(4) = NULL

DECLARE @ShippingZip NVARCHAR(100) = NULL

DECLARE @Phone NVARCHAR(50)

DECLARE @Cell NVARCHAR(50)

DECLARE @F1 NVARCHAR(max)

DECLARE @F2 NVARCHAR(max)

DECLARE @F3 NVARCHAR(max)

DECLARE @F4 NVARCHAR(max)

DECLARE @IsActive BIT

DECLARE @QbListID NVARCHAR(200)

DECLARE @LastLogin DATETIME

DECLARE @InsertedOn DATETIME

DECLARE @ModifiedOn DATETIME = NULL

DECLARE @AllowSMS BIT = NULL

DECLARE @xmlPassword NVARCHAR(100) = NULL

DECLARE @xmlPasswordForMail NVARCHAR(100) = NULL

DECLARE @xmlCCname NVARCHAR(100) = NULL

DECLARE @xmlCard NVARCHAR(100) = NULL

DECLARE @xmlMonth NVARCHAR(100) = NULL

DECLARE @xmlyear NVARCHAR(100) = NULL

DECLARE @XmlCCTypeID NVARCHAR(100) = NULL

DECLARE cur_insertbulkcustomer CURSOR FOR

SELECT (SELECT dbo.Getloginidforcustomer('loginid'))

,

(

SELECT dbo.Getloginidforcustomer('pin')),

Isnull(_xmlCustomer._email, ''),

_xmlCustomer._fname,

Isnull(_xmlCustomer._lname, ''),

_xmlCustomer._address,

_xmlCustomer._city,

_xmlCustomer._state,

_xmlCustomer._zip

,

_xmlCustomer._phone,

_xmlCustomer._cell,

_xmlCustomer._f1,

_xmlCustomer._f2,

_xmlCustomer._f3,

_xmlCustomer._f4,

_xmlCustomer._isactive,

_xmlCustomer._qblistid,

_xmlCustomer._lastlogin,

Getdate(),

_xmlCustomer._password,

_xmlCustomer._PasswordFormail,

_xmlCustomer._Ccname,

_xmlCustomer._card,

_xmlCustomer._expyear,

_xmlCustomer._expmonth,

_xmlCustomer._CCTypeID

FROM OPENXML(@xmlDataHandle, '/CustomerData/Customer', 2)

WITH (

_loginid INT '@LoginID'

,

_pin INT '@Pin',

_email NVARCHAR(100) '@Email',

_fname NVARCHAR(50) '@FName',

_lname NVARCHAR(50) '@LName',

_address NVARCHAR(100) '@Address',

_city NVARCHAR(50) '@City',

_state NVARCHAR(2) '@State',

_zip NVARCHAR(50) '@Zip'

,

_phone NVARCHAR(25) '@Phone',

_cell NVARCHAR(25) '@Cell',

_f1 NVARCHAR(max) '@F1',

_f2 NVARCHAR(max) '@F2',

_f3 NVARCHAR(max) '@F3',

_f4 NVARCHAR(max) '@F4',

_isactive BIT '@IsActive',

_qblistid NVARCHAR(100) '@QbListID',

_lastlogin DATETIME '@LastLogin',

_insertedon DATETIME '@InsertedOn',

_password NVARCHAR(100) '@xmlPassword' ,

_PasswordFormail nvarchar(100) '@PasswordFormail',

--_Ccname nvarchar(100) '@CCname ',

_Ccname nvarchar(100) '@xmlCCname',

_card nvarchar(100) '@xmlCard',

-- _expyear nvarchar(100) '@ExpMonth ',

_expyear nvarchar(100) '@xmlExpMonth',

-- _expmonth nvarchar(100) '@ExpYear ',

_expmonth nvarchar(100) '@xmlExpYear',

_CCTypeID nvarchar(100) '@CardTypeID'

)_xmlCustomer

OPEN cur_insertbulkcustomer

FETCH next FROM cur_insertbulkcustomer INTO @LoginID, @Pin, @Email, @Fname , @LName, @Address, @City, @State, @Zip, @Phone, @Cell, @F1, @F2, @F3,

@F4, @IsActive, @QbListID, @LastLogin, @InsertedOn,@xmlPassword,@xmlPasswordForMail,@XmlCCname,@xmlCard,@xmlMonth,@xmlyear,@XmlCCTypeID

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @CustID INT

SELECT @CustID = dbo.Udf_iscustomerexistbyfnamelname(@Fname, @LName, @MerchantID)

IF @CustID = 0

BEGIN

INSERT INTO customer

(merchantid,

profileid,

loginid,

[password],

pin,

email,

fname,

lname,

address,

city,

state,

zip,

shippingaddress,

shippingcity,

shippingstate,

shippingzip,

phone,

cell,

f1,

f2,

f3,

f4,

isactive,

qblistid,

lastlogin,

lastip,

insertedon,

modifiedon,

allowsms,

AdminID)

VALUES (@MerchantID,

@Profileid,

@LoginID,

@xmlPassword,

@Pin,

@Email,

@Fname,

@LName,

@Address,

@City,

@State,

@Zip,

@ShippingAddress,

@ShippingCity,

@ShippingState,

@ShippingZip,

@Phone,

@Cell,

@F1,

@F2,

@F3,

@F4,

'true',

@QbListID,

@LastLogin,

@LastIP,

Getdate(),

@ModifiedOn,

@AllowSMS,

@IsAdminID)

--Start code by ram on 28 Jan 2014 to add Password request in log

declare @CustomerID int

SET @CustomerID = SCOPE_IDENTITY()

if @xmlCard<> ''

begin

exec [USP_WG_CustomerCardForBulkUpload] @customerID=@CustomerID,@nameOnCard=@XmlCCname,@CardTypeID=@XmlCCTypeID,@card=@xmlCard,

@ExpMonth=@xmlMonth, @ExpYear=@xmlyear,@Address=@Address,@Zip=@Zip,@LastIP=@LastIP,@Mode='Insert',@Default=0,

@IsBilling=0

end

exec USP_WG_InsertInPwdLog @xmlPassword,@CustomerID,'Customer',@PasswordExpiryLimit

insert into @CustTable (CustID,PasswordFormail)values(@CustomerID,@xmlPasswordForMail)

--End code by ram on 28 Jan 2014 to add Password request in log

-- CRD 185 Admin Logs (Changes by Govind) Start

SET @LogDescription = @LogDescription +'Customer Name :'+@Fname+' '+ @LName

-- CRD 185 Admin Logs (Changes by Govind) END

END

ELSE

BEGIN

INSERT INTO @CustomerNotSave

(merchantid,

profileid,

loginid,

[password],

pin,

email,

fname,

lname,

address,

city,

state,

zip,

shippingaddress,

shippingcity,

shippingstate,

shippingzip,

phone,

cell,

f1,

f2,

f3,

f4,

isactive,

qblistid,

lastlogin,

lastip,

insertedon,

modifiedon,

allowsms,

[status],

ccname,

card ,

expMonth,

expYear

)

VALUES (@MerchantID,

@Profileid,

@LoginID,

@Password,

@Pin,

@Email,

@Fname,

@LName,

@Address,

@City,

@State,

@Zip,

@ShippingAddress,

@ShippingCity,

@ShippingState,

@ShippingZip,

@Phone,

@Cell,

@F1,

@F2,

@F3,

@F4,

@IsActive,

@QbListID,

@LastLogin,

@LastIP,

Getdate(),

@ModifiedOn,

@AllowSMS,

'Customer Already Exists.',

@xmlCCname,

@xmlCard ,

@xmlMonth ,

@xmlyear

)

END

--if @xmlCard<> ''

-- begin

-- exec USP_WG_CustomerCardForBulkUpload @customerID=@CustID,@nameOnCard=@XmlCCname,@CardTypeID=1,@card=@xmlCard,

-- @ExpMonth=@xmlMonth, @ExpYear=@xmlyear,@Address=@Address,@Zip=@Zip,@LastIP=@LastIP,@Mode='Insert',@Default=0,

-- @IsBilling=0

-- end

FETCH next FROM cur_insertbulkcustomer INTO @LoginID, @Pin,

@Email,

@Fname

,

@LName, @Address, @City, @State, @Zip, @Phone, @Cell, @F1, @F2

,

@F3,

@F4,

@IsActive, @QbListID, @LastLogin, @InsertedOn,@xmlPassword,@xmlPasswordForMail,@XmlCCname,@xmlCard,@xmlMonth,@xmlyear,@XmlCCTypeID

END

CLOSE cur_insertbulkcustomer

DEALLOCATE cur_insertbulkcustomer

-- CRD 185 Admin Logs (Changes by Govind) Start

IF(@AdminID <> 0)

BEGIN

SET @LogDescription = 'Performed Customer BULK Add.' + @LogDescription

EXEC USP_WG_RecordAdminActivity @CustomerID,5,@AdminID,@MerchantID,@ProfileID,'Customer','BulkInsert',@LogDescription,'Customer',NULL,NULL

END

-- CRD 185 Admin Logs (Changes by Govind) END

SELECT fname AS 'First Name',

lname AS 'Last Name',

address,

city,

state,

zip AS 'Zip Code',

phone AS 'Phone Home',

cell AS 'Phone Mobile',

email AS 'Email ID',

status,

password ,

'' as xmlPassword ,

'' as PasswordFormail ,

ccname as CCname,

expMonth as ExpMonth,

--'' as ExpMonth,

card as CardNum,

expYear as Expyear,

--'' as Expyear,

'' as CardType,

'' as CardTypeID,

'' Xmlcard,

--'' as Xmlcard,

'' as xmlCCname,

'' as xmlExpMonth,

'' as xmlExpYear,

'' as RSACard

FROM @CustomerNotSave

Select * from Customer C join @CustTable Ct on c.CustomerID =ct.CustID

return 1

END

 

 

This Stored Procedure has being written using OpenXml.Now, I have to Use Nodes() Method instead of OpenXml