SQL SERVER XQuery node processing problem

Question

I have to process data from xml by date of operation, simulating day by day, during 4th months process in the data base with a XML file that looks like this:

<Operaciones_por_Dia>
  <OperacionDia fecha="2020-01-30">
    <PagoRecibo TipoRecibo="5" NumFinca="9782331"/>
    <PagoRecibo TipoRecibo="5" NumFinca="6696849"/>
    <TransConsumo id="1" LecturaM3="325" descripcion="Cobro Mensual" NumFinca="3336538"/>
    <TransConsumo id="3" LecturaM3="40" descripcion="Lectura errónea" NumFinca="2425954"/>
  </OperacionDia>
  <OperacionDia fecha="2020-04-08">
   <PagoRecibo TipoRecibo="7" NumFinca="1423800"/>
   <PagoRecibo TipoRecibo="7" NumFinca="1393022"/>
   <TransConsumo id="2" LecturaM3="22" descripcion="Reclamo de cliente" NumFinca="2101885"/>
  </OperacionDia>
</Operaciones_por_Dia>

When I prepare the data, I store all the dates from XML into a variable table and then I iterate from the min date throught the max date (@FechaOperacion has stored that date), then I have to process the node data where the date match with the date from xml node .

I try it doing this, but @DocumentoXML.value('(/Operaciones_por_Dia/OperacionDia/@fecha)[1]', 'DATE') don't match well, the correct data that I have to compare is Fecha with @FechaOperacion, but I don't know how can I get that value with Xquery.

INSERT @PagosHoy (NumFinca, TipoRecibo, Fecha)
        select ph.value('@NumFinca', 'INT')
            , ph.value('@TipoRecibo', 'INT')
            , ph.value('../@fecha', 'DATE')
        from @DocumentoXML.nodes('/Operaciones_por_Dia/OperacionDia/PagoRecibo') AS t(ph)
        where @DocumentoXML.value('(/Operaciones_por_Dia/OperacionDia/@fecha)[1]', 'DATE') = @FechaOperacion

With the following code it work well, but I want to know how to do it like the other way.

INSERT INTO @PagosHoy(NumFinca,TipoRecibo,Fecha)  
SELECT [NumFinca],[TipoRecibo],[fechaDeIngreso]
FROM OPENXML (@hdoc, 'Operaciones_por_Dia/OperacionDia/PagoRecibo',1)  
WITH (  [NumFinca]      VARCHAR(30) '@NumFinca',  
        [TipoRecibo]    INT         '@TipoRecibo',
        [fechaDeIngreso]    VARCHAR(100)    '../@fecha')
WHERE [fechaDeIngreso] = @FechaOperacion
EXEC spProcesaPagos @PagosHoy

Answer 1

Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. It is strongly recommended to re-write your SQL and switch it to XQuery.

Here is another method similar to what is proposed by @AlwaysLearning, but more simple.

It is using XPath predicate instead of WHERE clause.

SQL

-- DDL and sample data population, start
DECLARE @PagosHoy TABLE (ID INT IDENTITY PRIMARY KEY, NumFinca INT, TipoRecibo INT, Fecha DATE);
DECLARE @xml XML = 
N'<Operaciones_por_Dia>
    <OperacionDia fecha="2020-01-30">
        <PagoRecibo TipoRecibo="5" NumFinca="9782331"/>
        <PagoRecibo TipoRecibo="5" NumFinca="6696849"/>
        <TransConsumo id="1" LecturaM3="325" descripcion="Cobro Mensual"
                      NumFinca="3336538"/>
        <TransConsumo id="3" LecturaM3="40" descripcion="Lectura errónea"
                      NumFinca="2425954"/>
    </OperacionDia>
    <OperacionDia fecha="2020-04-08">
        <PagoRecibo TipoRecibo="7" NumFinca="1423800"/>
        <PagoRecibo TipoRecibo="7" NumFinca="1393022"/>
        <TransConsumo id="2" LecturaM3="22" descripcion="Reclamo de cliente"
                      NumFinca="2101885"/>
    </OperacionDia>
</Operaciones_por_Dia>';
-- DDL and sample data population, end


DECLARE @FechaOperacion DATE = '2020-01-30';

INSERT @PagosHoy (NumFinca, TipoRecibo, Fecha)
SELECT c.value('@NumFinca', 'INT')
    , c.value('@TipoRecibo', 'INT')
    , @FechaOperacion AS FechaOperacion
FROM @xml.nodes('/Operaciones_por_Dia/OperacionDia[@fecha eq sql:variable("@FechaOperacion")]/PagoRecibo') AS t(c)

-- test
SELECT * FROM @PagosHoy;

Output

+----+----------+------------+------------+
| ID | NumFinca | TipoRecibo |   Fecha    |
+----+----------+------------+------------+
|  1 |  9782331 |          5 | 2020-01-30 |
|  2 |  6696849 |          5 | 2020-01-30 |
+----+----------+------------+------------+

Answer 2

The where clause in your first example queries /Operaciones_por_Dia/OperacionDia/@fecha[1] independently of the nodes('/Operaciones_por_Dia/OperacionDia/PagoRecibo') query so will always be comparing @FechaOperacion against the first date attribute - which is 2020-01-30 in this document.

declare @FechaOperacion date = convert(date, '2020-01-30', 120);
select
    ph.value('@NumFinca', 'INT'),
    ph.value('@TipoRecibo', 'INT'),
    ph.value('../@fecha', 'DATE')
from @DocumentoXML.nodes('/Operaciones_por_Dia/OperacionDia/PagoRecibo') AS t(ph)
where @DocumentoXML.value('(/Operaciones_por_Dia/OperacionDia/@fecha)[1]', 'DATE') = @FechaOperacion

Depending on the value of @FechaOperacion it will either return all rows in the document (when it matches) or now rows at all (when it doesn't)...

(No column name) (No column name) (No column name)
---------------- ---------------- ----------------
9782331          5                2020-01-30
6696849          5                2020-01-30
1423800          7                2020-04-08
1393022          7                2020-04-08

The solution is to query for nodes('/Operaciones_por_Dia/OperacionDia') and then use a cross apply to query the PagoRecibo child nodes.

declare @FechaOperacion date = convert(date, '2020-01-30', 120);
select
    ph.value('@NumFinca', 'INT'),
    ph.value('@TipoRecibo', 'INT'),
    od.value('@fecha', 'DATE')
from @DocumentoXML.nodes('/Operaciones_por_Dia/OperacionDia') AS s(od)
cross apply s.od.nodes('PagoRecibo') AS t(ph)
where s.od.value('@fecha', 'DATE') = @FechaOperacion

Which returns...

(No column name) (No column name) (No column name)
---------------- ---------------- ----------------
9782331          5                2020-01-30
6696849          5                2020-01-30