xml query strange effect on sql server

[Denis Miorandi]

Hi, 

    I've got a strange behavior running an xml query on sql-server 2012.

Fattout e = Tables.FATTOUT;
        
        Table<Record> FatturaPA =
                //TODO: fix per il type-safe
            DSL.table("{0}.nodes({1})", DSL.inline("fatturaPA"), "declare namespace ns3=\"http://www.fatturapa.gov.it/sdi/fatturapa/v1.1\";/ns3:FatturaElettronica")
               .as("FATT", "MAIN");
        
       Field<String> NUMERO_FATTURA =
            DSL.field("FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Numero[1]', 'varchar(80)')", String.class)
               .as("NUMERO_FATTURA");
    
       
       Field<String> DATA_FATTURA =
                DSL.field("FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Data[1]', 'varchar(80)')", String.class)
                   .as("DATA_FATTURA");
       
       Field<String> TOTALE_FATTURA =
                DSL.field("FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/ImportoTotaleDocumento[1]', 'varchar(80)')", String.class)
                   .as("TOTALE_FATTURA");
       
        SelectJoinStep<Record13<Integer, Integer, String, String, String, Integer, LocalDateTime, Integer, String, String, String, String, String>> query=create.select(
                FATTOUT.IDFATTOUT,
                REGISTRIIVA.IDRAMOAZIENDA,
                STATIFATTURA.STATOFATTURA,
                CLASSIIVA.SIGLACLASSEIVA,
                REGISTRIIVA.NOMEREGISTRO,
                FATTOUT.ADEPANNO,
                FATTOUT.DATAREGISTRO,
                FATTOUT.NUMREGISTRO,
                SEDI.SEDE.as("cliente"),
                ADEPCATEGORIE.ADEPCATEGORIADEP,
                NUMERO_FATTURA,
                DATA_FATTURA,
                TOTALE_FATTURA
                )
        .from(FATTOUT)
        .innerJoin(STATIFATTURA).on(FATTOUT.IDSTATOFATTURA.eq(STATIFATTURA.IDSTATOFATTURA))
        .innerJoin(SEDI).on(FATTOUT.IDSEDE.eq(SEDI.IDSEDE))
        .crossApply(FatturaPA)
        .leftJoin(ADEPCATEGORIE).on(ADEPCATEGORIE.IDADEPCATEGORIA.eq(FATTOUT.IDADEPCATEGORIA))
        .leftJoin(CLASSIIVA).on(CLASSIIVA.IDCLASSEIVA.eq(FATTOUT.IDCLASSEIVA))
        .leftJoin(REGISTRIIVA).on(REGISTRIIVA.IDREGISTROIVA.eq(FATTOUT.IDREGISTROIVA));

generate the sql:

SELECT [dbo].[FattOut].[IDFattOut]
	,[dbo].[RegistriIVA].[IDRamoAzienda]
	,[dbo].[StatiFattura].[StatoFattura]
	,[dbo].[ClassiIVA].[SiglaClasseIva]
	,[dbo].[RegistriIVA].[NomeRegistro]
	,[dbo].[FattOut].[ADEPAnno]
	,[dbo].[FattOut].[DataRegistro]
	,[dbo].[FattOut].[NumRegistro]
	,[dbo].[Sedi].[Sede] [cliente]
	,[dbo].[ADEPCategorie].[ADEPCategoriaDep]
	,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Numero[1]', 'varchar(80)') [NUMERO_FATTURA]
	,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Data[1]', 'varchar(80)') [DATA_FATTURA]
	,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/ImportoTotaleDocumento[1]', 'varchar(80)') [TOTALE_FATTURA]
