Consulting

Results 1 to 2 of 2

Thread: Parse XML String into Columns or Fields

  1. #1

    Parse XML String into Columns or Fields

    Hello.

    I am trying to parse an XML string into separate columns. Below is the XML .

    <tracking_data tracking_data_site='03063' seq_no='426' mpe_id='DSM-1'  softwareversion=''  run_start='08-01-2018 17:00:00' sortplan_1='' mpesortplanname_1='Dorap18' operation_1=''
    >
    <MailPiece Timestamp="08-01-2018 00:46:36.000" USPSdate="2018_03_20_t3" Result="Missent" MODSrun="3" Operation="222" IU="4" IUname="FSD" DU="257" DUname="CH-565" DST="257" DSTname="CH-565" Tray="37" IUtype="Unmanned" IDtype="Unknown" MDev="4" MDevName="FSD" MReason="4" MReasonName="Singulation Problem" Operation_no="222" SpecServiceCode_1="4202006699989401915901057903319581"/>
    </tracking_data>

    For example I want to extract the fields from the XML string to look like this:

    tracking_data_site seq_no mpe_id
    03063 426 DSM-1

    Is there a more simplified way of doing this besides using left, mid or right string functions?

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,080
    Location
    Well using the Left, Mid & Right string functions is the usual method for doing what you want, especially when coupled with the Instring Function to find the headings and the Len Function to get the overall length of the string.
    However if the format of the data is fixed then it is simple enough to just use left, right & mid.
    You can also use the VarSplit Function and place the data into into an array based on the spaces before working on those first 4 shorter strings to obtain the parts you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •