Hello everyone
Database 12.1.0.2.0.
I have to extract some fields from an XML file, when I do with the following query, it works fine.
select RutEmisor
,RutReceptor
,TO_TIMESTAMP(TmstFirmaEnv,'YYYY-MM-DD"T"HH24:MI:SS.FF') as Fecha
from xmltable('.'
passing( select EnvioDTE_XML
from SII_DTE_EMAIL_UPLOAD
where correl = 850 )
columns RutEmisor Varchar2(10) path '/EnvioDTE/SetDTE/Caratula/RutEmisor'
,RutReceptor Varchar2(10) path '/EnvioDTE/SetDTE/Caratula/RutReceptor'
,TmstFirmaEnv Varchar2(19) path '/EnvioDTE/SetDTE/Caratula/TmstFirmaEnv'
);
RUTEMISOR RUTRECEPTO FECHA
---------------- -------------------- ----------------------------------------
79760110-1 97004000-5 25/07/19 17:47:44,000000000
but when I change it due to the XML structure, it delivers error
select RutEmisor
,RutReceptor
,TO_TIMESTAMP(TmstFirmaEnv,'YYYY-MM-DD"T"HH24:MI:SS.FF') as Fecha
from xmltable('/EnvioDTE/SetDTE/Caratula'
passing( select EnvioDTE_XML
from SII_DTE_EMAIL_UPLOAD
where correl = 850 )
columns RutEmisor Varchar2(10) path 'RutEmisor'
,RutReceptor Varchar2(10) path 'RutReceptor'
,TmstFirmaEnv Varchar2(19) path 'TmstFirmaEnv'
)
Error en la línea de comandos : 2 Columna : 15
Informe de error -
Error SQL: ORA-19276: XPST0005: El paso de XPath especifica un nombre de atributo o elemento no válido: (EnvioDTE)
19276. 00000 - "XPST0005 - XPath step specifies an invalid element/attribute name: (%s)"
*Cause: The XPath step specified invalid element or attribute name that did not match any nodes according to the input XML schema or structure.
*Action: Correct the element or attribute name as the name may be mis-spelled.
The table has the following structure.
CREATE TABLE "COBRANZA"."SII_DTE_EMAIL_UPLOAD"
( "CORREL" NUMBER NOT NULL ENABLE,
.
...
.
"PDF_ARCHIVO_NOMBRE" VARCHAR2(255 BYTE),
"ENVIODTE_XML" "SYS"."XMLTYPE" ,
CONSTRAINT "SII_DTE_Email_Upload_PK" PRIMARY KEY ("CORREL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "SII_DTE_EMAIL_UPLOAD_UK1" UNIQUE ("CASILLA_EMAIL_CONVENIO_DTE", "EMAIL_ORIGEN", "FECHA_ENVIO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
LOB ("ENVIODTE_PDF") STORE AS SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
XMLTYPE COLUMN "ENVIODTE_XML" STORE AS SECUREFILE BINARY XML (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
CACHE NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
XMLSCHEMA "EnvioDTE_v10.xsd" ELEMENT "EnvioDTE" ID 7609 DISALLOW NONSCHEMA
;
COMMENT ON COLUMN "COBRANZA"."SII_DTE_EMAIL_UPLOAD"."ESTADO" IS 'INSERTADO / PENDIENTE';
COMMENT ON COLUMN "COBRANZA"."SII_DTE_EMAIL_UPLOAD"."DTE_OPERACION" IS 'COM / VTA / CES';
CREATE OR REPLACE EDITIONABLE TRIGGER "COBRANZA"."SII_DTE_EMAIL_UPLOAD_INSERT"
before insert on "COBRANZA"."SII_DTE_EMAIL_UPLOAD"
for each row
begin
if inserting then
if :NEW."CORREL" is null then
select SEQ_SII_DTE_email_upload.nextval into :NEW."CORREL" from dual;
end if;
end if;
end;
/
ALTER TRIGGER "COBRANZA"."SII_DTE_EMAIL_UPLOAD_INSERT" ENABLE;
The contents of the EnvioDTE_XML column are here.
The DTE schema is here
To create the schema I used the following procedure.:
create or replace PROCEDURE REGSCHEMA_SII AS
v_definicion_xsd blob;
begin
select definicion_xsd
into v_definicion_xsd
from sii_schema_xml
-- where schema_sii = 'xmldsignature_v10'; -- registrar primero
-- where schema_sii = 'SiiTypes_v10'; -- registrar segundo
-- where schema_sii = 'DTE_v10'; -- registrar tercero
where schema_sii = 'EnvioDTE_v10'; -- registrar al final
-- where schema_sii = 'SetDTE_v10'; -- registrar al final
DBMS_XMLSCHEMA.REGISTERSCHEMA(
-- schemaurl => 'xmldsignature_v10.xsd',
-- schemaurl => 'SiiTypes_v10.xsd',
-- schemaurl => 'DTE_v10.xsd',
schemaurl => 'EnvioDTE_v10.xsd',
-- schemaurl => 'SetDTE_v10.xsd',
schemadoc => v_definicion_xsd,
LOCAL => true,
GENTYPES => true, --false,
-- GENBEAN => true, --false,
GENTABLES => false, --false,
FORCE => false, --false,
csid => 0, -- character set id == 0 ==> set de caracteres del archivo definicion XML
-- OPTIONS => 0,
OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
enablehierarchy => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_CONTENTS,
OWNER => USER
/*
local IN BOOLEAN := TRUE,
genTypes IN BOOLEAN := TRUE,
genBean IN BOOLEAN := FASLE,
force IN BOOLEAN := FALSE,
owner IN VARCHAR2 := NULL,
enablehierarchy IN PLS_INTEGER := DBMS_XMLSCHEMA.ENABLE_CONTENTS,
options IN PLS_INTEGER := 0
*/
);
END REGSCHEMA_SII;
some help please be very much appreciated
best regards
Carlos