FROM [dbo].[FattOut]
INNER JOIN [dbo].[StatiFattura] ON [dbo].[FattOut].[IDStatoFattura] = [dbo].[StatiFattura].[IDStatoFattura]
INNER JOIN [dbo].[Sedi] ON [dbo].[FattOut].[IDSede] = [dbo].[Sedi].[IDSede]
CROSS APPLY 'fatturaPA'.nodes('declare namespace ns3="http://www.fatturapa.gov.it/sdi/fatturapa/v1.1";/ns3:FatturaElettronica') [FATT]([MAIN])
LEFT JOIN [dbo].[ADEPCategorie] ON [dbo].[ADEPCategorie].[IDADEPCategoria] = [dbo].[FattOut].[IDADEPCategoria]
LEFT JOIN [dbo].[ClassiIVA] ON [dbo].[ClassiIVA].[IDClasseiva] = [dbo].[FattOut].[IDClasseiva]
LEFT JOIN [dbo].[RegistriIVA] ON [dbo].[RegistriIVA].[IDRegistroIVA] = [dbo].[FattOut].[IDRegistroIVA]

[Denis Miorandi]

sorry wrong sql post sql is the following. I've got an error on cross apply, but i don't understand why. Jooq seems to generate right query.

Removing dbo from table name it works.

Anyone knows about it?

Msg 107, Level 15, State 1, Line 18

The column prefix 'dbo' does not match with a table name or alias name used in the query.

Msg 9506, Level 16, State 1, Line 2

The XMLDT method 'nodes' can only be invoked on columns of type xml.

SELECT [dbo].[FattOut].[IDFattOut]
	,[dbo].[RegistriIVA].[IDRamoAzienda]
	,[dbo].[StatiFattura].[StatoFattura]
	,[dbo].[ClassiIVA].[SiglaClasseIva]
	,[dbo].[RegistriIVA].[NomeRegistro]
	,[dbo].[FattOut].[ADEPAnno]
	,[dbo].[FattOut].[DataRegistro]
	,[dbo].[FattOut].[NumRegistro]
	,[dbo].[Sedi].[Sede] [cliente]
	,[dbo].[ADEPCategorie].[ADEPCategoriaDep]
	,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Numero[1]', 'varchar(80)') [NUMERO_FATTURA]
	,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Data[1]', 'varchar(80)') [DATA_FATTURA]
	,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/ImportoTotaleDocumento[1]', 'varchar(80)') [TOTALE_FATTURA]
FROM [dbo].[FattOut]
INNER JOIN [dbo].[StatiFattura] ON [dbo].[FattOut].[IDStatoFattura] = [dbo].[StatiFattura].[IDStatoFattura]
INNER JOIN [dbo].[Sedi] ON [dbo].[FattOut].[IDSede] = [dbo].[Sedi].[IDSede]

CROSS APPLY [dbo].[FattOut].nodes('declare namespace ns3="http://www.fatturapa.gov.it/sdi/fatturapa/v1.1";/ns3:FatturaElettronica') [FATT]([MAIN])

LEFT JOIN [dbo].[ADEPCategorie] ON [dbo].[ADEPCategorie].[IDADEPCategoria] = [dbo].[FattOut].[IDADEPCategoria]
LEFT JOIN [dbo].[ClassiIVA] ON [dbo].[ClassiIVA].[IDClasseiva] = [dbo].[FattOut].[IDClasseiva]
LEFT JOIN [dbo].[RegistriIVA] ON [dbo].[RegistriIVA].[IDRegistroIVA] = [dbo].[FattOut].[IDRegistroIVA]

[Lukas Eder]

Hi Denis,

Thank you for your feedback. I vaguely recall having run into a similar issue on SQL Server with CROSS APPLY. What does your [FattOut] table's DDL look like?

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Denis Miorandi]

Hi Lukas, 

          all in all the issue seems not to be on jooq but in sqlserver I think but I'm not sure. Jooq generate this code that seems correct to me:

CROSS APPLY [dbo].[FattOut].[fatturaPA].nodes('declare namespace ns3="http://www.fatturapa.gov.it/sdi/fatturapa/v1.1";/ns3:FatturaElettronica') [FATT]([MAIN])

              

but SQLServer 2012 does not execute that line. It work's just removing dbo. So I'm concerning about this. If is by design on sqlserver, jooq should not write [dbo], but there is no reasong to be like this.

I don't know if you have any informations more about this.

DDL is the following

USE [Adep_FattureXML]
GO

