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>

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

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() ?

CodePudding user response：

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.