By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select
array_agg(
ARRAY[ substring(b, 7, position('=' in b) - 7),
replace(substring(b, position('"' in b)), '"', '')
]
)
from (
select
unnest(regexp_matches(
$a$
<mdb:MD_Metadata xmlns:mdb="http://standards.iso.org/iso/19115/-3/mdb/2.0" xmlns:cat="http://standards.iso.org/iso/19115/-3/cat/1.0" xmlns:gfc="http://standards.iso.org/iso/19110/gfc/1.1" xmlns:cit="http://standards.iso.org/iso/19115/-3/cit/2.0" xmlns:gcx="http://standards.iso.org/iso/19115/-3/gcx/1.0" xmlns:gex="http://standards.iso.org/iso/19115/-3/gex/1.0" xmlns:lan="http://standards.iso.org/iso/19115/-3/lan/1.0" xmlns:srv="http://standards.iso.org/iso/19115/-3/srv/2.1" xmlns:mas="http://standards.iso.org/iso/19115/-3/mas/1.0" xmlns:mcc="http://standards.iso.org/iso/19115/-3/mcc/1.0" xmlns:mco="http://standards.iso.org/iso/19115/-3/mco/1.0" xmlns:mda="http://standards.iso.org/iso/19115/-3/mda/1.0" xmlns:mds="http://standards.iso.org/iso/19115/-3/mds/2.0" xmlns:mdt="http://standards.iso.org/iso/19115/-3/mdt/2.0" xmlns:mex="http://standards.iso.org/iso/19115/-3/mex/1.0" xmlns:mmi="http://standards.iso.org/iso/19115/-3/mmi/1.0" xmlns:mpc="http://standards.iso.org/iso/19115/-3/mpc/1.0" xmlns:mrc="http://standards.iso.org/iso/19115/-3/mrc/2.0" xmlns:mrd="http://standards.iso.org/iso/19115/-3/mrd/1.0" xmlns:mri="http://standards.iso.org/iso/19115/-3/mri/1.0" xmlns:mrl="http://standards.iso.org/iso/19115/-3/mrl/2.0" xmlns:mrs="http://standards.iso.org/iso/19115/-3/mrs/1.0" xmlns:msr="http://standards.iso.org/iso/19115/-3/msr/2.0" xmlns:mdq="http://standards.iso.org/iso/19157/-2/mdq/1.0" xmlns:mac="http://standards.iso.org/iso/19115/-3/mac/2.0" xmlns:gco="http://standards.iso.org/iso/19115/-3/gco/1.0" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://standards.iso.org/iso/19115/-3/mdb/2.0 https://schemas.isotc211.org/19115/-3/mdb/2.0/mdb.xsd">
<mdb:metadataIdentifier>
<mcc:MD_Identifier>
<mcc:code>
<gco:CharacterString>34831b8d-9da1-4a19-b425-482f982381d9</gco:CharacterString>
</mcc:code>
<mcc:codeSpace>
<gco:CharacterString>urn:uuid</gco:CharacterString>
</mcc:codeSpace>
</mcc:MD_Identifier>
</mdb:metadataIdentifier>
<mdb:defaultLocale>
<lan:PT_Locale id="FR">
<lan:language>
<lan:LanguageCode codeList="http://www.loc.gov/standards/iso639-2/" codeListValue="fre" />
</lan:language>
<lan:characterEncoding>
<lan:MD_CharacterSetCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_CharacterSetCode" codeListValue="utf8" />
</lan:characterEncoding>
</lan:PT_Locale>
</mdb:defaultLocale>
<mdb:metadataScope>
<mdb:MD_MetadataScope>
<mdb:resourceScope>
<mcc:MD_ScopeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ScopeCode" codeListValue="dataset" />
</mdb:resourceScope>
| array_agg |
|---|
| {{mdb,http://standards.iso.org/iso/19115/-3/mdb/2.0},{cat,http://standards.iso.org/iso/19115/-3/cat/1.0},{gfc,http://standards.iso.org/iso/19110/gfc/1.1},{cit,http://standards.iso.org/iso/19115/-3/cit/2.0},{gcx,http://standards.iso.org/iso/19115/-3/gcx/1.0},{gex,http://standards.iso.org/iso/19115/-3/gex/1.0},{lan,http://standards.iso.org/iso/19115/-3/lan/1.0},{srv,http://standards.iso.org/iso/19115/-3/srv/2.1},{mas,http://standards.iso.org/iso/19115/-3/mas/1.0},{mcc,http://standards.iso.org/iso/19115/-3/mcc/1.0},{mco,http://standards.iso.org/iso/19115/-3/mco/1.0},{mda,http://standards.iso.org/iso/19115/-3/mda/1.0},{mds,http://standards.iso.org/iso/19115/-3/mds/2.0},{mdt,http://standards.iso.org/iso/19115/-3/mdt/2.0},{mex,http://standards.iso.org/iso/19115/-3/mex/1.0},{mmi,http://standards.iso.org/iso/19115/-3/mmi/1.0},{mpc,http://standards.iso.org/iso/19115/-3/mpc/1.0},{mrc,http://standards.iso.org/iso/19115/-3/mrc/2.0},{mrd,http://standards.iso.org/iso/19115/-3/mrd/1.0},{mri,http://standards.iso.org/iso/19115/-3/mri/1.0},{mrl,http://standards.iso.org/iso/19115/-3/mrl/2.0},{mrs,http://standards.iso.org/iso/19115/-3/mrs/1.0},{msr,http://standards.iso.org/iso/19115/-3/msr/2.0},{mdq,http://standards.iso.org/iso/19157/-2/mdq/1.0},{mac,http://standards.iso.org/iso/19115/-3/mac/2.0},{gco,http://standards.iso.org/iso/19115/-3/gco/1.0},{gml,http://www.opengis.net/gml/3.2},{xlink,http://www.w3.org/1999/xlink},{xsi,http://www.w3.org/2001/XMLSchema-instance}} |
SELECT 1