Extract values from xml and it has namespaces and parsing xml cdata

Question

I am trying to extract values from xml by using below oracle SQL query but it is retuning null data. I am not sure what is wrong in my query but it is working for regular xml(without name spaces and CDATA). Can anyone have idea how to extract values if there is CDATA and Namespace's in xml. Please help. Thanks in Advance.

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>
   </cac:Attachment>
</AttachedDocument>'),
                '/AttachedDocument' ,
                '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#"'
                ))) a1

I assume you have to declare namespaces, see i.e. : stackoverflow.com/questions/38439595/… — Siebe Jongebloed
– Siebe Jongebloed, Commented May 18, 2021 at 9:37

Alex Poole · Accepted Answer · 2021-05-18 11:34:09Z

If you take that approach then you would have to declare the namespaces in all extract() clauses, e.g.:

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
...

which is obviously going to be messy and painful; although you only need to declare the ones you refer to in your XPath.

But extract() has long been deprecated anyway, so unless you're on a really old version this would be a lot simpler with XMLTable():

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('<!-- your XML here -->')
  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;

The CDATA has to be extracted as a text node and then evaluated as a separate XMLTable; also note that the default namespace is different in your CDATA block. I've left out the unused namespaces.

Your CDATA is also malformed - it's missing closing tags for Party and Invoice. With those added to your XML document:

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>
</Invoice>]]></cbc:Description>
      </cac:ExternalReference>
   </cac:Attachment>
</AttachedDocument>')
  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;

that generates:

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

db<>fiddle

If the XML string is coming from a column in a table then you can cross join/apply to the first XMLTable clause:

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;

db<>fiddle

If you're on a version that doesn't support apply then you can cross join instead; the second join is more of a problem, but if you know you will always have the CDATA Invoice then that can be a cross-join too; here in 11gR2.

Collectives™ on Stack Overflow

Extract values from xml and it has namespaces and parsing xml cdata

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related