PostGreSQL XML / arrays SQL ninja queries

psql_xml_ninja_queries.sql

-- extracts xlink hrefs from MD
SELECT 
	       unnest(xpath('//x:Anchor/@y:href',
	             data::xml,
	             ARRAY[ARRAY['x', 'http://www.isotc211.org/2005/gmx'],
			   ARRAY['y', 'http://www.w3.org/1999/xlink']])) AS hrefs
FROM 
       		metadata
WHERE 
       		id = 22;

-- xlinks hrefs wrong url (PIGMA)
WITH foo AS (
SELECT 
       uuid,
       unnest(xpath('//@y:href',
             data::xml,
             ARRAY[ARRAY['x', 'http://www.isotc211.org/2005/gmx'],
		   ARRAY['y', 'http://www.w3.org/1999/xlink']]))::text AS hrefs
FROM 
       		geonetwork.metadata
) 
SELECT uuid,hrefs FROM foo WHERE hrefs LIKE 'http://www.pigma.org%'
;

-- AAP (geocat)
SELECT 
       uuid
FROM 
       metadata
WHERE
  xpath_exists('//gmd:metadataMaintenance/che:CHE_MD_MaintenanceInformation/che:CHE_Appraisal_AAP', data::xml,
ARRAY[ARRAY['gmd',  'http://www.isotc211.org/2005/gmd'],
      ARRAY['che',  'http://www.geocat.ch/2008/che']])
;



-- Logos
SELECT
	id AS metadataid,
	unnest(xpath('//gmd:identificationInfo//gmd:pointOfContact/gmd:CI_ResponsibleParty/gmd:contactInfo/gmd:CI_Contact/gmd:contactInstructions/gmx:FileName/@src',
	      data::xml,
	      ARRAY[
                 ARRAY['gmd', 'http://www.isotc211.org/2005/gmd'],
                 ARRAY['gmx', 'http://www.isotc211.org/2005/gmx']
              ]))::text AS logos
FROM
        metadata;


-- descriptive Keywords (SXT issue #23894)
SELECT
	id AS metadataid,
        schemaid,
	unnest(xpath('//gmd:descriptiveKeywords/@xlink:href',
	      data::xml,
	      ARRAY[
                 ARRAY['gmd', 'http://www.isotc211.org/2005/gmd'],
                 ARRAY['xlink', 'http://www.w3.org/1999/xlink']
              ]))::text AS descrpKeywords
FROM
        metadata;


-- uuids
SELECT
   uuid
   , unnest(xpath('/gmd:MD_Metadata/gmd:fileIdentifier/gco:CharacterString/text()',
	      data::xml,
	      ARRAY[
                 ARRAY['gmd', 'http://www.isotc211.org/2005/gmd'],
                 ARRAY['gco', 'http://www.isotc211.org/2005/gco']
              ]))::text AS uuid
FROM
  metadata;


-- failing xlink from gc
SELECT DISTINCT uuid, failingLogo FROM (
SELECT
   uuid
   , unnest(xpath('/che:CHE_MD_Metadata/gmd:distributionInfo/gmd:MD_Distribution/gmd:transferOptions/gmd:MD_DigitalTransferOptions/gmd:onLine/gmd:CI_OnlineResource/gmd:linkage/gmd:URL/text()',
      data::xml,
      ARRAY[
                 ARRAY['gmd', 'http://www.isotc211.org/2005/gmd'],
                 ARRAY['che', 'http://www.geocat.ch/2008/che']
              ]))::text AS failingLogo
FROM
  metadata
) AS foo
WHERE
  failingLogo LIKE '%fname=&%'
;