1 Reply Latest reply on Nov 9, 2020 1:53 PM by Juan Rios

    Regular expression to get value from xml

    Bharath Tanneru New Member

      Hi all,

       

      I am having a problem to extract data from xml file

      iput example:

      <tns:AP_employee_name>Stephen</tns:AP_employee_name><tns:deptno>10A</tns:deptno><tns:salary>$2000</tns:salary>

       

      Output :10A using  regular expression

       

      any suggestions?

       

      Thanks

      Kumar

        • 1. Re: Regular expression to get value from xml
          Juan Rios New Member

          Did you solved it?

           

          You can use INSTR with SUBSTR function(s).

           

          There's 2 ways, if you are using XML reader, it would be easier, if not, set the content as string in a port, like XML_Raw_Content

           

          Example:

          out_deptno = '10A', using expression:

          RTRIM(LTRIM(                                           -- Removes leading and end spaces

          SUBSTR( XML_Raw_Content,

            INSTR( XML_Raw_Content,'<tns:deptno>') + 1,          -- Start position = 64

              (INSTR( XML_Raw_Content,'</tns:deptno>') - 14) -     -- End position (67)

              (INSTR( XML_Raw_Content,'<tns:deptno>') + 1)         -- Minus Start (64) = 3

          )

          ))

          Sometimes for debugging I use variables for start/end position. That will be easier to see/find the exact expression...

           

          This only works if the tag name is the same and does not repeat, if can be repeatable you need to use occurrence option at INSTR function.

           

          Good luck!

          - JR