/****** Object:  Table [dbo].[FattOut]    Script Date: 18/01/2016 08.11.19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FattOut] (
	[IDFattOut] [int] IDENTITY(1, 1) NOT NULL
	,[IDStatoFattura] [smallint] NULL
	,[IDClasseiva] [smallint] NULL
	,[IDRegistroIVA] [int] NULL
	,[IDSede] [int] NULL
	,[IDVersioneSDI] [int] NULL
	,[FatturaPA] [xml] NULL
	,[NoteInterne] [nvarchar](255) NULL
	,[DataRegistro] [datetime] NULL
	,[NumRegistro] [int] NULL
	,[ADEPAnno] [int] NULL
	,[IDADEPCategoria] [smallint] NULL
	,[Sollecito] [bit] NULL
	,[DataSollecito] [date] NULL
	,[DataCreazione] [datetime] NULL
	,[DataUM] [datetime] NULL
	,CONSTRAINT [PK_TDocTestate] PRIMARY KEY CLUSTERED ([IDFattOut] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[FattOut]
	WITH CHECK ADD CONSTRAINT [FK_DocTestate_RegistriIVA] FOREIGN KEY ([IDRegistroIVA]) REFERENCES [dbo].[RegistriIVA]([IDRegistroIVA])
GO

ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_DocTestate_RegistriIVA]
GO

ALTER TABLE [dbo].[FattOut]
	WITH NOCHECK ADD CONSTRAINT [FK_DocTestate_Sedi] FOREIGN KEY ([IDSede]) REFERENCES [dbo].[Sedi]([IDSede]) NOT
FOR REPLICATION
GO

ALTER TABLE [dbo].[FattOut] NOCHECK CONSTRAINT [FK_DocTestate_Sedi]
GO

ALTER TABLE [dbo].[FattOut]
	WITH CHECK ADD CONSTRAINT [FK_DocTestate_StatiFatture] FOREIGN KEY ([IDStatoFattura]) REFERENCES [dbo].[StatiFattura]([IDStatoFattura])
GO

ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_DocTestate_StatiFatture]
GO

ALTER TABLE [dbo].[FattOut]
	WITH CHECK ADD CONSTRAINT [FK_FattOut_ADEPCategorie] FOREIGN KEY ([IDADEPCategoria]) REFERENCES [dbo].[ADEPCategorie]([IDADEPCategoria])
GO

ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_FattOut_ADEPCategorie]
GO

ALTER TABLE [dbo].[FattOut]
	WITH CHECK ADD CONSTRAINT [FK_FattOut_ClassiIVA] FOREIGN KEY ([IDClasseiva]) REFERENCES [dbo].[ClassiIVA]([IDClasseIVA])
GO

ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_FattOut_ClassiIVA]
GO

ALTER TABLE [dbo].[FattOut]
	WITH CHECK ADD CONSTRAINT [FK_FattOut_VersioniSDI] FOREIGN KEY ([IDVersioneSDI]) REFERENCES [dbo].[VersioniSDI]([IDVersioneSDI])
GO

ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_FattOut_VersioniSDI]
GO

[Lukas Eder]

Hi Denis,

Thanks for your additional feedback.

I'm thinking too that there is probably some misunderstanding in how SQL Server's CROSS JOIN wit [xml-column].nodes() really works. I'm equally surprised by this issue.

Unfortunately, there is not much jOOQ can do when you use plain SQL like you did:

Table<Record> FatturaPA =
                //TODO: fix per il type-safe
            DSL.table("{0}.nodes({1})", DSL.inline("fatturaPA"), "declare namespace ns3=\"http://www.fatturapa.gov.it/sdi/fatturapa/v1.1\";/ns3:FatturaElettronica")
               .as("FATT", "MAIN");

Whatever you're passing in as QueryPart argument {0} will be rendered at that position.

If jOOQ had formal support for this nodes() method, things might look differently, as we could then avoid rendering the schema name. I'll be happy to look at this in the context of https://github.com/jOOQ/jOOQ/issues/4947

Hope this helps,

Lukas

[Denis Miorandi]

ok, tks Lukas

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/PcYhxv6gWkI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Dott. Denis Miorandi
via dei Vignai 27
38060 - Nogaredo (TN)