I am having problems when "migrating" my query in which I parse a column with XML stored in it, i "migrating" from T-SQL to Postgres, since my current development requires it. In T-SQL this is my current query:
My query in T-SQL, takes a column that contains XML that are retrieved from another system, I create the prefixes for the xml with the "XMLNAMESPACES" and then I do an INSERT INTO to save the query in a previously created table, with the subquery I "choose" the values or "columns" that I want to return, cast them and rename them at the same time. After this I cast the "XML" column to "XML" and it is already in "VARCHAR" and finally the .nodes function is called to return the table derived from the query
WITH XMLNAMESPACES('http://www.sat.gob.mx/cfd/4' AS cfdi,
'http://www.sat.gob.mx/Pagos20' AS pago20,
'http://www.sat.gob.mx/TimbreFiscalDigital' AS tfd)
INSERT INTO [CFDI_SON990511MI0_RECIBIDOS].[dbo].[DataXml_SON990511MI0_RepsRecibidos_2023] (UUID, DoctoRelacionado, NumParcialidad, ImpPagado, FePago)
SELECT
prx4_0.*
FROM
(
SELECT
UUID,
x.value('(@IdDocumento)', 'NVARCHAR(64)') AS DoctoRelacionado,
x.value('(@NumParcialidad)', 'TINYINT') AS NumParcialidad,
x.value('(@ImpPagado)', 'DECIMAL(26, 8)') AS ImpPagado,
x.value('(/cfdi:Comprobante/@Fecha)[1]', 'DATETIME') AS FePago
FROM [dbo].[XML]
--El casteo ocurre aqui
CROSS APPLY(SELECT CAST(XML AS XML)) A(TheXml)
--Se llama a la funcion .nodes para retornar la tabla derivada de la consulta
CROSS APPLY TheXml.nodes('/cfdi:Comprobante/cfdi:Complemento/pago20:Pagos/pago20:Pago/pago20:DoctoRelacionado') B(x)
) prx4_0
GO
This is an XML to be able to reproduce:
-- DDL and sample data population, start
DECLARE @tbl TABLE (UUID UNIQUEIDENTIFIER PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT @tbl (UUID, xmldata) VALUES
('AAA1ADA1-9151-4C90-AA62-43590DCC7C81',
N'<cfdi:Comprobante xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sat.gob.mx/cfd/3 http://www.sat.gob.mx/sitio_internet/cfd/3/cfdv33.xsd
http://www.sat.gob.mx/Pagos http://www.sat.gob.mx/sitio_internet/cfd/Pagos/Pagos10.xsd"
LugarExpedicion="37480" TipoDeComprobante="P" Total="0" Moneda="XXX" Certificado="MIIG"
SubTotal="0" NoCertificado="00001000000504465028" Sello="V1" Fecha="2023-01-09T17:09:53" Version="3.3"
xmlns:cfdi="http://www.sat.gob.mx/cfd/3" xmlns:pago10="http://www.sat.gob.mx/Pagos">
<cfdi:Emisor Rfc="CARE891026810" Nombre="MARIA ELIZABETH CHAVEZ RAMIREZ" RegimenFiscal="621"></cfdi:Emisor>
<cfdi:Receptor Rfc="SON990511MI0" Nombre="SONIGAS SA DE CV" UsoCFDI="P01"></cfdi:Receptor>
<cfdi:Conceptos>
<cfdi:Concepto ClaveProdServ="84111506" Cantidad="1" ClaveUnidad="ACT" Descripcion="Pago" ValorUnitario="0" Importe="0"></cfdi:Concepto>
</cfdi:Conceptos>
<cfdi:Complemento>
<pago10:Pagos Version="1.0">
<pago10:Pago FechaPago="2022-12-27T00:00:00" FormaDePagoP="03" MonedaP="MXN" Monto="31508.65">
<pago10:DoctoRelacionado IdDocumento="AAA1D7E6-23AD-44FC-A544-9CB47D9CFCCD" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="999.99" ImpPagado="999.99" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA124C5-48FC-4409-BB76-8A23BF6318F3" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="6904.91" ImpPagado="6904.91" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA17CA8-222A-4146-A943-58AAFD62C7F3" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="2284.99" ImpPagado="2284.99" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA111BC-4CE8-4572-8C75-2D673F1ED9F4" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="1800.00" ImpPagado="1800.00" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA1686A-7605-423D-809F-A6EE240E9587" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="3229.99" ImpPagado="3229.99" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA1783C-A24A-4DC6-8A87-54591E555CCE" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="2599.99" ImpPagado="2599.99" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA17618-B009-4A80-8C12-CF7F697A512E" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="3167.95" ImpPagado="3167.95" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA1DBB5-B99E-4BFB-AD27-FC249EDEEBCE" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="2972.86" ImpPagado="2972.86" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA19C00-65FB-4E83-9AD7-2EF660694ECF" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="4629.99" ImpPagado="4629.99" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
<pago10:DoctoRelacionado IdDocumento="AAA1CD78-9581-4F43-8741-8C9506E26B2C" MonedaDR="MXN" MetodoDePagoDR="PPD" NumParcialidad="1" ImpSaldoAnt="2739.99" ImpPagado="2739.99" ImpSaldoInsoluto="0"></pago10:DoctoRelacionado>
</pago10:Pago>
</pago10:Pagos>
<registrofiscal:CFDIRegistroFiscal xmlns:schemaLocation="http://www.sat.gob.mx/registrofiscal http://www.sat.gob.mx/sitio_internet/cfd/cfdiregistrofiscal/cfdiregistrofiscal.xsd" Version="1.0" Folio="2301200000009898" xmlns:registrofiscal="http://www.sat.gob.mx/registrofiscal">
</registrofiscal:CFDIRegistroFiscal>
<tfd:TimbreFiscalDigital xmlns:tfd="http://www.sat.gob.mx/TimbreFiscalDigital" xsi:schemaLocation="http://www.sat.gob.mx/TimbreFiscalDigital http://www.sat.gob.mx/sitio_internet/cfd/TimbreFiscalDigital/TimbreFiscalDigitalv11.xsd" Version="1.1" UUID="AAA1ADA1-9151-4C90-AA62-43590DCC7C81" FechaTimbrado="2023-01-09T17:20:57" RfcProvCertif="SAT970701NN3" SelloCFD="V1cULko9bjPwFcWBsjJbPDHo92KXkK2VT12bxm9F+9XiiD4oNDE3mpLaf4L1iKndhotlcx14MphlBAfRBRm/ue+eR6YAR+v+t3ZLXpD0QzhJzKCF0WSIkt7kDMhJmroYluMs+D2GwELC9t/LxhmjY5k5OyEBoQm75hfD1EpfN+j1MzZrRj/DuLLp4oVnb64/wfwKmJ0BxtsR7Dwpe7Q/SvuR7W3GnuRIqL/gFKBfsH7qW9fP+4owoX2iERCX5RWIt+yVOejOGMPlMzE7pdihEuulq32aGV1AlyZNqKG9FukbOnSqRJnZdqmz/VqufWEC1h7C8S105tCuDGfldyDy4Q==" NoCertificadoSAT="00001000000504465028" SelloSAT="U09+TX0gGO45iDdPi0gN9CttScMvfj5G7OF8biKdijrPIWSNO7PzYdoGauKvOP6UCgW/x8MpRN+TfoVvdVEmpFrQC1jD7dpbAqWTeh+q1Qsb+5dm3lbgHxp3ImHACR7DORIxOadF/1oj6s0t69UGkTmsDOs90uVDsLL8RWf/yKTwRN+PpSdqy3FCHLMMOfZKZM76xjIVPQT6Hl2n8HhtaBTL/CiKb095wHTl/mXfX5u7UNlOoUUsG2wMeo+lJOqf5e0M0cJ4V3t5Cqr8ZhZu6atLDBqTSDpDSbJh1f3H9OLqoOTMgzSLkmU0jA+Q0TdknP7ujhkZPK2c/gJaJO91Cw==" />
</cfdi:Complemento>
</cfdi:Comprobante>');
-- DDL and sample data population, end
and this is my current code in postgres:
WITH
XMLNAMESPACES AS (
SELECT
'http://www.sat.gob.mx/cfd/3' AS cfdi,
'http://www.sat.gob.mx/Pagos' AS pago10,
'http://www.sat.gob.mx/TimbreFiscalDigital' AS tfd,
'http://www.sat.gob.mx/sitio_internet/cfd/cfdiregistrofiscal/cfdiregistrofiscal.xsd' AS registrofiscal)
SELECT
"UUID"
, unnest(xpath('/cfdi:Comprobante/@Serie',"XML"::xml , '{{cfdi, http://www.sat.gob.mx/cfd/3}}')::varchar[]) AS cfd_serie
from public."Pago_XML";
This is the error I get when I want to perform the query:
SQL Error [2200M]: ERROR: could not parse XML document¶ Detail: line 1: xmlns:schemaLocation: 'http://www.sat.gob.mx/registrofiscal http://www.sat.gob.mx/sitio_internet/cfd/cfdiregistrofiscal/cfdiregistrofiscal.xsd' is not a valid URI¶p://www.sat.gob.mx/sitio_internet/cfd/cfdiregistrofiscal/cfdiregistrofiscal.xsd"¶
I was already investigating my table already generated in T-SQL, it does not give me this error and it is not possible for me to make a replacement for that "URI" since the table is from a provider. I would greatly appreciate any help, if you need more information about the XML, table or query I can provide it.
This my actual table on T-SQL and and it is also the output I expect with the postgres query
| UUID | FePago | DoctoRelacionado | NumParcialidad | ImpPagado |
|---|---|---|---|---|
| 0009034a-3feb-4b2e-88fa-4605e5c6a7d4 | 2023-01-20 12:00:00.000 | EE98EEDA-915B-B34D-8FFF-DD7FA49E2490 | 1 | 287124.37000000 |
| 0010D6C2-AB5E-45F5-B8AB-5EE57C79BAE8 | 2022-12-28 12:00:00.000 | FADF8224-0D47-48E6-B8A4-362861D9EB00 | 1 | 16923.24000000 |
| 00157E99-984B-42A0-BA55-D57D76E9155E | 2023-04-26 12:00:00.000 | D912107F-C1E9-4078-8F8D-7D954446CF8A | 1 | 14160.70000000 |
T-SQL VERSION (SELECT @@VERSION)
version |
----------------------------------------------------------|
Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64) Aug 16 2023 00:09:21 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: ) (Hypervisor)|
Postgres version (SELECT version();)
version |
----------------------------------------------------------|
PostgreSQL 15.3, compiled by Visual C++ build 1914, 64-bit|
2
Answers
The answer that @Yitzhak Kavinsky gave me served me perfectly, since there are 3 tables that have to be formed with 3 different XML formats, the first 2 I was successful with their due adjustments. With the latter you have a slight problem when parsing the XML.
This is the error: ERROR: Column XPath expression returned more than one value.
The error comes from this "row":
When invoking the XMLTABLE function, you specify an XPath expression that generates rows and my the row generation expression is the XPath expression is this:
I based myself on that documentation to better understand the XMLTABLE:
https://www.ibm.com/docs/es/i/7.5?topic=sssf-using-xmltable-reference-xml-content-as-relational-table
I also tried the examples that come there, such as: Returning a row for each value and Handling non-existent path values, but I did not have success. I leave a DDL with a test case.
This is my current table:
This is the output I intend to obtain:
There is incorrect xsi:schemaLocation values in the XML.
I had to remove them from the XML.
PostgreSQL XML shredding is similar to Oracle implementation.
dbfiddle
Postres SQL
Output