Hello,
I have the next XML into a BLOB field:
<?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:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:qdt="urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2" xmlns:rsc="urn:rsc:names:specification:ubl:peru:schema:xsd:RscAggregateComponents-1" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<cbc:UBLVersionID>2.1</cbc:UBLVersionID>
<cbc:CustomizationID schemeAgencyName="PE:SUNAT">2.0</cbc:CustomizationID>
<cbc:ID>F001-0077889</cbc:ID>
<cbc:IssueDate>2021-01-13</cbc:IssueDate>
<cbc:IssueTime>14:34:20</cbc:IssueTime>
<cbc:DueDate>2021-01-31</cbc:DueDate>
<cbc:InvoiceTypeCode name="Tipo de Operacion" listSchemeURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo51" listID="0101">01</cbc:InvoiceTypeCode>
<cbc:Note languageLocaleID="1000"><![CDATA[CUATRO MIL SETECIENTOS SETENTA Y NUEVE CON 00/100 SOLES]]></cbc:Note>
<cbc:DocumentCurrencyCode listID="ISO 4217 Alpha" listName="Currency" listAgencyName="United Nations Economic Commission for Europe">PEN</cbc:DocumentCurrencyCode>
<cac:Signature>
<cbc:ID>IDSign20557425889</cbc:ID>
<cac:SignatoryParty>
<cac:PartyIdentification>
<cbc:ID schemeName="Documento de Identidad" schemeAgencyName="PE:SUNAT" schemeURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo06">20557425889</cbc:ID>
</cac:PartyIdentification>
<cac:PartyName>
<cbc:Name><![CDATA[FIBERLUX SAC]]></cbc:Name>
</cac:PartyName>
</cac:SignatoryParty>
<cac:DigitalSignatureAttachment>
<cac:ExternalReference>
<cbc:URI>#signature20557425889</cbc:URI>
</cac:ExternalReference>
</cac:DigitalSignatureAttachment>
</cac:Signature>
<cac:TaxTotal>
<cbc:TaxAmount currencyID="PEN">729.00</cbc:TaxAmount>
<cac:TaxSubtotal>
<cbc:TaxableAmount currencyID="PEN">4050.00</cbc:TaxableAmount>
<cbc:TaxAmount currencyID="PEN">729.00</cbc:TaxAmount>
<cac:TaxCategory>
<cbc:ID schemeID="UN/ECE 5305" schemeName="Tax Category Identifier" schemeAgencyName="United Nations Economic Commission for Europe">S</cbc:ID>
<cac:TaxScheme>
<cbc:ID schemeName="Codigo de tributos" schemeAgencyName="PE:SUNAT" schemeURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo05">1000</cbc:ID>
<cbc:Name>IGV</cbc:Name>
<cbc:TaxTypeCode>VAT</cbc:TaxTypeCode>
</cac:TaxScheme>
</cac:TaxCategory>
</cac:TaxSubtotal>
</cac:TaxTotal>
<cac:LegalMonetaryTotal>
<cbc:LineExtensionAmount currencyID="PEN">4050.00</cbc:LineExtensionAmount>
<cbc:TaxInclusiveAmount currencyID="PEN">4779.00</cbc:TaxInclusiveAmount>
<cbc:AllowanceTotalAmount currencyID="PEN">0.00</cbc:AllowanceTotalAmount>
<cbc:ChargeTotalAmount currencyID="PEN">0.00</cbc:ChargeTotalAmount>
<cbc:PayableAmount currencyID="PEN">4779.00</cbc:PayableAmount>
</cac:LegalMonetaryTotal>
<cac:InvoiceLine>
<cbc:ID>1</cbc:ID>
<cbc:InvoicedQuantity unitCode="ZZ" unitCodeListID="UN/ECE rec 20" unitCodeListAgencyName="United Nations Economic Commission for Europe">1.000</cbc:InvoicedQuantity>
<cbc:LineExtensionAmount currencyID="PEN">850.00</cbc:LineExtensionAmount>
<cac:PricingReference>
<cac:AlternativeConditionPrice>
<cbc:PriceAmount currencyID="PEN">1003.000000000</cbc:PriceAmount>
<cbc:PriceTypeCode listName="Tipo de Precio" listAgencyName="PE:SUNAT" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo16">01</cbc:PriceTypeCode>
</cac:AlternativeConditionPrice>
</cac:PricingReference>
<cac:TaxTotal>
<cbc:TaxAmount currencyID="PEN">153.00</cbc:TaxAmount>
<cac:TaxSubtotal>
<cbc:TaxableAmount currencyID="PEN">850.00</cbc:TaxableAmount>
<cbc:TaxAmount currencyID="PEN">153.00</cbc:TaxAmount>
<cac:TaxCategory>
<cbc:ID schemeID="UN/ECE 5305" schemeName="Tax Category Identifier" schemeAgencyName="United Nations Economic Commission for Europe">S</cbc:ID>
<cbc:Percent>18.00</cbc:Percent>
<cbc:TaxExemptionReasonCode listAgencyName="PE:SUNAT" listName="Afectacion del IGV" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo07">10</cbc:TaxExemptionReasonCode>
<cac:TaxScheme>
<cbc:ID schemeName="Codigo de tributos" schemeAgencyName="PE:SUNAT" schemeURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo05">1000</cbc:ID>
<cbc:Name>IGV</cbc:Name>
<cbc:TaxTypeCode>VAT</cbc:TaxTypeCode>
</cac:TaxScheme>
</cac:TaxCategory>
</cac:TaxSubtotal>
</cac:TaxTotal>
<cac:Item>
<cbc:Description><![CDATA[PRESTACION DE SERVICIOS - INTERNET]]></cbc:Description>
<cac:SellersItemIdentification>
<cbc:ID><![CDATA[S02]]></cbc:ID>
</cac:SellersItemIdentification>
</cac:Item>
<cac:Price>
<cbc:PriceAmount currencyID="PEN">850.000000000</cbc:PriceAmount>
</cac:Price>
</cac:InvoiceLine>
<cac:InvoiceLine>
<cbc:ID>2</cbc:ID>
<cbc:InvoicedQuantity unitCode="ZZ" unitCodeListID="UN/ECE rec 20" unitCodeListAgencyName="United Nations Economic Commission for Europe">1.000</cbc:InvoicedQuantity>
<cbc:LineExtensionAmount currencyID="PEN">2600.00</cbc:LineExtensionAmount>
<cac:PricingReference>
<cac:AlternativeConditionPrice>
<cbc:PriceAmount currencyID="PEN">3068.000000000</cbc:PriceAmount>
<cbc:PriceTypeCode listName="Tipo de Precio" listAgencyName="PE:SUNAT" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo16">01</cbc:PriceTypeCode>
</cac:AlternativeConditionPrice>
</cac:PricingReference>
<cac:TaxTotal>
<cbc:TaxAmount currencyID="PEN">468.00</cbc:TaxAmount>
<cac:TaxSubtotal>
<cbc:TaxableAmount currencyID="PEN">2600.00</cbc:TaxableAmount>
<cbc:TaxAmount currencyID="PEN">468.00</cbc:TaxAmount>
<cac:TaxCategory>
<cbc:ID schemeID="UN/ECE 5305" schemeName="Tax Category Identifier" schemeAgencyName="United Nations Economic Commission for Europe">S</cbc:ID>
<cbc:Percent>18.00</cbc:Percent>
<cbc:TaxExemptionReasonCode listAgencyName="PE:SUNAT" listName="Afectacion del IGV" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo07">10</cbc:TaxExemptionReasonCode>
<cac:TaxScheme>
<cbc:ID schemeName="Codigo de tributos" schemeAgencyName="PE:SUNAT" schemeURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo05">1000</cbc:ID>
<cbc:Name>IGV</cbc:Name>
<cbc:TaxTypeCode>VAT</cbc:TaxTypeCode>
</cac:TaxScheme>
</cac:TaxCategory>
</cac:TaxSubtotal>
</cac:TaxTotal>
<cac:Item>
<cbc:Description><![CDATA[PRESTACION DE SERVICIOS - TRANSMISION DE DATOS]]></cbc:Description>
<cac:SellersItemIdentification>
<cbc:ID><![CDATA[S03]]></cbc:ID>
</cac:SellersItemIdentification>
</cac:Item>
<cac:Price>
<cbc:PriceAmount currencyID="PEN">2600.000000000</cbc:PriceAmount>
</cac:Price>
</cac:InvoiceLine>
<cac:InvoiceLine>
<cbc:ID>3</cbc:ID>
<cbc:InvoicedQuantity unitCode="ZZ" unitCodeListID="UN/ECE rec 20" unitCodeListAgencyName="United Nations Economic Commission for Europe">1.000</cbc:InvoicedQuantity>
<cbc:LineExtensionAmount currencyID="PEN">600.00</cbc:LineExtensionAmount>
<cac:PricingReference>
<cac:AlternativeConditionPrice>
<cbc:PriceAmount currencyID="PEN">708.000000000</cbc:PriceAmount>
<cbc:PriceTypeCode listName="Tipo de Precio" listAgencyName="PE:SUNAT" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo16">01</cbc:PriceTypeCode>
</cac:AlternativeConditionPrice>
</cac:PricingReference>
<cac:TaxTotal>
<cbc:TaxAmount currencyID="PEN">108.00</cbc:TaxAmount>
<cac:TaxSubtotal>
<cbc:TaxableAmount currencyID="PEN">600.00</cbc:TaxableAmount>
<cbc:TaxAmount currencyID="PEN">108.00</cbc:TaxAmount>
<cac:TaxCategory>
<cbc:ID schemeID="UN/ECE 5305" schemeName="Tax Category Identifier" schemeAgencyName="United Nations Economic Commission for Europe">S</cbc:ID>
<cbc:Percent>18.00</cbc:Percent>
<cbc:TaxExemptionReasonCode listAgencyName="PE:SUNAT" listName="Afectacion del IGV" listURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo07">10</cbc:TaxExemptionReasonCode>
<cac:TaxScheme>
<cbc:ID schemeName="Codigo de tributos" schemeAgencyName="PE:SUNAT" schemeURI="urn:pe:gob:sunat:cpe:see:gem:catalogos:catalogo05">1000</cbc:ID>
<cbc:Name>IGV</cbc:Name>
<cbc:TaxTypeCode>VAT</cbc:TaxTypeCode>
</cac:TaxScheme>
</cac:TaxCategory>
</cac:TaxSubtotal>
</cac:TaxTotal>
<cac:Item>
<cbc:Description><![CDATA[PRESTACION DE SERVICIOS - SEGURIDAD GESTIONADA]]></cbc:Description>
<cac:SellersItemIdentification>
<cbc:ID><![CDATA[S04]]></cbc:ID>
</cac:SellersItemIdentification>
</cac:Item>
<cac:Price>
<cbc:PriceAmount currencyID="PEN">600.000000000</cbc:PriceAmount>
</cac:Price>
</cac:InvoiceLine>
</Invoice>
I need to read all the values inside cac: InvoiceLine.
In this case they are 3.
I only managed to show the 3 rows but not the values inside.
My Query is:
with prueba as
(select FL.NOMBRE_ARCHIVO, FL.ARCHIVO_BLOB, FL.ESTADO, FL.FECHA_PROCESO
from XBOL.XXCNV_RPA_BLOB_XML FL
where fl.NOMBRE_ARCHIVO = 'FIBERLUX - 88494.xml')
select y.*
from prueba x,
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"),
'/' passing
xmltype(x.ARCHIVO_BLOB, nls_charset_id('CHAR_CS')) --xmltype(x.file_data)
columns Nmspace varchar2(60) path 'namespace-uri(Invoice)',
UBLVersionID varchar2(20) path '/Invoice/cbc:UBLVersionID',
NID varchar2(20) path '/Invoice/cbc:ID',
fecha_emision varchar2(20) path '/Invoice/cbc:IssueDate',
CustomizationID varchar2(20) path
'/Invoice/cbc:CustomizationID',
SignatureID varchar2(50) path
'/Invoice/cac:Signature/cbc:ID',
invoice_line XMLTYPE PATH '/Invoice/cac:InvoiceLine') y,
XMLTABLE(xmlnamespaces('urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc")
, '/cac:InvoiceLine' passing y.invoice_line
COLUMNS
linea varchar2(20) path 'cac:cbc:ID',
Cantidad varchar2(20) path 'cac:cbc:InvoicedQuantity');
I get this result:
Please, help me!