Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT EXTRACT (VALUE (a1), '/AttachedDocument/ParentDocumentID/text()').getStringVal () AS ParentDocumentID
      ,EXTRACT (VALUE (a1), '/AttachedDocument/SenderParty/PartyTaxScheme/RegistrationName/text()').getStringVal () AS RegistrationName
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/MimeCode/text()').getStringVal () AS MimeCode
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/Description/DocumentCurrencyCode/text()').getStringVal () AS DocumentCurrencyCode
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/Description/AccountingSupplierParty/Party/PartyName/Name/text()').getStringVal () AS PartyName
FROM 
       TABLE (
          XMLSEQUENCE (
             EXTRACT ( xmltype(
                '<?xml version="1.0" encoding="UTF-8"?>
<AttachedDocument xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#">
   <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 &amp; Sample SSS</cbc:Name>
      </cac:PartyName>
        </cac:AccountingSupplierParty>]]></cbc:Description>
      </cac:ExternalReference>


PARENTDOCUMENTID
REGISTRATIONNAME
MIMECODE
DOCUMENTCURRENCYCODE
PARTYNAME


null
null
null
null
null


SELECT EXTRACT (VALUE (a1), '/AttachedDocument/cbc:ParentDocumentID/text()',
                'xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" 
                                                                 xmlns:ds="http://www.w3.org/2000/09/xmldsig#" 
                                                                 xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" 
                                                                 xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" 
                                                                 xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" 
                                                                 xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" 
                                                                 xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" 
                                                                 xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#"'
       ).getStringVal () AS ParentDocumentID
      ,EXTRACT (VALUE (a1), '/AttachedDocument/SenderParty/PartyTaxScheme/RegistrationName/text()').getStringVal () AS RegistrationName
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/MimeCode/text()').getStringVal () AS MimeCode
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/Description/DocumentCurrencyCode/text()').getStringVal () AS DocumentCurrencyCode
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/Description/AccountingSupplierParty/Party/PartyName/Name/text()').getStringVal () AS PartyName
FROM 
       TABLE (
          XMLSEQUENCE (
             EXTRACT ( xmltype(
                '<?xml version="1.0" encoding="UTF-8"?>
<AttachedDocument xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#">
   <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">


PARENTDOCUMENTID
REGISTRATIONNAME
MIMECODE
DOCUMENTCURRENCYCODE
PARTYNAME


1245
null
null
null
null


SELECT x1.ParentDocumentID, x1.RegistrationName, x1.MimeCode,
  x2.DocumentCurrencyCode, x2.PartyName
FROM 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('<?xml version="1.0" encoding="UTF-8"?>
<AttachedDocument xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#">
   <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 &amp; Sample SSS</cbc:Name>
      </cac:PartyName>
    </cac:Party>
  </cac:AccountingSupplierParty>


PARENTDOCUMENTID
REGISTRATIONNAME
MIMECODE
DOCUMENTCURRENCYCODE
PARTYNAME


1245
SSS
text/xml
COP
First & Sample SSS