Avatar of 25112
25112
 asked on

the size of xml to stall a proc

if the xml gets a little big, then the proc gets very slow.. could it be the server resource of the code of the proc?
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Saurabh Bhadauria

8/22/2022 - Mon
25112

ASKER
the code is


/******************************************************************************  
Input       : @pFileName  
              @pXmlExaminees  
              @pAuditUserName  
----------------------------------------------------------------------------  
Sample Call Syntax Using Variables:  
exec dbo.proc_BchSingleXMLExamineeUpload @pFileName, @pXmlExaminees, @pAuditUserName  
******************************************************************************/  
CREATE       PROCEDURE [dbo].[proc_BchSingleXMLExamineeUpload]  
 @pFileName varchar(25),  
 @pXmlExaminees text,  
 @pAuditUserName varchar(24)  
AS  
BEGIN  
 
 DECLARE @TempTestCenterFK as varchar(20)  
   
 ----------------Import into @tmpExaminee----------------  
 DECLARE @tmpExaminee TABLE  
 (  
  [Seq] [smallint] ,  
  [InfoDt] [varchar] (15) NULL ,  
  [ExID] [varchar] (50) NULL ,  
  [LNm] [varchar] (50) NULL ,  
  [FNm] [varchar] (50) NULL ,  
  [MI] [char] (1) NULL ,  
  [Sfx] [char] (10) NULL ,  
  [IDType] [varchar] (10) NULL ,  
  [IDNum] [varchar] (15) NULL ,  
  [DOB] [varchar] (15) NULL ,  
  [EthCd] [varchar] (10) NULL ,  
  [PrLan] [varchar] (10) NULL ,  
  [GenCd] [varchar] (10) NULL ,  
  [MilCd] [varchar] (10) NULL ,  
  [StAdd] [varchar] (100) NULL ,  
  [City] [varchar] (50) NULL ,  
  [StCd] [varchar] (5) NULL ,  
  [ZipCd] [varchar] (10) NULL ,  
  [SubJIDCd] [varchar] (5) NULL ,  
  [EdLvl] [varchar] (10) NULL ,  
  [LastYr] [varchar] (10) NULL ,  
  [PTest] [char] (1) NULL ,  
  [SU01] [char] (1) NULL ,  
  [SU02] [char] (1) NULL ,  
  [TCCd] [varchar] (20) NULL ,  
  [ICCd] [varchar] (10) NULL ,  
  [CredStat] [char] (1) NULL ,  
  [CredDt] [varchar] (15) NULL,  
  [TestCenterFK] [varchar] (20) NULL  
 )  
   
 DECLARE @hdoc INT  
 EXEC sp_xml_preparedocument @hdoc OUTPUT, @pXmlExaminees  
   
 INSERT INTO @tmpExaminee  
 (  
  Seq,  
  InfoDt,  
  ExID,  
  LNm,  
  FNm,  
  MI,  
  Sfx,  
  IDType,  
  IDNum,  
  DOB,  
  EthCd,  
  PrLan,  
  GenCd,  
  MilCd,  
  StAdd,  
  City,  
  StCd,  
  ZipCd,  
  SubJIDCd,  
  EdLvl,  
  LastYr,  
  PTest,  
  SU01,  
  SU02,  
  TCCd,  
  ICCd,  
  CredStat,  
  CredDt  
 )  
 (SELECT  
  Seq,  
  InfoDt,  
  ExID,  
  UPPER(RTRIM(LNm)),  
  UPPER(RTRIM(FNm)),  
  UPPER(RTRIM(MI)),  
  UPPER(RTRIM(Sfx)),  
  IDType,  
  Right(IDNum,9),    
  DOB,  
  EthCd,  
  PrLan,  
  GenCd,  
  MilCd,  
  UPPER(RTRIM(StAdd)),  
  UPPER(RTRIM(City)),  
  UPPER(RTRIM(StCd)),  
  left(ZipCd,5),  
  SubJIDCd,  
  EdLvl,  
  LastYr,  
  PTest,  
  SU01,  
  SU02,  
  TCCd,  
  ICCd,  
  CredStat,  
  CredDt  
 FROM OPENXML(@hdoc,'/Root/Demo') WITH (  
  Seq [smallint] 'Seq',  
  InfoDt [varchar] (15) 'InfoDt',  
  ExID [varchar] (50) 'ExID',  
  LNm [varchar] (50) 'LNm',  
  FNm [varchar] (50) 'FNm',  
  MI [char] (1) 'MI',  
  Sfx [char] (10) 'Sfx',  
  IDType [varchar] (10) 'IDType',  
  IDNum [varchar] (15) 'IDNum',  
  DOB [varchar] (15) 'DOB',  
  EthCd [varchar] (10) 'EthCd',  
  PrLan [varchar] (10) 'PrLan',  
  GenCd [varchar] (10) 'GenCd',  
  MilCd [varchar] (10) 'MilCd',  
  StAdd [varchar] (100) 'StAdd',  
  City [varchar] (50) 'City',  
  StCd [varchar] (5) 'StCd',  
  ZipCd [varchar] (10) 'ZipCd',  
  SubJIDCd [varchar] (5) 'SubJIDCd',  
  EdLvl [char] (10) 'EdLvl',  
  LastYr [varchar] (10) 'LastYr',  
  PTest [char] (1) 'PTest',  
  SU01 [char] (1) 'SU01',  
  SU02 [char] (1) 'SU02',  
  TCCd [varchar] (20) 'TCCd',  
  ICCd [varchar] (10) 'ICCd',  
  CredStat [char] (1) 'CredStat',  
  CredDt [varchar] (15) 'CredDt')  
 )  
   
 ------------------------------------------  
 UPDATE  @tmpExaminee  
 SET TestCenterFK = TestCenterPK  
 FROM  tblTestCenter AS TC  
 WHERE TC.TestCenterNumber =  
  CASE  
   When SUBSTRING(ExID, 1, 3) = 'PPB' Then  
    SUBSTRING(TCCd, LEN(TCCd)-3, 4)    
   ELSE  
    SUBSTRING(TCCd, LEN(TCCd)-5, 4)  
   END  
   
 UPDATE  @tmpExaminee  
 SET TestCenterFK = 'OSS' +  
 CASE  
   When SUBSTRING(ExID, 1, 3) = 'PPB' Then  
    TCCd -- Pearson VUE  
   ELSE  
    SUBSTRING(TCCd, 1, LEN(TCCd)-2)
   END  
 WHERE TestCenterFK IS NULL  
 ----------------------------------------------  
 DECLARE @tmpTestScore TABLE    
 (  
  [Seq] [smallint] NULL,  
  [ExID] [varchar] (50)  NULL ,  
  [TestDt] [varchar] (15)  NULL ,  
  [TestNbr] [varchar] (10)  NULL ,  
  [TCcd] [varchar] (20) NULL ,  
  [Fmt] [varchar] (10)  NULL ,  
  [Frm] [varchar] (10)  NULL ,  
  [Lan] [varchar] (10) NULL ,  
  [Std] [varchar] (10)  NULL ,  
  [Rnk] [varchar] (10)  NULL ,  
  [FErr] [varchar] (10)  NULL ,  
  [AErr] [varchar] (10)  NULL ,  
  [MErr] [varchar] (10)  NULL ,  
  [QErr] [varchar] (10)  NULL ,  
  [StageTestFK] [int] NULL ,  
  [TestFormFK] [varchar] (20) NULL ,  
  [TestCenterFK] [varchar] (20) NULL ,  
  [StageExamineeFK] [int] NULL  
 )  
   
 INSERT INTO @tmpTestScore  
 (  
  Seq,  
  ExID,  
  TestDt,  
  TestNbr,  
  TCCd,  
  Fmt,  
  Frm,  
  Lan,  
  Std,  
  Rnk,  
  FErr,  
  AErr,  
  MErr,  
  QErr  
 )  
 (SELECT  
  Seq,  
  ExID,  
  TestDt,  
  TestNbr,  
  TCCd,  
  Fmt,  
  Frm,  
  Lan,  
  Std,  
  Rnk,  
  FErr,  
  AErr,  
  MErr,  
  QErr  
 FROM OPENXML(@hdoc,'/Root/Test') WITH (    
  Seq [smallint] 'Seq',  
  ExID [varchar] (50)  'ExID' ,  
  TestDt [varchar] (15)  'TestDt' ,  
  TestNbr [varchar] (10)  'TestNbr' ,  
  TCCd [varchar] (20)  'TCCd' ,  
  Fmt [varchar] (10)  'Fmt' ,  
  Frm [varchar] (10)  'Frm' ,  
  Lan [varchar] (10)  'Lan' ,  
  Std [varchar] (10)  'Std' ,  
  Rnk [varchar] (10)  'Rnk',  
  FErr [varchar] (10)  'FErr',  
  AErr [varchar] (10)  'AErr',  
  MErr [varchar] (10)  'MErr',  
  QErr [varchar] (10)  'MErr')  
   
 )  
   
 UPDATE  @tmpTestScore  
 SET TestCenterFK = TestCenterPK, @TempTestCenterFK = TestCenterPK  
 FROM  tblTestCenter AS TC  
 WHERE TC.TestCenterNumber =  
  CASE  
   When SUBSTRING(ExID, 1, 3) = 'PPB' Then  
    SUBSTRING(TCCd, LEN(TCCd)-3, 4)
   ELSE  
    SUBSTRING(TCCd, LEN(TCCd)-5, 4)  
   END  
   
 UPDATE  @tmpTestScore  
 SET TestCenterFK = 'OSS' +  
 CASE  
   When SUBSTRING(ExID, 1, 3) = 'PPB' Then  
    TCCd  
   ELSE  
    SUBSTRING(TCCd, 1, LEN(TCCd)-2)  
   END  
 WHERE TestCenterFK IS NULL  
   
 UPDATE  @tmpTestScore  
 SET @TempTestCenterFK = 'OSS' +  
 CASE  
   When SUBSTRING(ExID, 1, 3) = 'PPB' Then
    TCCd  
   ELSE  
    SUBSTRING(TCCd, 1, LEN(TCCd)-2)  
   END  
 WHERE TestCenterFK IS NULL  
   
   
   
 UPDATE @tmpTestScore  
 SET TestFormFK =  
  CASE dbo.udf_GetTestFormPK(Fmt, Frm, SUBSTRING(TestDt,1,4))  
   WHEN 0 THEN 'OSS' + Fmt + '~' + Frm + '~' + SUBSTRING(TestDt,1,4)  
   ELSE CONVERT(VARCHAR(20),dbo.udf_GetTestFormPK(Fmt, Frm, SUBSTRING(TestDt,1,4)))  
  END  
   
   
 UPDATE @tmpTestScore  
 SET Lan =  
  CASE Lan  
   WHEN '1' THEN '1'  
   WHEN '6' THEN '1'  
   WHEN '7' THEN '1'  
   WHEN '3' THEN '3'  
   WHEN '4' THEN '2'  
   WHEN '5' THEN '1'  
   WHEN '17' THEN '1'  
   WHEN '19' THEN '3'  
   WHEN '20' THEN '2'  
   ELSE 'OSS' + Lan  
  END  
 ---------------------------------------  
 DECLARE @maxHandle AS INT  
 SELECT @maxHandle = MAX(SEQ) FROM @tmpExaminee  
 DECLARE @handle AS INT  
 SET @handle = 1  
 DECLARE @ExID AS VARCHAR(50)  
   
 WHILE (@handle <= @maxHandle)  
 BEGIN  
  SELECT @ExID = ExID FROM @tmpExaminee WHERE Seq = @handle  
  DECLARE @ExamineeExists AS BIT  
  SELECT @ExamineeExists=COUNT(*) FROM tblStageExaminee WHERE ExamineeID=@ExID  
   
  IF(@ExamineeExists>0)  
   UPDATE tblStageExaminee  
   SET  PPBID = IDNum,  
    PPBIDTypeFK = IDType,  
    FirstName = FNm,  
    MiddleInitial = MI,  
    LastName = LNm,  
    NameSuffix = Sfx,  
    AddressLine1 = StAdd,  
    City = tE.City,  
    State = StCd,  
    ZipCode = SUBSTRING(ZipCd,1,5),  
    ZipCodeExtn =  
     CASE  
      WHEN LEN(ZipCd) > 5 THEN REPLACE(SUBSTRING(ZipCd, 6, LEN(ZipCd)), '-','')  
      ELSE NULL  
     END,  
    EdNA_CountyFK = SubJIDCd,  
    PrimaryLanguage = PrLan,  
    DateofBirth = DOB,  
    Gender = GenCd,  
    PPBEthnicCodeFK = EthCd,  
    HighestGradeCompleted = EdLvl,  
    WithdrawalYear = LastYr,  
    PAResidencyInd =  
     CASE SU01  
      WHEN 'N' THEN 0  
      ELSE 1  
     END,  
    SDIssuedDiplomaInd =  
     CASE SU02  
      WHEN 'Y' THEN 1  
      ELSE  0  
     END,  
    PracticeTestInd = PTest,  
    CredStatusInd = CredStat,  
    CredStatusDate = CredDt,  
    InstructionalCenterCode = ICCd,  
    StageExamineeRecordStatusFK = 1,  
    ByPassInd = 0,    
    TestCenterFK = @TempTestCenterFK,  
    MilitaryExamineeInd = MilCd,  
    AuditChanPPBate = GETDATE(),  
    AuditChangeUserName = @pAuditUserName  
   FROM @tmpExaminee AS tE  
   WHERE  Seq = @handle AND  
    ExamineeID = @ExID
  ELSE  
   INSERT INTO tblStageExaminee(  
    ExamineeID,  
    PPBID,  
    PPBIDTypeFK,  
    FirstName,  
    MiddleInitial,  
    LastName,  
    NameSuffix,  
    AddressLine1,  
    City,  
    State,  
    ZipCode,  
    ZipCodeExtn,  
    EdNA_CountyFK,  
    PrimaryLanguage,  
    DateofBirth,  
    Gender,  
    PPBEthnicCodeFK,  
    HighestGradeCompleted,  
    WithdrawalYear,  
    PAResidencyInd,  
    SDIssuedDiplomaInd,  
    PracticeTestInd,  
    CredStatusInd,  
    CredStatusDate,  
    InstructionalCenterCode,  
    StageExamineeRecordStatusFK,  
    ByPassInd,  
    TestCenterFK,  
    MilitaryExamineeInd,  
    AuditAddDate,  
    AuditAddUserName,  
    AuditChanPPBate,  
    AuditChangeUserName)  
   (SELECT  
    ExID,  
    IDNum,  
    IDType,  
    FNm,  
    MI,  
    LNm,  
    Sfx,  
    StAdd,  
    City,  
    StCd,  
    SUBSTRING(ZipCd,1,5),  
    CASE  
     WHEN LEN(ZipCd) > 5 THEN REPLACE(SUBSTRING(ZipCd, 6, LEN(ZipCd)), '-','')  
     ELSE NULL  
    END,  
    SubJIDCd,  
    PrLan,  
    DOB,  
    GenCd,  
    EthCd,  
    EdLvl,  
    LastYr,  
    CASE SU01  
     WHEN 'N' THEN 0  
     ELSE 1  
    END,  
    CASE SU02  
     WHEN 'Y' THEN 1  
     ELSE 0  
    END,  
    PTest,      
    CredStat,  
    CredDt,  
    ICCd,  
    1,  
    0,  
    @TempTestCenterFK,  
    MilCd,  
    GETDATE(),  
    @pAuditUserName,  
    GETDATE(),  
    @pAuditUserName  
   FROM @tmpExaminee AS tE  
   WHERE  Seq = @handle  
   )  
   
  DECLARE @StageExamineePK AS INT  
  SELECT @StageExamineePK = StageExamineePK  
  FROM tblStageExaminee  
  WHERE ExamineeID=@ExID  
   
  ----------------Import into tblStageTest----------------  
  UPDATE @tmpTestScore  
  SET StageExamineeFK = SE.StageExamineePK  
  FROM tblStageExaminee AS SE  
  WHERE Seq = @handle AND  
   ExamineeID = @ExID  
   
  INSERT INTO tblStageTest(  
   StageExamineeFK,  
   TestCenterFK,  
   TestDate,  
   AuditAddDate,  
   AuditAddUserName,  
   AuditChanPPBate,  
   AuditChangeUserName)  
  (SELECT DISTINCT  
   StageExamineeFK,  
   tTS.TestCenterFK,  
   TestDt,  
   GETDATE(),  
   @pAuditUserName,  
   GETDATE(),  
   @pAuditUserName  
  FROM @tmpTestScore AS tTS  
  WHERE Seq = @handle AND  
   CONVERT(VARCHAR(10),tTS.StageExamineeFK) + '~' + tTS.TestDt + '~' + tTS.TestCenterFK NOT IN  
    (SELECT CONVERT(VARCHAR(10),StageExamineeFK) + '~' + TestDate + '~' + TestCenterFK FROM tblStageTest)  
  )  
   
   
  UPDATE tblStageExaminee  
   SET  ByPassInd = 1  
  WHERE StageExamineePK not in (Select distinct StageExamineeFK from tblStageTest)  
   
  DECLARE @count Int  
  SELECT @count = count(*)  from tblStageExaminee where StageExamineePK = @StageExamineePK and ByPassInd = 1  
  IF (@count > 0)  
  BEGIN  
   EXEC sp_xml_removedocument @hdoc  
   RETURN  
  END  
 
  UPDATE @tmpTestScore  
  SET StageTestFK = ST.StageTestPK  
  FROM @tmpTestScore AS tTS  
  INNER JOIN tblStageTest AS ST ON  
   tTS.StageExamineeFK = ST.StageExamineeFK AND  
   tTS.TestDt = ST.TestDate AND  
   tTS.TestcenterFK = ST.TestcenterFK  
  WHERE Seq = @handle  
   
  ----------------Import into tblStageTestScore----------------  
  UPDATE  tblStageTestScore  
  SET TestFormFK = tTS.TestFormFK,  
   TestLanguageFK = Lan,  
   SubjectScore = Std,  
   ScorePercent = Rnk,  
   InvalidTestForm = FErr,  
   InvalidAge = AErr,  
   MaxRetestError =MErr,  
   QuestionableActivity = QErr,  
   AuditChanPPBate = GETDATE(),  
   AuditChangeUserName = @pAuditUserName  
  FROM @tmpTestScore tTS  
  WHERE Seq = @handle AND  
   tTS.StageTestFK = tblStageTestScore.StageTestFK AND  
   tTS.TestNbr = CONVERT(VARCHAR(10),TestSubjectFK)  
   
  INSERT INTO tblStageTestScore(  
   StageTestFK,  
   TestFormFK,  
   TestSubjectFK,  
   TestLanguageFK,  
   SubjectScore,  
   ScorePercent,  
   InvalidTestForm,  
   InvalidAge,  
   MaxRetestError,  
   QuestionableActivity,  
   AuditAddDate,  
   AuditAddUserName,  
   AuditChanPPBate,  
   AuditChangeUserName)  
  (SELECT  
   StageTestFK,  
   TestFormFK,  
   TestNbr,  
   Lan,  
   Std,  
   Rnk,  
   FErr,  
   AErr,  
   MErr,  
   QErr,  
   GETDATE(),  
   @pAuditUserName,  
   GETDATE(),  
   @pAuditUserName  
  FROM @tmpTestScore tTS  
  WHERE Seq = @handle AND  
   CONVERT(VARCHAR(10),tTS.StageTestFK) + '~' + tTS.TestNbr NOT IN  
   (SELECT CONVERT(VARCHAR(10),StageTestFK) + '~' + CONVERT(VARCHAR(10),TestSubjectFK) FROM tblStageTestScore)  
  )  
   
   
   
  INSERT INTO tblStageExamineeReason  
  (  
   StageExamineeFK,  
   TestReasonFK,  
   AuditAddDate,  
   AuditAddUserName  
  )  
  (  
  SELECT  
   SE.StageExamineePK,  
   RFT,  
   GETDATE(),  
   @pAuditUserName  
  FROM OPENXML(@hdoc,'/Root/Reason') WITH (  
   Seq [smallint] 'Seq' ,  
   ExID [varchar] (50)  'ExID' ,  
   RFT [varchar] (5)  'RFT') AS XR  
  INNER JOIN tblStageExaminee AS SE ON  
   XR.ExID = SE.ExamineeID  
  WHERE  Seq = @handle AND  
   CONVERT(VARCHAR(10),StageExamineePK) + '~' + RFT NOT IN  
    (SELECT CONVERT(VARCHAR(10),StageExamineeFK) + '~' + CONVERT(VARCHAR(5),TestReasonFK) FROM tblStageExamineeReason)  
  )  
  SET @handle = @handle + 1  
 END  
   
 EXEC sp_xml_removedocument @hdoc  
   
 EXEC proc_BchBatchProcessStageExaminee @StageExamineePK, @pFileName, @pAuditUserName  
