I am fairly new to SQL, and very unfamiliar with XML. I want to extract the key data from the XML document below into PostgreSQL, but I am having difficulties. Basically, for every data series, I would like to parse all of the attributes, as well as the values for the different years. Can anyone help me out please? It seems that the XML document is particularly peculiar.
The expected output I would like is as follows:
Series Period Value
FG384104005.Q 2017-12-31 4.856960795039409
FG384104005.Q 2018-03-31 6.685299171442962
.....
<?xml version="1.0" encoding="utf-8" standalone="no"?>
<message:MessageGroup xmlns:message="http://www.SDMX.org/resources/SDMXML/schemas/v1_0/message" xmlns:common="http://www.SDMX.org/resources/SDMXML/schemas/v1_0/common" xmlns:frb="http://www.federalreserve.gov/structure/compact/common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.SDMX.org/resources/SDMXML/schemas/v1_0/message SDMXMessage.xsd http://www.federalreserve.gov/structure/compact/common frb_common.xsd">
<message:Header xmlns:message="http://www.SDMX.org/resources/SDMXML/schemas/v1_0/message">
<message:ID>OTHER</message:ID>
<message:Test>false</message:Test>
<message:Name>Z.1 Statistical Release</message:Name>
<message:Prepared>2019-09-19T23:31:06</message:Prepared>
<message:Sender id="FRB">
<message:Name>Federal Reserve Board</message:Name>
<message:Contact>
<message:Name>Financial Accounts of the United States Z.1 Coordinator</message:Name>
<message:Telephone>(202)-452-3000</message:Telephone>
</message:Contact>
</message:Sender>
</message:Header>
<frb:DataSet id="Z1" xmlns:kf="http://www.federalreserve.gov/structure/compact/Z1_Z1" xsi:schemaLocation="http://www.federalreserve.gov/structure/compact/Z1_Z1 Z1_Z1.xsd">
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG384104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="38" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Domestic nonfinancial sectors; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Domestic nonfinancial sectors; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.825831484374212" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="6.685299171442962" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.922904576231467" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.130042303121223" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.377391222747768" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.905098159371182" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.142025368753797" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG154104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="15" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Households and nonprofit organizations; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Households and nonprofit organizations; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.084259576315951" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.994283100284392" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.357940781533728" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.382807703277534" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.915212086558261" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.072911152729307" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.265474038700484" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="31651" SERIES_NAME="FG153165105.Q" SERIES_PREFIX="FG" SERIES_SECTOR="15" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Households and nonprofit organizations; home mortgages; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Households and nonprofit organizations; home mortgages; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.190052017480996" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.590072043418194" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.343399749580076" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.108870948819421" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.368108520717171" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.190939916139975" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.183395534476023" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="31660" SERIES_NAME="FG153166000.Q" SERIES_PREFIX="FG" SERIES_SECTOR="15" SERIES_TYPE="0" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Households and nonprofit organizations; consumer credit; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Households and nonprofit organizations; consumer credit; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="6.158260115598504" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.554759020656510" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.231760626750670" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.462344733253373" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.432498874027520" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.268915782478897" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.640293079973249" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG144104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="14" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Nonfinancial business; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Nonfinancial business; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.856960795039409" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.937026292209636" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.368250237127337" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.939649024335195" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.527273794666953" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="6.679776228429807" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.364320285758023" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG104104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="10" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Nonfinancial corporate business; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Nonfinancial corporate business; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.503452371709450" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.670254159879930" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="1.309016868338993" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.191176501703054" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.041634403281393" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="7.590014224313086" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.279407898561263" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG314104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="31" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Federal government; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Federal government; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="1.785825656428645" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="14.288117205171471" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.663750800146286" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.947833760197000" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.659446647972927" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="9.799654480658001" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.084444919189768" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG214104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="21" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>State and local governments, excluding employee retirement funds; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>State and local governments, excluding employee retirement funds; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="3.928584786168869" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-3.198453580216953" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-0.364751393518411" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-1.493354542386708" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-1.575085049703778" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-1.163191483206581" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-2.510471166127347" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG794104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="79" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Domestic financial sectors; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Domestic financial sectors; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="0.159348908897592" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="1.678531748037725" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.226028889034942" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="1.844495517503089" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.848153667727601" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.027952718415100" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="2.490888053624153" TIME_PERIOD="2019-06-30" />
</kf:Series>
<kf:Series CURRENCY="NA" FREQ="162" SERIES_INSTRUMENT="41040" SERIES_NAME="FG264104005.Q" SERIES_PREFIX="FG" SERIES_SECTOR="26" SERIES_TYPE="5" UNIT="Percent" UNIT_MULT="1">
<frb:Annotations>
<common:Annotation>
<common:AnnotationType>Short Description</common:AnnotationType>
<common:AnnotationText>Rest of the world; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
<common:Annotation>
<common:AnnotationType>Long Description</common:AnnotationType>
<common:AnnotationText>Rest of the world; debt securities and loans; liability</common:AnnotationText>
</common:Annotation>
</frb:Annotations>
<frb:Obs OBS_STATUS="A" OBS_VALUE="16.232882216382549" TIME_PERIOD="2017-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="9.322037677591291" TIME_PERIOD="2018-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="4.467988888014356" TIME_PERIOD="2018-06-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="5.907598117735855" TIME_PERIOD="2018-09-30" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-1.598513733891009" TIME_PERIOD="2018-12-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="-1.617225073562540" TIME_PERIOD="2019-03-31" />
<frb:Obs OBS_STATUS="A" OBS_VALUE="9.883975645208007" TIME_PERIOD="2019-06-30" />
</kf:Series>
</frb:DataSet>
</message:MessageGroup>
Best Answer
This is actually quite tricky because of the nested namespaces and tags.
To process something like that,
xmltable()
is the best choice (available since Postgres 10).The first xmltable() usage retrieves all
Series
tags from the document in order to be able to extract the series_name (so if the XML contains more than one<kf:Series>
this would return multiple rows.As the periods are contained as a nested list of tags, we need another
xmltable()
call to which we pass all the<frb:Obs>
tags. To be able to do that, the firstxmltable
returns the inner content of the<kf:Series>
tag as an XML value which is then used in the nextxmltable()
throughpassing series.content
I have used a
left join
for the secondxmltable
to make sure the series is returned even if there are no<kf:Obs>
sub-tags in the content.The result of the above given your input is:
Online example