PostgreSQL extract multi value from xml and into columns

Question

I have the below XML in each row with different data.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ns2:Declaration xmlns="urn:wco:datamodel:WCO:Declaration_DS:DMS:2" xmlns:ns2="urn:wco:datamodel:WCO:DEC-DMS:2" xmlns:ns3="urn:wco:datamodel:WCO:WCO_DEC_EDS_AUTHORISATION:1">
        <ns2:FunctionCode>9</ns2:FunctionCode>
        <ns2:ProcedureCategory>B1</ns2:ProcedureCategory>
        <ns2:FunctionalReferenceID>LRNU4YZHFFG</ns2:FunctionalReferenceID>
        <ns2:IssueDateTime>
            <DateTimeString formatCode="304">20210816084322+01</DateTimeString>
        </ns2:IssueDateTime>
        <ns2:TypeCode>EXA</ns2:TypeCode>
        <ns2:GoodsItemQuantity>2</ns2:GoodsItemQuantity>
        <ns2:DeclarationOfficeID>ABCd</ns2:DeclarationOfficeID>
        <ns2:TotalGrossMassMeasure unitCode="KGM">33000.000</ns2:TotalGrossMassMeasure>
        <ns2:TotalPackageQuantity>400</ns2:TotalPackageQuantity>
        <ns2:Submitter>
            <ns2:Name>ABC</ns2:Name>
            <ns2:ID>ABC</ns2:ID>
        </ns2:Submitter>
    </ns2:Declaration>

ID	messagebody
1	<Xml...
2	<Xml...

what I would like to have is a query that extracts some elements from the XML and put them in a table as below

ID	messagebody	FunctionalReferenceID	ProcedureCategory
1	<Xml...	LRR....	B1
2	<Xml...	LR1....	B2

I'm using the below sql to extract only 1 path

select u.val::text
from sw_customs_message scm 
   cross join unnest(xpath('//ns2:ProcedureCategory/text()', 
                     scm.messagebody::xml,  
                     array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']])) as u(val)
where u.val::text = 'H7'

How i can use the xmltable() ?

user330315 · Accepted Answer · 2022-06-09 05:20:06Z

Using xmltable() is typically easier for multiple columns (and rows), especially if namespaces come into play:

select scm.id, mb.*
from sw_customs_message scm
  cross join xmltable(xmlnamespaces ('urn:wco:datamodel:WCO:DEC-DMS:2' as ns2, 
                                     'urn:wco:datamodel:WCO:WCO_DEC_EDS_AUTHORISATION:1' as ns3), 
                      '/ns2:Declaration' 
                      passing cast(messagebody as xml) 
                      columns 
                         functional_reference_id text path 'ns2:FunctionalReferenceID', 
                         procedure_category      text path 'ns2:ProcedureCategory',
                         function_code           int  path 'ns2:FunctionCode',
                         good_items_quantity     int  path 'ns2:GoodsItemQuantity'
                      ) as mb
where mb.procedure_category = ...

messagebody should really be defined as xml so that you don't need to cast it each time you want to do something with the XML.

Collectives™ on Stack Overflow

PostgreSQL extract multi value from xml and into columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related