xmlregexpostgresqlpostgresql-9.0

Regex for newline in XML

I'm trying desperately hard to figure this out but with no luck. I'm trying to parse this XML data in Postgres:

<map>
  <entry>
    <string>id</string>
    <string>555</string>
  </entry>
  <entry>
    <string>label</string>
    <string>Need This Value</string>
  </entry>
  <entry>
    <string>key</string>
    <string>748</string>
  </entry>
</map>

I'm trying to get the value in the string element right after <string>label</string>. Note that the Postgres version I'm working does not have the XML (libxml) function installed.

I have tried many variations of:

substring(xmlStringData from E'<string>label</string>\\n<string>(.*?)</string>')

but with no luck.

Solution

  • So I seem to got it figured out. I just needed to account for the spaces after the newline. The solution was:

    substring(event_data from E'<string>label</string>\\n\\s*?<string>(.*?)</string>')