awk field separator with in the xml

Tags: awk

I have a xml file with the following data.

<record record_no = "2" error_code="100">&quot;18383531&quot;;&quot;22677833&quot;;&quot;21459732&quot;;&quot;41001&quot;;&quot;394034&quot;;&quot;0208&quot;;&quot;Prime Lending - ;Corporate  - 2201&quot;;&quot;&quot;;&quot;Prime Lending - Lacey - 2508&quot;;&quot;Prime Lending - Lacey - 2508&quot;;&quot;1&quot;;&quot;rrvc&quot;;&quot;Tiffany Poe&quot;;&quot;HEIDI&quot;;&quot;BUNDY&quot;;&quot;000002274&quot;;&quot;2.0&quot;;&quot;18.0&quot;;&quot;2&quot;;&quot;362661&quot;;&quot;Rejected by IRS&quot;;&quot;A1AAA&quot;;&quot;20160720&quot;;&quot;1021&quot;;&quot;HEDI &amp; Bundy&quot;;&quot;4985045838&quot;;&quot;PPASSESS&quot;;&quot;Web&quot;;&quot;3683000826&quot;;&quot;823&quot;;&quot;IC W2&quot;;&quot;&quot;;&quot;&quot;;&quot;&quot;;&quot;&quot;;&quot;Rapid_20160801_Monthly.txt&quot;;&quot;20160720102100&quot;;&quot;&quot;;&quot;20160803095309&quot;;&quot;286023&quot;;&quot;RGT&quot;;&quot;1&quot;;&quot;14702324400223&quot;;&quot;14702324400223&quot;;&quot;0&quot;;&quot;OMCProcessed&quot;

I'm using following code:

cat RR_00404.fin.bc_lerr.xml.bc| awk 'BEGIN { FS=OFS=";" }/<record/ { gsub(/&quot;/,"\"");  gsub(/.*=" ">.*/,"",$1);print $1,$40,$43,$46 ,"'base_err_xml'", "0",$7; }' 

The idea is to do the following:

  1. Replace &quote; with "
  2. Extract the error_code
  3. Print " and ; seperated values.
  4. Use sqlldr to load ( not to worry about this).

Problem to solve:

  1. There is ; within the text. e.g Prime Lending -;Corporate - 2201
  2. There's &amp;


100;"20160803095309";"1";"1";"base_err_xml";"0";"Prime Lending
100;"286023";"14702324400223";"OMCProcessed";"base_err_xml";"0";"Prime Lending - Corporate  - 2201"
100;"286024-1";"";"OMCProcessed";"base_err_xml";"0";"Prime Lending - Corporate  - 2201"


awk is the wrong tool for this job, without some preprocessing. Here, we use XMLStarlet for the first pass (decoding all XML entities and splitting attributes off into separate fields), and GNU awk for the second (reading those fields and performing whatever transforms or logic you actually need):


# reads XML on stdin; puts record_no in first field, error code in second,
# ...record content for remainder of output line.

xmlstarlet sel -t -m '//record' \
  -v ./@record_no -o ';' \
  -v ./@error_code -o ';' \
  -v . -n

...and, cribbed from the GNU awk documentation...

#!/bin/env gawk -f
# must be GNU awk for the FPAT feature

    FPAT = "([^;]*)|(\"[^\"]*\")"

    print "NF = ", NF
    for (i = 1; i 

This video can help you solving your question :)
By: admin