Hello,
I have the following XML:
<?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:ccts="urn:un:unece:uncefact:documentation:2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:qdt="urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ext:UBLExtensions>
<ext:UBLExtension>
<ext:ExtensionContent>
<Signature xmlns="http://www.w3.org/2000/09/xmldsig#" Id="SigNode-01-F003-8245">
<SignedInfo>
<CanonicalizationMethod Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315" />
<SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1" />
<Reference URI="">
<Transforms>
<Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature" />
</Transforms>
<DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" />
<DigestValue>eNdXG2OeazA2O6ATHf84qIp0vis=</DigestValue>
</Reference>
</SignedInfo>
</Signature>
</ext:ExtensionContent>
</ext:UBLExtension>
</ext:UBLExtensions>
<cbc:UBLVersionID>2.1</cbc:UBLVersionID>
<cbc:CustomizationID schemeAgencyName="PE:SUNAT">2.0</cbc:CustomizationID>
<cbc:ID>F003-8245</cbc:ID>
<cbc:IssueDate>2021-04-29</cbc:IssueDate>
<cbc:DueDate>2021-06-28</cbc:DueDate>
<cbc:InvoiceTypeCode listAgencyName="PE:SUNAT" listID="0101" listName="Tipo de Documento" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo01">01</cbc:InvoiceTypeCode>
<cbc:DocumentCurrencyCode listAgencyName="United Nations Economic Commission for Europe" listID="ISO 4217 Alpha" listName="Currency">PEN</cbc:DocumentCurrencyCode>
<cac:OrderReference>
<cbc:ID>36018</cbc:ID>
</cac:OrderReference>
<cac:Signature>
<cbc:ID>SignatureID</cbc:ID>
<cac:SignatoryParty>
<cac:PartyIdentification>
<cbc:ID>0123456789</cbc:ID>
</cac:PartyIdentification>
<cac:PartyName>
<cbc:Name>CLIENTE 001</cbc:Name>
</cac:PartyName>
<cac:AgentParty>
<cac:PartyIdentification>
<cbc:ID>0123456789</cbc:ID>
</cac:PartyIdentification>
<cac:PartyLegalEntity>
<cbc:RegistrationName>CLIENTE 001</cbc:RegistrationName>
</cac:PartyLegalEntity>
</cac:AgentParty>
</cac:SignatoryParty>
<cac:DigitalSignatureAttachment>
<cac:ExternalReference>
<cbc:URI>SigNode-01-F003-8245</cbc:URI>
</cac:ExternalReference>
</cac:DigitalSignatureAttachment>
</cac:Signature>
</Invoice>
I need to read it but some elements have colon and I get an error ORA-31013: Invalid XPATH expression.
This is my query:
with prueba as
(select a.id_xml, a.archivo from xbol.xxcnv_prueba_xml a where a.id_xml = 1)
select y.*
from prueba x,
xmltable(xmlnamespaces(default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'),
'/' passing xmltype(x.archivo) columns Nmspace varchar2(60) path 'namespace-uri(Invoice)',
UBLVersionID varchar2(20) path '/Invoice/cbc:UBLVersionID') y;
Where:
id_xml = is a number value
archivo = is the field where the XML is stored
When I remove all the colon then it works perfectly.
Please, help me!