peppol_invoice_invoice_line_item_additional_item_property_v.sql

peppol_invoice_invoice_line_item_additional_item_property_v.sql

create or replace view peppol_invoice_invoice_line_item_additional_item_property_v
as
select
    rownum seq_id,
    v.invoice_id,
    v."ID" "InvoiceLine_ID",
    c."Name",
    c."Value"
from peppol_invoice_invoice_line_v v,
xmltable(
    xmlnamespaces(
        default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
        'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
        'urn:un:unece:uncefact:documentation:2' as "ccts",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as "ext",
        'urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2' as "qdt",
        'urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2' as "udt",
        'http://www.w3.org/2001/XMLSchema' as "xsd",
        'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
    ),
    '/cac:InvoiceLine/cac:Item/cac:AdditionalItemProperty[*]'
    passing v."InvoiceLine"
    columns
        "Name"       varchar2(4000)  path 'cbc:Name',
        "Value"      varchar2(4000)  path 'cbc:Value'
) c;