Hello,
I have a table with 2 columns:
CREATE TABLE XBOL.XXCNV_PRUEBA_XML
(
ID_XML NUMBER,
ARCHIVO VARCHAR2(1500)
);
Then I insert the first record with these values:
INSERT INTO XBOL.XXCNV_PRUEBA_XML
(ID_XML, ARCHIVO)
VALUES
(1, '<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID schemeAgencyName="PE:SUNAT">2.0</CustomizationID>
<ID>F003-8245</ID>
<IssueDate>2021-04-29</IssueDate>
<DueDate>2021-06-28</DueDate>
</Invoice>');
But, when I try to read the XML value I don't get results:
Query 01 (This doesn't work) I get blank values.
SELECT EXTRACTVALUE(XMLTYPE(X.ARCHIVO), '/Invoice/@xmlns') xmlns,
EXTRACTVALUE(XMLTYPE(X.ARCHIVO), '/Invoice/UBLVersionID') ublversion
FROM XBOL.XXCNV_PRUEBA_XML X
WHERE X.ID_XML = 1;
Query 02 (This doesn't work either) I don't get data
select x.version_fac, x.customizationID
from XBOL.XXCNV_PRUEBA_XML fl
,XMLTABLE('/Invoice'
PASSING XMLTYPE(fl.archivo)
COLUMNS version_fac NUMBER PATH '/Invoice/UBLVersionID'
,customizationID NUMBER PATH '/Invoice/CustomizationID'
) x
where fl.id_xml = 1;
On the other hand, when I insert a new record with another attribute for the Invoice mark, it works!!
Eg:
INSERT INTO XBOL.XXCNV_PRUEBA_XML
(ID_XML, ARCHIVO)
VALUES
(2, '<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<Invoice id="Test">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID schemeAgencyName="PE:SUNAT">2.0</CustomizationID>
<ID>F003-8245</ID>
<IssueDate>2021-04-29</IssueDate>
<DueDate>2021-06-28</DueDate>
</Invoice>');
Query 01:
SELECT EXTRACTVALUE(XMLTYPE(X.ARCHIVO), '/Invoice/@id') xmlns,
EXTRACTVALUE(XMLTYPE(X.ARCHIVO), '/Invoice/UBLVersionID') ublversion
FROM XBOL.XXCNV_PRUEBA_XML X
WHERE X.ID_XML = 2;
Query 02:
select x.version_fac, x.customizationID
from XBOL.XXCNV_PRUEBA_XML fl
,XMLTABLE('/Invoice'
PASSING XMLTYPE(fl.archivo)
COLUMNS version_fac NUMBER PATH '/Invoice/UBLVersionID'
,customizationID NUMBER PATH '/Invoice/CustomizationID'
) x
where fl.id_xml = 2;
My question is: Why it works with the second record and doesn't work with the first record?
I need this to work with the first record
Please, help me!!