Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table your_table (xml_string) as

select '<?xml version="1.0" encoding="UTF-8"?>

<cbc:DocumentType>Test Doc</cbc:DocumentType>

<cbc:ParentDocumentID>1245</cbc:ParentDocumentID>

<cac:SenderParty>

<cac:PartyTaxScheme>

<cbc:RegistrationName>SSS</cbc:RegistrationName>

<cbc:CompanyID schemeName="5" schemeID="8" schemeAgencyID="195">11000912</cbc:CompanyID>

<cac:TaxScheme>

<cbc:Name>IVA</cbc:Name>

</cac:TaxScheme>

</cac:PartyTaxScheme>

</cac:SenderParty>

<cac:Attachment>

<cac:ExternalReference>

<cbc:MimeCode>text/xml</cbc:MimeCode>

<cbc:EncodingCode>UTF-8</cbc:EncodingCode>

<cbc:Description><![CDATA[<?xml version="1.0" encoding="utf-8"?><Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:sts="dian:gov:co:facturaelectronica:Structures-2-1" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<cbc:DocumentCurrencyCode>COP</cbc:DocumentCurrencyCode>

<cac:AccountingSupplierParty>

<cbc:AdditionalAccountID schemeAgencyID="195">1</cbc:AdditionalAccountID>

<cac:Party>

<cac:PartyName>

<cbc:Name>First & Sample SSS</cbc:Name>

</cac:PartyName>

</cac:Party>

</cac:AccountingSupplierParty>

</Invoice>]]></cbc:Description>

</cac:ExternalReference>

</cac:Attachment>

</AttachedDocument>'

from dual

1 rows affected

SELECT x1.ParentDocumentID, x1.RegistrationName, x1.MimeCode,

x2.DocumentCurrencyCode, x2.PartyName

FROM your_table t

CROSS APPLY XMLTable (

XMLNamespaces (

default 'urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2',

'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",

'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"

'/AttachedDocument'

passing xmltype(t.xml_string)

columns ParentDocumentID number path 'cbc:ParentDocumentID',

RegistrationName varchar2(16) path 'cac:SenderParty/cac:PartyTaxScheme/cbc:RegistrationName',

MimeCode varchar2(10) path 'cac:Attachment/cac:ExternalReference/cbc:MimeCode',

Description clob path 'cac:Attachment/cac:ExternalReference/cbc:Description/text()'

) x1

OUTER APPLY XMLTable (

XMLNamespaces (

default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',

'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",

'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"

'/Invoice'

passing XMLType(x1.Description)

columns DocumentCurrencyCode varchar2(3) path 'cbc:DocumentCurrencyCode',

PartyName varchar2(50) path 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name'

) x2;

PARENTDOCUMENTID	REGISTRATIONNAME	MIMECODE	DOCUMENTCURRENCYCODE	PARTYNAME
1245	SSS	text/xml	COP	First & Sample SSS