Obtaining XML with same tags

• Brooke Wilke

  SSC Enthusiast

  Points: 172

  More actions

  September 26, 2007 at 6:53 am

  #181659

  Below is the code I am working with. I am trying to write the data that is in the xml below to a field. I would like to append all the data below into one field. The output should read: 5X100 18 RA661164945, 5X100 2 RA659653220.

  I can get the 5X100 18 RA661164945 to write to the field, but I cannot get the second Item group (5X100 2 RA659653220) to append. Any thoughts?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 26, 2007 at 7:20 am

  #736361

  can you show us the sql you are currently using?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Brooke Wilke

  SSC Enthusiast

  Points: 172

  More actions

  September 26, 2007 at 7:34 am

  #736366

  Here is the sql that I am currently using.

  open tranno3

  fetch next from tranno3 into @ejtranno

  while @@fetch_status = 0

  begin

  select @strXML = tranxml

  from ejsummary

  where ejtranno = @ejtranno and lower(transactioncode)in ('tlrtmtravelck1', 'tlrtmtravelck2', 'tlrtmtravelck3', 'tlrcktravel1' ,'tlrcktravel2','tlrcktravel3')

  exec sp_xml_preparedocument @xmldocpointer OUTPUT, @strXML

  insert ExportAML

  select ej.EJTranNo, ej.BankId, ej.Till, ej.TellerID, ej.ProcessingDate,

  ej.EntryDate, ej.MultiMode, ej.HostTranPost, ej.HostOutput, ej.HostPostDate,

  ej.HostRejected, ej.TransactionCode, ej.BatchNum, ej.Status, ej.ComputerID, ej.InBatch, ej.IsReversal, ej.LinkedEJTranNo, ej.IsReversed,

  ej.OverrideType, ej.OverrideId, ej.TillSeqNo, ej.BranchId, ej.FieldType1, ej.FieldValue1, ej.FieldType2, ej.FieldValue2,

  ej.FieldType3, ej.FieldValue3, ej.FieldType4, ej.FieldValue4, ej.FieldType5,

  ej.FieldValue5, ej.FieldType6, ej.FieldValue6, ej.FieldType7, ej.FieldValue7, ej.FieldType8, ej.FieldValue8, ej.FieldType9,

  ej.FieldValue9, ej.FieldType10, ej.FieldValue10, ej.FieldType11, ej.FieldValue11,

  ej.FieldType12, ej.FieldValue12, ej.FieldType13, ej.FieldValue13, ej.FieldType14, ej.FieldValue14, ej.FieldType15, ej.FieldValue15,

  ej.FieldType16, ej.FieldValue16, ej.FieldType17, ej.FieldValue17, ej.FieldType18, ej.FieldValue18, ej.FieldType19, ej.FieldValue19,

  ej.FieldType20, ej.FieldValue20, ej.FieldType21, ej.FieldValue21, ej.FieldType22,

  ej.FieldValue22, ej.FieldType23, ej.FieldValue23, ej.FieldType24, ej.FieldValue24, ej.FieldType25, ej.FieldValue25, ej.FieldType26,

  ej.FieldValue26, ej.FieldType27, ej.FieldValue27, ej.FieldType28, ej.FieldValue28, ej.FieldType29, ej.FieldValue29, ej.FieldType30,

  ej.FieldValue30, ej.FieldType31, ej.FieldValue31, ej.FieldType32, ej.FieldValue32, ej.FieldType33, ej.FieldValue33, ej.FieldType34,

  ej.FieldValue34, ej.FieldType35, ej.FieldValue35, ej.FieldType36, ej.FieldValue36, ej.FieldType37, ej.FieldValue37, ej.FieldType38,

  ej.FieldValue38, ej.FieldType39, ej.FieldValue39, ej.FieldType40, ej.FieldValue40, ej.BatchTypeId,

  Rtrim(ProcDateM) + '/' + Rtrim(ProcDateD) + '/' + ProcDateY as ProcDate,

  NumInPack + 'X' + Denom + ' ' + NumOfPack + ' ' + StartNum as ChkNum, ChkAmt, payee, Remitter, NegItemType, Purchaser, SSAID,

  IDTYPE, IDNUMBER, Address, City, State, Zip, DOB, MonLog

  from OpenXml(@xmldocpointer, '/IFX/MonSvcRq/TravChkSellAddRq', 2)

  with (

  ProcDateM Char(2) './TellerData/ProcDate/Month',

  ProcDateD Char(2) './TellerData/ProcDate/Day',

  ProcDateY Char(4) './TellerData/ProcDate/Year',

  StartNum Char(12) './NegItems/Item/StartNum',

  NumInPack Char(4)’./NegItems/Item/NumInPack’,

  Denom Char(4)’./NegItems/Item/Denom’,

  NumOfPack Char(2)’./NegItems/Item/NumOfPack’,

  ChkAmt Money './TransAmt/Amt',

  payee Varchar(255) ’./NegItems/Payee’,

  Remitter Varchar(255) ’./NegItems/Remitter’,

  NegItemType Varchar(10) './AddMonChkLogFlds/NegItemType',

  Purchaser Varchar(255) './AddMonChkLogFlds/Purchaser',

  SSAID Varchar(50) './AddMonChkLogFlds/SSAID',

  IDTYPE Varchar(10) './AddMonChkLogFlds/IDType',

  IDNUMBER Varchar(20) './AddMonChkLogFlds/IDNumber',

  Address Varchar(100) './AddMonChkLogFlds/Address',

  City Varchar(25) './AddMonChkLogFlds/City',

  State Varchar(25) './AddMonChkLogFlds/State',

  Zip Varchar(10) './AddMonChkLogFlds/Zip',

  DOB Varchar(10) './AddMonChkLogFlds/DOB',

  MonLog char(1) './AddMonChkLogFlds/MonLog'

  )

  join ejsummary ej on ej.ejtranno = @ejtranno

  exec sp_xml_removedocument @xmldocpointer

  fetch next from tranno3 into @ejtranno

  end

  close tranno3

  deallocate tranno3