END
ASKER CERTIFIED SOLUTION
Saurabh Bhadauria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
selva_kongu

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
25112

ASKER
Saurabh, appreciate your input..
1) in the following example, you would say table variable is OK.. (small size).. but in general, i will follow your advice to make it into a temp table. what is the cut off you keep (100 records, 200 records etc).

2)OK.. i will change  open_xml to be used only once..

3)i am not understanding the loop/while idea.. can you give a small example? thanks.

here is an actual small parameter.



exec proc_BchSingleXMLExamineeUpload @pFileName = N'CO201209272359.xml', @pXmlExaminees = '<Root>
  <Demo>
    <Seq>20</Seq>
    <InfoDt>20101016</InfoDt>
    <ExID>1202728976</ExID>
    <LNm>P</LNm>
    <FNm>John</FNm>
    <MI>O</MI>
    <Sfx>          </Sfx>
    <IDType>1</IDType>
    <IDNum>202728976</IDNum>
    <DOB>19000101</DOB>
    <EthCd>4</EthCd>
    <PrLan>1</PrLan>
    <GenCd>1</GenCd>
    <MilCd>2</MilCd>
    <StAdd>Some Address</StAdd>
    <City>Some City</City>
    <StCd>??</StCd>
    <ZipCd>12345</ZipCd>
    <SubJIDCd>039</SubJIDCd>
    <EdLvl>7         </EdLvl>
    <LastYr>2007</LastYr>
    <PTest>2</PTest>
    <SU01>Y</SU01>
    <SU02>N</SU02>
    <TCCd>300041040005</TCCd>
    <ICCd />
    <CredStat>0</CredStat>
    <CredDt />
  </Demo>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120926</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>10</Frm>
    <Std>400</Std>
    <Rnk>16</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101211</TestDt>
    <TestNbr>2</TestNbr>
    <Fmt>1</Fmt>
    <Frm>8</Frm>
    <Std>430</Std>
    <Rnk>24</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120612</TestDt>
    <TestNbr>3</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>480</Std>
    <Rnk>42</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120612</TestDt>
    <TestNbr>4</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>450</Std>
    <Rnk>31</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120612</TestDt>
    <TestNbr>5</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>450</Std>
    <Rnk>31</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120612</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>380</Std>
    <Rnk>12</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120612</TestDt>
    <TestNbr>2</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>410</Std>
    <Rnk>18</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101113</TestDt>
    <TestNbr>3</TestNbr>
    <Fmt>1</Fmt>
    <Frm>2</Frm>
    <Std>440</Std>
    <Rnk>27</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101016</TestDt>
    <TestNbr>4</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>440</Std>
    <Rnk>27</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101113</TestDt>
    <TestNbr>5</TestNbr>
    <Fmt>1</Fmt>
    <Frm>2</Frm>
    <Std>450</Std>
    <Rnk>31</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20110205</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>5</Frm>
    <Std>380</Std>
    <Rnk>12</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101016</TestDt>
    <TestNbr>2</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>410</Std>
    <Rnk>18</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20110409</TestDt>
    <TestNbr>3</TestNbr>
    <Fmt>1</Fmt>
    <Frm>3</Frm>
    <Std>430</Std>
    <Rnk>24</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120919</TestDt>
    <TestNbr>4</TestNbr>
    <Fmt>1</Fmt>
    <Frm>10</Frm>
    <Std>430</Std>
    <Rnk>24</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101016</TestDt>
    <TestNbr>5</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>330</Std>
    <Rnk>4</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20110409</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>3</Frm>
    <Std>370</Std>
    <Rnk>10</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041040005</TCCd>
    <TestDt>20120919</TestDt>
    <TestNbr>2</TestNbr>
    <Fmt>1</Fmt>
    <Frm>10</Frm>
    <Std>390</Std>
    <Rnk>14</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101016</TestDt>
    <TestNbr>3</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>360</Std>
    <Rnk>8</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20110205</TestDt>
    <TestNbr>4</TestNbr>
    <Fmt>1</Fmt>
    <Frm>5</Frm>
    <Std>420</Std>
    <Rnk>21</Rnk>
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101211</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>8</Frm>
    <Std>0</Std>
    <Rnk />
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101113</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>2</Frm>
    <Std>0</Std>
    <Rnk />
    <FErr />
  </Test>
  <Test>
    <Seq>20</Seq>
    <Lan>1</Lan>
    <ExID>1202728976</ExID>
    <TCCd>300041048000</TCCd>
    <TestDt>20101016</TestDt>
    <TestNbr>1</TestNbr>
    <Fmt>1</Fmt>
    <Frm>7</Frm>
    <Std>0</Std>
    <Rnk />
    <FErr />
  </Test>
  <Reason>
    <Seq>20</Seq>
    <ExID>1202728976</ExID>
    <RFT>13</RFT>
  </Reason>
</Root>', @pAuditUserName = N'SRBBO\magros'
SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Saurabh Bhadauria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck